>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:
<?php
# 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
database');
$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
data");
# 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");
}
?>
NOTES:
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
browser.
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.
WARNING:
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.
YMMV
--
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
