php - update sql statement with multiples rows

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.

Solution

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