How to construct an UPDATE statement using two arrays

Question

I have an array of column headers and an array of corresponding values at the same index I need to update a row using both those arrays in SQL.

I know this might not be the neatest way of doing this. How I've got the values is I take the SQL database columns and use those to generate an HTML form which gives the values hence why both are in an array.

Using an INPUT my SQL looks like this and works how I want it to:

$strcol = implode(",", $fieldname);
$strdata = implode(",", $fielddata);
$placeholders = str_repeat(" ?, ", count($fielddata)-1) . "?";

print_r($placeholders);
$sql = "INSERT INTO morning (" . $strcol . ")
VALUES (". $placeholders . ")";
print_r($sql);
$result = $conn->execute_query($sql, $fielddata);

That gives a result something like:

BUT I can't figure out how to do the same with an UPDATE, I might have missed a really obvious solution somewhere

Answer 1

// find the value of the primary key (assuming it is "id")
$idKey = array_search("id", $fieldname);
if ($idKey === false) {
  // return error because the data does not include the primary key
}
$idValue = $fielddata[$idKey];

$assignments = implode(", ", array_map(function($col) { return "`$col`=?"; }, $fieldname));
    
$sql = "UPDATE morning SET {$assignments} WHERE id=?";
$result = $conn->execute_query($sql, array_merge($fielddata, [$idValue]));