I have a Prepared Statement that is supposed to take a list of Message ID’s and update several records all at once using a single UPDATE query.
Here is an example of what the actual SQL might look like…
UPDATE private_msg_recipient
SET read_on=NULL,
updated_on=NOW()
WHERE member_id_to=19
AND message_id IN (52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1)
When I run this SQL in phpMyAdmin it runs perfectly., however my PHP isn’t working as expected?!
Towards the top of my script I have this code…
echo '$messagesToUpdate = ' . $messagesToUpdate;
…which yields this on the screen…
$messagesToUpdate = 52, 49, 38
The problem is that when I run my script, my Prepared Statement is only updating the first value in $messagesToUpdate…
Here is a snippet of my code…
// Build query.
$q1 = "UPDATE private_msg_recipient
SET read_on = NULL,
updated_on = NOW()
WHERE member_id_to = ?
AND message_id IN (?)";
// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);
// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 'is', $sessMemberID, $messagesToUpdate);
// Execute query.
mysqli_stmt_execute($stmt1);
// Verify Update.
if (mysqli_stmt_affected_rows($stmt1)==1){
// Update Succeeded.
}else{
// Update Failed.
What am I doing wrong here??
(Originally I had ‘ii’ in my mysqli_stmt_bind_param statement, and I thought that was the issue, but even with ‘is’ things still aren’t working?!) :-/
Sincerely,
Debbie