Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Problem using IN with Prepared Statement

    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...

    Code:
    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...
    Code:
    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

  • #2
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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. 
        

    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • #3
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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.
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • #4
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    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

  • #5
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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. 
        

    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • Users who have thanked AndrewGSW for this post:

    doubledee (01-05-2013)

  • #6
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewGSW View Post
    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

  • #7
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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!
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • #8
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,064
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewGSW View Post
    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

  • #9
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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.
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •