I am trying to copy the contents of a table from one server to a duplicate table on another server. This will be in a cronjob. There are only about 200 records that need daily updating. I don't want to use PHPMYAdmin, Navicat, etc. as I want it to be run via the server. I am truncating the destination table prior to the copy.
I was following the example here: https://stackoverflow.com/a/23657043/2413654 ($L1 is the connection to server 1, $L2 for server 2)
But some of the data in source table contains apostrophes, etc. and the insert fails.
I tried adding mysqli_real_escape_string to the value of $v in the iteration below, but it still errors on the first apostrophe encountered in the data.
Any suggestions? Is there a better way of doing this task?
Here is my code:
$re = mysqli_query($L1, "SELECT * FROM ft_form_2");
$keyfield = 'submission_id';
while($i=mysqli_fetch_assoc($re))
{
$u = array();
foreach($i as $k=>$v)
$v = mysqli_real_escape_string($L2,$v); // added, but not working
if($k!=$keyfield) $u[] = "$k='$v'";
mysqli_query($L2,"INSERT INTO ft_form_2_test (".implode(',',array_keys($i)).")
VALUES ('".implode("','",$i)."') ON DUPLICATE KEY UPDATE ".implode(',', $u))
or die(mysqli_error());
}
mysqliyou should be using parameterized queries andbind_paramto add user data to your query. DO NOT use manual escaping and string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. Accidentally unescaped data is a serious risk. Using bound parameters is less verbose and easier to review to check you’re doing it properly.LOAD DATA INFILEon the other. This is often faster than shuttling over from one connection to another.