SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,872
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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...
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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.
    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

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    The second parameter is a string - they're not seperate values, but a string that should be inserted there...
    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

  6. #6
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,314
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    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.

    Code SQL:
    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.
    "First make it work. Then make it better."


Bookmarks

Posting Permissions

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