Original problem: How to copy ONLY CERTAIN mysql tables between TWO servers
USING PHP? This approach is working, but its is slow and it can easily
exceed php maximum executution time. (Could maximum time extended only for
current page request?)
-perttu pulkkinen, jkl
<?php
$host_A = "x";
$user_A = "x";
$pass_A = "x";
$base_A = "x";
$host_B = "y";
$user_B = "y";
$pass_B = "y";
$base_B = "y";
$tables = array("qwer", "wer", "ert", "rty");
$table_prefix_B = "copy_of_";
// -------------------------------------------------------
$table_prefix_B = trim($table_pre fix_B);
if($host_A == $host_B && $base_A == $base_B && $table_prefix_B =="")
die("Samaa taulua ei voi kopioida samannimiseksi saman tietokannan
sisällä!");
// -------------------------------------------------------
$link_A = mysql_connect($ host_A, $user_A, $pass_A)
or die("Yhteys tietokantaan A epäonnistui! : " . mysql_error());
$link_B = mysql_connect($ host_B, $user_B, $pass_B)
or die("Yhteys tietokantaan B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
mysql_select_db ($base_A, $link_A) or die("Tietokanna n A valinta
epäonnistui!");
mysql_select_db ($base_B, $link_B) or die("Tietokanna n B valinta
epäonnistui!");
// -------------------------------------------------------
foreach($tables as $table)
{
$table_B = $table_prefix_B .$table;
$show_create = "SHOW CREATE TABLE $table";
// echo "<p>".$show_cre ate."</p>";
$qid_SHOW_A = mysql_query($sh ow_create, $link_A)
or die("Taulukuvau ksen haku tietokannasta A epäonnistui! : " .
mysql_error());
// -------------------------------------------------------
$drop = "DROP TABLE IF EXISTS $table_B";
// echo "<p>".$drop ."</p>";
$qid_DROP_B = mysql_query($dr op, $link_B)
or die("Taulun poisto tietokannasta B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
$create_row = mysql_fetch_row ($qid_SHOW_A)
or die("Kuvausrivi n haku epäonnistui! : " . mysql_error());
$create = $create_row[1];
$create = str_replace("CR EATE TABLE `$table`", "CREATE TABLE `$table_B`",
$create)
or die("Taulunimen muodostus epäonnistui!");
// echo "<p>".$create." </p>";
$qid_CREATE_B = mysql_query($cr eate, $link_B)
or die("Taulun luonti tietokantaan B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
$select = "SELECT * FROM $table";
// echo "<p>".$select." </p>";
$qid_SELECT_A = mysql_query($se lect, $link_A)
or die("Taulun datan haku tietokannasta A epäonnistui! : " .
mysql_error());
// -------------------------------------------------------
while($row = mysql_fetch_ass oc($qid_SELECT_ A))
{
$insert = formulate_inser t_row($table_B, $row, $link_B);
// echo "<p>".$insert," </p>";
$qid_INSERT_B = mysql_query($in sert, $link_B)
or die("Rivin kopiointi tauluun B epäonnistui! : " . mysql_error());
}
}
// -------------------------------------------------------
function formulate_inser t_row($table, $row, $link)
{
$fields=""; $values = "";
$flag=true;
foreach($row as $key=>$value)
{
if($flag) $flag=false; else { $values.= ","; $fields.= ","; }
$values.= "'".mysql_real_ escape_string($ value, $link)."'";
$fields.= $key;
}
return " INSERT INTO $table($fields) VALUES($values) ";
}
// -------------------------------------------------------
?>
