>I'm sorry, I thought that what I was asking was very simple.  but everyone 
>wants to make it more
>difficult then it actually is. 

No, you just hadn't quite explained what you needed properly.

There were simply too many undefined points in your email for a reasonable
answer to be given.

If you really want to ignore the experts and do it your way, here's a crude
sample script that should be pretty close to what you think you want:

  # Untested code written on the fly:
  $stage = mysql_connect('stage.example.com', 'stageuser', 'stagepass') or
die('Could not connect to stage database');
  mysql_select_db('stage', $stage) or die('Could not select stage
  $production = mysql_connect('production.example.com', 'productionuser',
'productionpass') or die('Could not connect to production database');
  mysql_select_db('production', $production) or die('Could not select
production database');
  # Update production data to "archived" status:
  $query = "update sometable set archived = 1";
  mysql_query($query, $production) or die("Could not update production
database to 'archived'");
  # Find out which IDs already exist in 'production'
  $query = "select whatever_id from sometable";
  $archived = mysql_query($query, $production) or die("Could not obtain
production IDs");
  $archived_ids = array();
  while (list($id) = mysql_fetch_row($archived)){
    $archived_ids[] = $id;
  # Snag any staged content that is not yet archived (ie, on production):
  $archived_ids_sql = implode(', ', $archived_ids;
  $query = "select whatever_id, whatever_columns from sometable where
whatever_id in ($archived_ids_sql)";
  $newbies = mysql_query($query, $stage) or die("Could not obtain stage
  # Copy all those rows to 'production'
  while (list($whatever_id, $whatever_columns) = mysql_fetch_row($newbies)){
    $query = "insert into whatever(whatever_id, whatever_columns) ";
    $query = "values($whatever_id, '$whatever_columns') ";
    mysql_query($query, $production) or die("Could not insert $whatever_id
into production");

You could put this on 'stage' or 'production' server and run it from a cron
job, or on demand from some other script, or just by loading it into a

You'll need to configure MySQL on the machine where this script is *not*
living to accept connections from the "other" machine.  In other words,
assuming you put this on 'stage', and you expect the script to be able to
mysql_connect('production.example.com' ..., you will need to configure MySQL
on 'production.example.com' to accept connections from 'stage.example.com' 
I think MySQL out of the box will only accept connections from 'localhost'
(ie, the same machine as it's on)

You could gain a little (or maybe a lot...) of performance by using MySQL's
multiple-insert syntax instead of doing a different insert query for every
single new row in 'stage'.  However, multiple-insert syntax will not be
portable to most other SQL engines, I don't think.

You can either repeat the above technique for as many tables as you need,
*OR* if you have a zillion tables, you could use
http://php.net/mysql-list-tables to get all the tables in the database
(either one) and then http://php.net/mysql-list-fields to figure out what
tables/fields are in each database and build your queries dynamically.

This is probably not the right way to do what you want to do.  I'm only
answering your question, no matter how wrong that question might be :-)  The
experts have already told you that, and you don't seem to want to listen,
though, so I figure you might as well try it your way and see what happens.

You might some day find it easier to re-think your design, however, and
time-stamp the records that can be "archived" and then always just use
mysqldump to move stage -> production.

You would then add an extra table/field to your schema that tracked when the
previous 'move' occurred, and any records 'older' than that would be
'archived' records, not 'current'

Using mysqldump and a time-stamp will "scale up" *MUCH* better if the number
of records/tables/fields increases dramatically...

The above script is very, very crude and will *NOT* do well when zillions of
records are added to stage at once.

If you don't expect the numbers to ever become large, just do whichever one
makes you happy :-)

But if you are thinking this database might grow large, doing it the way
above is insane.


Like Music?  http://l-i-e.com/artists.htm

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to