Problem using IN with Prepared Statement

[doubledee]

I am having trouble using the SQL keyword "IN" with my Prepared Statement.

Some background...

I have a Private Messaging system that looks and works like Yahoo Mail. Next to each PM is a checkbox that the User can check to mark off PM's to receive some action (e.g. Mark as Unread).

Based on the "action" chosen, and the PM's that the user checked off, I then need to run an UPDATE query to make the changes.

Here is a hard-coded example of what a query 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)

If I run this hard-coded query through phpMyAdmin it runs just fine - thus proving that my SQL is okay.

However, when I try to do something similar in my PHP code using a Prepared Statement, I am having issues.


Here are some snippets of my problematic code...

In the HTML section of my code, I build an array containing an entry for every PM that is checked like this...

PHP Code:

// Loop through Messages.
    while (mysqli_stmt_fetch($stmt6)){
        echo "<tr" . (is_null($readOn) ? " class='pmUnread'" : "") . ">
                <td class='colSelect'>
                    <input id='"
                    . str2htmlentities($pmID)
                    . "' name=selectedMsgArray["
                    . str2htmlentities($pmID)
                    . "] type='checkbox' value=1 />
                </td> 


Then when my Form gets submitted, I have this code...

PHP Code:

    if (!empty($_POST['selectAll']) && $_POST['selectAll'] == 1){
        // All Messages Selected.
        echo "All Messages Selected<br />";

        $messagesToUpdate = implode(', ', array_keys($_POST['msgArray']));

    }elseif (!empty($_POST['selectedMsgArray']) && $_POST['selectedMsgArray']){
        // Some Messages Selected.
        echo "Some Messages Selected<br />";

        $messagesToUpdate = implode(', ', array_keys($_POST['selectedMsgArray']));

    }else{
        // No Messages Selected.
        echo "No Messages Selected<br />";

        $messagesToUpdate = '';

    }//End of MARK INCOMING AS UNREAD 


If I echo $messagesToUpdate, then I get...

52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1

...which is what you would expect.


Finally, I have this code which should go out and update the PM's that were selected by the User...

PHP Code:

    if (isset($messagesToUpdate) && $messagesToUpdate){
        // Update Selected Messages.

        // 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);
    //            mysqli_stmt_bind_param($stmt1, 'ii', $sessMemberID, $msgID);

        // Execute query.
        mysqli_stmt_execute($stmt1);

        // Verify Update.
        if (mysqli_stmt_affected_rows($stmt1)==1){
            // Update Succeeded.

        }else{
            // Update Failed.

        } 


When I run my code, what is happening is that the FIRST Private Message gets updated, but nothing else that was selected?!

As mentioned above, if I run a hard-coded version of the query, it updates all checked PM's in the table, but my code is not working properly?!

Originally I had 'ii' when binding the parameters, and that seemed like a possible culprit, so I changed things to 'is' because 52, 51, 49, 39, 38, 10, 8, 6, 5, 2, 1 in the IN statement should really be treated like a STRING.

However, using 'is' did not help.

I am wondering if PHP is seeing the first part of my "String" and seeing that it is a Number and dynamically type-casting things as an "Integer"?!

Any ideas why my Prepared Statement is not working?

Thanks,


Debbie

[AndrewGSW]

Maybe something like..

PHP Code:

if (isset($messagesToUpdate) && $messagesToUpdate) {  
        // Update Selected Messages. 
    $parmcount = count($messagesToUpdate);
    $inclause = implode(',', array_fill(0, $parmcount, '?')); // = ?,?,?,?
    // Build query. 
            $q1 = "UPDATE private_msg_recipient 
                    SET read_on = NULL, 
                        updated_on = NOW() 
                    WHERE member_id_to = ? 
                    AND message_id IN (%s)";

    $preparesql = sprintf($q1, $inclause);
    // Prepare statement. 
    $stmt1 = mysqli_prepare($dbc, $preparesql); 

    array_unshift($messagesToUpdate, $sessMemberID);

    $type = 'i' . str_repeat('i', $parmcount);
    // Bind variables to query.
    call_user_func_array('mysqli_stmt_bind_param', 
        array_merge(array($stmt1, $type), $messagesToUpdate)); 
    // Execute query. 
    mysqli_stmt_execute($stmt1);
    
    // Verify Update. 
    if (mysqli_stmt_affected_rows($stmt1) == 1) { 
        // Update Succeeded. 

    } else { 
        // Update Failed. 
    } 


[AndrewGSW]

An ampersand may be required:

call_user_func_array('mysqli_stmt_bind_param',
array_merge(array(&$stmt1, $type), $messagesToUpdate));

but I have no way of knowing.

[doubledee]

AndrewGSW,

Wow, thanks for the effort!

Unfortunately I'm having a hard time following your fancy code.

Would I be better to just use a loop like I originally had, and run an UPDATE query for each Private Message that needs to be updated?

Maybe that is better?


Debbie

[AndrewGSW]

Well you could cheat - side-step the parameters on this occasion only! As long as you ensure that the message-ids are ALL INTEGER NUMBERS you could embed them into the sql-statement.

The 'W' on the third line is to ensure that all characters are numbers. If a value of 12.34 is obtained, it is converted to '12W34' and it will no longer pass the is_numeric() test.

PHP Code:

if (isset($messagesToUpdate) && $messagesToUpdate) { 
        // Update Selected Messages. 
    if (!is_numeric(str_replace('.', 'W', implode('', $messagesToUpdate))) {
        // at least one of them isn't an integer: bail!
    }
    $parmcount = count($messagesToUpdate);
    $inclause = implode(',', $messagesToUpdate);
    // Build query. 
            $q1 = "UPDATE private_msg_recipient 
                    SET read_on = NULL, 
                        updated_on = NOW() 
                    WHERE member_id_to = ? 
                    AND message_id IN (%s)";

    $preparesql = sprintf($q1, $inclause);
    // Prepare statement. 
    $stmt1 = mysqli_prepare($dbc, $preparesql); 

    // Bind variable to query. 
    mysqli_stmt_bind_param($stmt1, 'i', $sessMemberID); 
    // Execute query. 
    mysqli_stmt_execute($stmt1);
    
    // Verify Update. 
    if (mysqli_stmt_affected_rows($stmt1) == 1) { 
        // Update Succeeded. 

    } else { 
        // Update Failed. 
    } 


[doubledee]

Originally Posted by AndrewGSW

Well you could cheat - side-step the parameters on this occasion only! As long as you ensure that the message-ids are ALL INTEGER NUMBERS you could embed them into the sql-statement.

The 'W' on the third line is to ensure that all characters are numbers. If a value of 12.34 is obtained, it is converted to '12W34' and it will no longer pass the is_numeric() test.

PHP Code:

if (isset($messagesToUpdate) && $messagesToUpdate) { 
        // Update Selected Messages. 
    if (!is_numeric(str_replace('.', 'W', implode('', $messagesToUpdate))) {
        // at least one of them isn't an integer: bail!
    }
    $parmcount = count($messagesToUpdate);
    $inclause = implode(',', $messagesToUpdate);
    // Build query. 
            $q1 = "UPDATE private_msg_recipient 
                    SET read_on = NULL, 
                        updated_on = NOW() 
                    WHERE member_id_to = ? 
                    AND message_id IN (%s)";

    $preparesql = sprintf($q1, $inclause);
    // Prepare statement. 
    $stmt1 = mysqli_prepare($dbc, $preparesql); 

    // Bind variable to query. 
    mysqli_stmt_bind_param($stmt1, 'i', $sessMemberID); 
    // Execute query. 
    mysqli_stmt_execute($stmt1);
    
    // Verify Update. 
    if (mysqli_stmt_affected_rows($stmt1) == 1) { 
        // Update Succeeded. 

    } else { 
        // Update Failed. 
    } 


Wow! That is a radical, yet innovative, approach?!

Honestly, though, while I waited for your response I started re-working my original code using a loop.

Here is what I came up with which isn't so bad, as it eliminates the duplicate code I originally wanted to avoid...

First I determine which Messages were selected...

PHP Code:

    // ********************************
    // Check for Messages Selected.        *
    // ********************************
    if (empty($error)){
        // Continue processing...

        if (!empty($_POST['selectAll']) && $_POST['selectAll'] == 1){
            // All Messages Selected.
            $updateMsgArray = $_POST['msgArray'];

        }elseif (!empty($_POST['selectedMsgArray']) && $_POST['selectedMsgArray']){
            // Some Messages Selected.
            $updateMsgArray = $_POST['selectedMsgArray'];

        }else{
            // No Messages Selected.
            $error = 'Please choose a Message(s) to update.';

        } 


And as you can see, I just re-name/re-assign whichever $_POST array applies to a generic $updateMsgArray one.

Then in my loop, I just use this single array to loop through, thus eliminating the need for two nearly identical blocks of code for "Select All" and "Select Some" scenarios...

PHP Code:

    foreach($updateMsgArray as $msgID => $msgValue){
        // Build query.
        $q1 = "UPDATE private_msg_recipient... 


Now, from a Database Server standpoint, your approach is probably better, but since I am not GMail, I'm thinking this latest approach I came up with will suffice, although I am very impressed with your solution!!

What do you think?

Thanks,


Debbie

[AndrewGSW]

You didn't take-to my first code though? Took me a couple of hours to forge that . I still don't know whether it works, but expect that it would.

It is regarded a poor practice to execute SQL statements in a loop, even with relatively small data-sets. Supposing a number of people are running the page at the same time? It's a burden on the server. And if one of the query-attempts fails, but previous ones succeeded, how will you handle this situation?

And thank you!

[doubledee]

Originally Posted by AndrewGSW

You didn't take-to my first code though? Took me a couple of hours to forge that . I still don't know whether it works, but expect that it would.

Don't be mad at me.

I did read it, and I do appreciate all of your effort. But honestly, that code was way over my head!!!

It also seemed like you weren't sure about some aspects of it, so that made me a little nervous, because code like that I'd have to take on blind faith.

It is regarded a poor practice to execute SQL statements in a loop, even with relatively small data-sets. Supposing a number of people are running the page at the same time? It's a burden on the server.

I suppose so.

And if one of the query-attempts fails, but previous ones succeeded, how will you handle this situation?

I throw an error message whenever a query fails, so the entire update would halt and they would have to try again or contact the Admin. That is reasonable.

And thank you!

If you are referring to me saying your code was "innovative, yet crazy", then you are welcome.

And back to your comment about the effort you put in, again, I appreciate that.

I would like to use your latest code, but I have also been stuck trying to get this script working for the last 3-4 weeks...

(You may recall that you and Tango helped me with another aspect of this script around Christmas?!)

I'm not sure what to do...


Debbie

[AndrewGSW]

Don't be mad at me

I'm not; I was only joshing. I looked into that code because it interested me. The fact that it was not used is irrelevant to me.