Update mysql table data from xml file

[enrb15]

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";
    }
}
?>

 

[requinix]

You're reading from an XML file but you haven't posted it, and you're getting errors but you haven't said what they are. Does that about sum it up?

[Barand]

Your UPDATE query should be an INSERT .. ON DUPLICATE KEY query.

If there is a duplicate you should be updating the city, not the id.

INSERT INTO user_city (id_city, city)
    VALUES ('$id_city', '$city')
    ON DUPLICATE KEY UPDATE city = '$city'

Better still, use prepared statements.