Prepared Statement only updating One Record

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

You have 12 values you want to pass but only 2 fields defined to bind the values to the SQL.

You need to explode $messagesToUpdate and pass them in separately.

Okay, gonna make myself look foolish here i think but…

Why?

The second parameter is a string - they’re not seperate values, but a string that should be inserted there…

No.

If you look at my example SQL, the value inside the IN( ) should be one long String.

The whole purpose of IN is that lets you have a dynamic listing of values…

What was 12 values in my example above, would likely be some other value next time, so it is NOT a predetermined, finite list!

Debbie

I agree, but then why isn’t my code working as expected?

Could it be that PHP - with its “dynamic type-casting” - is just seeing the first number in my “string” and treating it like an Integer?

Debbie

To help illustrate, here’s what your query, as currently written, would be equivalent to if we were to build it using old-school concatenation.

UPDATE private_msg_recipient 
SET read_on = NULL, 
    updated_on = NOW() 
WHERE member_id_to = '$sessMemberID'
AND message_id IN ('$messagesToUpdate')

Notice that $messagesToUpdate is treated as a single string value, not a list of values. If you’re going to bind a dynamic number of parameters, then you need to create a dynamic number of “?” placeholders.