0

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());
}
9
  • 1
    That example is terribly outdated and teaches bad habits. Use prepared statements with bound parameters, via either mysqli or PDO. This post has some good examples. Commented Aug 1, 2018 at 18:05
  • 1
    If you always want to wipe and re-create the table on the destination server, then you're probably better off just using mysqldump. Commented Aug 1, 2018 at 18:07
  • 1
    WARNING: When using mysqli you should be using parameterized queries and bind_param to 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. Commented Aug 1, 2018 at 18:10
  • 1
    Another option is to dump as CSV on one server and LOAD DATA INFILE on the other. This is often faster than shuttling over from one connection to another. Commented Aug 1, 2018 at 18:24
  • 1
    Thanks for all your input. I will study up on PDO. Went with @tadman with a CSV dump and LOAD DATA INFILE until I properly code it in PDO. Commented Aug 1, 2018 at 18:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.