How can I copy a table from one database to another?

Dimoop

Hi all

I need a PHP script to copy a table from one database to another, including all the data in that table. I wil then use cron to run the script every hour. Unless there's any better way to mirror a database? It doesn't really need to be real-time, just so long as it's no more than every hour. I know if I had dedicated servers and MySQL 4 I could do this easily, but I only have shared hosting accounts and some are MySQL 4 while others are MySQL 3.

Thanks
Max

bastien

consider adding a field called something like 'last_update' and fill it with the date when this operation is complete...or use a date stamp field (running a small calc to determine the current time-60 minutes to get the rows that need to go)

then a simple sql call to get all the data from one to the other taking only the rows that have been added since the last running of the db

reduces the db load and the amount of data to be exchanged each time...

Dimoop

I'm not sure I understand exactly what you said but I've been playing around with this when I saw your reply. Am I on the right lines?

<?php
mysql_connect("localhost", "mhbillin_madb", "<REMOVED>");
mysql_select_db("mhbillin_madb");
$sql = "SELECT * FROM liscdata";
$res = mysql_query($sql);
$data = "INSERT INTO liscdata SET id='1', domain='remove.com', ip='123.123.123.123'\n";
while($row = mysql_fetch_array($res) {
$data .= "INSERT INTO liscdata SET id='$row['\id'\]', domain='$row[\'domain\']', ip='$row[\'ip\']'\n"; 
}
function insert($sql) {
mysql_query($sql);
mysql_query("DELETE FROM liscdata WHERE id='1' AND domain='remove.com' AND ip='123.123.123.123'");
mysql_query("DELETE FROM liscdata WHERE id='1' LIMIT 1");
}
mysql_close();
mysql_connect("69.57.152.42", "mhbillin_liscdb", "<REMOVED>");
mysql_select_db("mhbilin_liscdb");
insert($data);
mysql_close();
?>

Thanks 🙂