-2

Good morning, I ask you why I have a problem with a script in php to update the data of a mysql table.

I have a table with the following values:

id, name, city, city_id

and an xml file with the data to be updated:

id_city, city

I tried to make a script to update the data in the table, but unfortunately I always get errors.

My script is as follows:

<?php
$conn = mysqli_connect("localhost", "root", "", "dbtest001");


$affectedRow = 0;

$xml = simplexml_load_file("city.xml") or die("Error: Cannot create object");

foreach ($xml->children() as $row) {
    
    $id_city = mysqli_real_escape_string($conn, ($row-> id_city));
    $city = mysqli_real_escape_string($conn, ($row-> city));
    
        
    $sql = "UPDATE user_city SET (id_city, city)
    VALUES ('" . $id_city . "', '" . $city . "')
    ON DUPLICATE KEY UPDATE id_city=$id_city "
    ;
    
    
    $result = mysqli_query($conn, $sql);
    
    if (! empty($result)) {
        $affectedRow ++;
    } else {
        $error_message = mysqli_error($conn) . "\n";
    }
}
?>
ebr15
  • 5
  • 1
  • _"but unfortunately I always get errors"_ - Please edit your question and add the complete error message (and point out which line it happens at). It's hard to help solve a, for us, unknown error. – M. Eriksson May 23 '22 at 14:18
  • 1
    I would also recommend that you read [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to learn how to properly protected yourself against SQL injections using prepared statements and placeholders instead of manually escaping the data. Even `mysqli_real_escape_string()` has security issues in some situations. – M. Eriksson May 23 '22 at 14:21

0 Answers0