PHP Script to copy data from DB1 to DB2

[southbeach]

I wrote this script for a very specific need we had here in the office. It is pretty simple but I figure that it may be of use to others so I decided to post it here.

Please notice that the tables' name between the two databases must be same.

Enjoy!

<?
$conn = mysql_connect('localhost', 'My_UserID', 'My_Password');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('Source_DB');

$tables = mysql_query("show tables"); 
$count=0; 
$command = array(); $name = array();

echo '<table width="100%" border="1"><tr><th>Reading File</th><th>Time Processed</th></tr>';
while ($tlist = mysql_fetch_array($tables)) {

	echo '<tr><td>' . $tlist[0] . '</td><td>' . time() . '</td></tr>';
	$qstr = 'select * from ' . $tlist[0];
	$result = mysql_query($qstr);
	if (!$result) {
		die('Query failed: ' . mysql_error());
	}

	while ($rows = mysql_fetch_assoc($result)) {
		
		$i = 0; 
		$insert = 'INSERT INTO `' . $tlist[0] . '` ( ';
		$lastf = mysql_num_fields($result) - 1;
		
		/* get column metadata */
		while ($i < mysql_num_fields($result)) {
			// echo "Information for column $i:<br />\n";
			$meta = mysql_fetch_field($result, $i);
			if (!$meta) {
				echo "No information available<br />\n";
			}
			//echo 'i: ' . $i . '  lastf: ' . $lastf . '<br />';
			if ($i != $lastf) { $cols .= '`' . $meta->name . '`,'; }
			else { $cols .= '`' . $meta->name . '`'; }

			if ($i != $lastf) { $vals .= '"' . $rows[$meta->name] . '",'; }
			else { $vals .= '"' . $rows[$meta->name] . '"'; }

			$i++;
		}
		$insert .= $cols . ') VALUES (' . $vals . ')';
		$command[$count] = $insert; $count++;
		$name[$count]=$tlist[0];
		$cols=''; $vals=''; $insert='';

	}

}
echo '</table>';
mysql_free_result($result);

$local = mysql_connect('RemoteHost', 'My_UserID', 'My_Password');
if (!$local) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('Target_DB');

for ($i=0; $i < $count; $i++) {
	$insert = $command[$i];  echo 'Writing Data For ' . $name[$i] . '<br />';
	$addrec = mysql_query($insert) or die (mysql_error());
}

mysql_free_result($addrec);
?>

 

[johnwm]

You may also like to look at Backup (

http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html)

and Replication (

http://dev.mysql.com/doc/refman/5.1/en/replication.html)

which will possibly be quicker and less intrusive on other processes.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints