php - 用多行更新sql语句

Question

I know this problem has already been around but I cannot figure out how to solve it in my case. I try to use the UPDATE statement with sql but I have a problem when dealing with multiples rows.

This is my code:

$body = file_get_contents('php://input');
$jsonArray = json_decode($body, true);

$sql = array();
foreach ($jsonArray as $row) {   

    $sql[] = '("'.$row['firstname'].'", "'.$row['lastname'].'", "'.$row['sex'].'", "'.$row['dateOfBirth'].'", "'.$row['email'].'")';

}

$column_name = "(firstname, lastname, sex, dateOfBirth, email)";
$stringImplode = implode(',', $sql);

$action = $mysqli->query('INSERT INTO tbl_syncList '. $column_name .' VALUES '.$stringImplode .'ON DUPLICATE KEY UPDATE XXXXXXX'); // --> I get stuck here

I do not know how I should write after the ON DUPLICATE KEY UPDATE: I know it should be something like "column_name = value1, column_name2 = value2..." but could it be possible to use the arrays above in a way like "(column_name1, column_name2...) = (value1, value2...) that could be useful if I were to add multiples attributes?

Since this is located outside the loop I am not sure how to refer to my value inside the sql[] array since I could have a lot of different value for the same column_name.

Moreover I would like to add a WHERE clause at the end with something like "WHERE timestamp_column_name < timestamp_value" but I don't know if it is possible..

If anyone could help me to solve this problem that would be great.

Answer 1

To answer your first question, you can use VALUES() in the ON DUPLICATE KEY UPDATE statement :

$action = $mysqli->query('INSERT INTO tbl_syncList '. $column_name .' VALUES '.$stringImplode .'ON DUPLICATE KEY UPDATE firstname = VALUES(firstname), lastname = VALUES(lastname), sex = VALUES(sex), dateofbirth = VALUES(dateofbirth), email = VALUES(email)');

However, I don't think your second problem can be resolved using this approach. My recommendation would be to first insert all of the records into a staging table which doesn't have any unique constraints on the columns you are inserting/updating. Then use further queries (or preferably a database procedure) to perform separate update and insert queries based on a join between the tables, and whatever other criteria you have.

My MySQL is a bit rusty, but I think this should be roughly what you need once you have inserted the data into the staging table :

UPDATE a
SET a.firstname = b.firstname,
    a.lastname  = b.lastname,
    a.sex       = b.sex,
    ...
FROM stagingtable b
JOIN tbl_syncList a ON a.idcolumn = b.idcolumn
WHERE b.timestamp > a.timestamp

INSERT tbl_syncList(firstname, lastname, sex, dateOfBirth, email)
SELECT 
    b.firstname,
    b.lastname,
    b.sex,
    b.dateOfBirth,
    b.email
FROM stagingtable b
LEFT JOIN tbl_syncList a ON a.idcolumn = b.idcolumn
WHERE a.idcolumn IS NULL