SitePoint Sponsor

User Tag List

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

    INSERT multiple records??

    Is there a fairly easy way to be able to INSERT a varying number of records using Prepared Statements?

    Here is my current code which just inserts a single person into my table...
    PHP Code:
        $q3 "INSERT INTO private_msg_recipient(member_id_to, message_id, created_on)
                VALUES (?, ?, NOW())"
    ;

        
    $stmt3 mysqli_prepare($dbc$q3);

        
    mysqli_stmt_bind_param($stmt3'ii'$pmRecipientID$messageID);

        
    mysqli_stmt_execute($stmt3); 

    I am trying to find a way that allows someone to INSERT as many records as needed (e.g. 1, 2, 10, 100, 10,000), but to do it all with just ONE QUERY because I hear that is much better than doing 1,000 INSERTs!!

    Sincerely,


    Debbie

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Well, this is a multi-step process i've cobbled together (but not extensively tested).

    Construct a query string of valid size...
    $qstringvals = array();
    $paramlist = "";

    for ($i = 0; $i < number_of_records; $i++) {
    $qstringvals[] = "(?, ?, NOW())";
    $paramlist .= "ii"
    }
    (Note: You may need to throttle here to prevent the query from getting TOO long)

    Smash the array together to make a nice long query;
    $q3 = "INSERT INTO private_msg_recipient(member_id_to, message_id, created_on) VALUES ".implode(',',$qstringvals);

    bind using an array instead of static values. This is where things get oddball, and i'm moderately sure there's better ways to do it...citing an example from the bind_param manual page:

    call_user_func_array('mysqli_stmt_bind_param', array_merge (array($stmt3, $paramlist), $parameters);

    (Where $parameters is an array of the values you want to put into the statement.)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'm not seeing how you go from this...
    PHP Code:
    $q2 "INSERT INTO private_msg(member_id_from, subject, body, sent_on)
        VALUES (?, ?, ?, NOW())"
    ;

    $stmt2 mysqli_prepare($dbc$q2);

    mysqli_stmt_bind_param($stmt2'iss'$sessMemberID$pmSubject$pmBody); 
    To something like this...
    PHP Code:
    $q2 "INSERT INTO private_msg(member_id_from, subject, body, sent_on) 
        VALUES (?, ?, ? NOW()),
        (?, ?, ? NOW()),
        (?, ?, ? NOW())"
    ;

    $stmt2 mysqli_prepare($dbc$q2);

    mysqli_stmt_bind_param($stmt2'issississ'$sessMemberID$pmSubject$pmBody,
                            
    $sessMemberID$pmSubject$pmBody
                            $sessMemberID
    $pmSubject$pmBody); 

    I'm definitely not following the "translation" between a Single INSERT and a Varying Multiple INSERT...

    Sincerely,


    Debbie

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $qstringvals = array();
    $paramlist "";

    for (
    $i 0$i number_of_records$i++) {
    $qstringvals[] = "(?, ?, NOW())";
    $paramlist .= "ii"

    So lets say there were 3 records.
    $qstringvals is now an array of size 3, each element containing the string "(?, ?, NOW())"
    $paramlist is now "iiiiii" (If you wanted strings, put the s's in there instead, whatever format it is.

    $parameters must be an array of size 6 (all integers; what values they hold are not relevant)

    PHP Code:
    $q3 "INSERT INTO private_msg_recipient(member_id_to, message_id, created_on) VALUES ".implode(',',$qstringvals); 
    turns $q3 into "INSERT INTO private_msg_recipient(member_id_to, message_id, created_on) VALUES (?, ?, NOW()),(?, ?, NOW()),(?, ?, NOW())"
    This then gets Prepared.
    PHP Code:
    call_user_func_array('mysqli_stmt_bind_param'array_merge (array($stmt3$paramlist), $parameters); 
    This then (so the example goes) takes your parameters, and binds them all into the statement because of how it smashes all the values together. It's sort of a weakness of the mysqli function that makes this necessary (if the third parameter of bind_param could accept arrays [or in fact, have only 3 parameters - the third being a Mixed type], you wouldnt have an issue here.)

    This is also why I prefer PDO, because you can simply loop the bindParam calls, making it much more readable.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    StarLion,

    Deep post!!


    Quote Originally Posted by StarLion View Post
    PHP Code:
    $qstringvals = array();
    $paramlist "";

    for (
    $i 0$i number_of_records$i++) {
    $qstringvals[] = "(?, ?, NOW())";
    $paramlist .= "ii"

    So lets say there were 3 records.
    $qstringvals is now an array of size 3, each element containing the string "(?, ?, NOW())"
    $paramlist is now "iiiiii" (If you wanted strings, put the s's in there instead, whatever format it is.
    I see you like arrays!! (Personally, I hate them because I have never felt strong using them...)

    Wouldn't it have been easier to just build things out like this....

    PHP Code:
    $paramlist "";

    for (
    $i 0$i number_of_records$i++) {
        
    $placeholders =. '(?, ?, NOW()), ';
        
    // I guess you need a way to remove the trailing comma?!
        
    $paramlist .= "ii"



    Quote Originally Posted by StarLion View Post
    $parameters must be an array of size 6 (all integers; what values they hold are not relevant)

    PHP Code:
    $q3 "INSERT INTO private_msg_recipient(member_id_to, message_id, created_on) VALUES ".implode(',',$qstringvals); 
    turns $q3 into "INSERT INTO private_msg_recipient(member_id_to, message_id, created_on) VALUES (?, ?, NOW()),(?, ?, NOW()),(?, ?, NOW())"
    Again, maybe it would be easier to drop the array stuff and just append my string from above to this Query String?



    Quote Originally Posted by StarLion View Post
    This then gets Prepared.
    PHP Code:
    call_user_func_array('mysqli_stmt_bind_param'array_merge (array($stmt3$paramlist), $parameters); 
    Not to sound like a broken record, but is it necessary to only use arrays and array function here? (Just asking.)


    Quote Originally Posted by StarLion View Post
    This then (so the example goes) takes your parameters, and binds them all into the statement because of how it smashes all the values together. It's sort of a weakness of the mysqli function that makes this necessary (if the third parameter of bind_param could accept arrays [or in fact, have only 3 parameters - the third being a Mixed type], you wouldnt have an issue here.)

    This is also why I prefer PDO, because you can simply loop the bindParam calls, making it much more readable.
    You lost me in what the variable $parameters holds...

    Sincerely,


    Debbie

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    What's the point of using prepared statements for a long query that may insert 1000 rows? This will certainly work but the code for doing it will not be very readable because you have to build/bind two separate data sets - one with SQL and placeholders and the other with the data. With mysqli it's necessary to keep them synchronized and use arrays, with PDO it's slightly easier but still the placeholder names need to be synchronized. While it's not some huge complex task to do it's much simpler just to build a single insert SQL string by concatenating data in a single variable. While prepared statements have their place in my opinion they aren't helping in any way in this particular case.

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    What's the point of using prepared statements for a long query that may insert 1000 rows? This will certainly work but the code for doing it will not be very readable because you have to build/bind two separate data sets - one with SQL and placeholders and the other with the data. With mysqli it's necessary to keep them synchronized and use arrays, with PDO it's slightly easier but still the placeholder names need to be synchronized. While it's not some huge complex task to do it's much simpler just to build a single insert SQL string by concatenating data in a single variable. While prepared statements have their place in my opinion they aren't helping in any way in this particular case.
    In this instance I would never have more than 10 records being inserted. (It's for sending PM's to multiple people, so it's a rather manageable size.)

    As far as using regular mysqli, well, I have always used Prepared Statements for security reasons, and while I hear you can make a regular mysqli query just as safe, that is a topic for another day!!

    Besides, I do NOT trust my programming abilities enough to take that leap. And, my users' security trumps programming convenience on my end!!

    Care to help solve the problem that StarLion started above?

    Sincerely,


    Debbie

  8. #8
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Well, using mysqli_escape_string() or casting to (int) for security is hardly another topic because it's as simple as calling a function.

    But anyway, if you are going to insert only 10 records the whole thing is not worth the hassle - just perform 10 separate inserts. If your table is Innodb then surround the whole thing in a transaction and this will perform very fast. If the table is MyIsam then there is no need for a transaction, the inserts will be fast anyway. For this purpose prepared statements fit very well, just make one statement exactly like your $stmt3 and then loop over you data and in each iteration use mysqli_stmt_bind_param() and mysqli_stmt_execute() on the same statement to execute the insert. StarLion's idea is all fine, just a bit complicated IMHO for such a simple task.

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    Well, using mysqli_escape_string() or casting to (int) for security is hardly another topic because it's as simple as calling a function.
    On another day I'll revisit this...


    Quote Originally Posted by Lemon Juice View Post
    But anyway, if you are going to insert only 10 records the whole thing is not worth the hassle - just perform 10 separate inserts. If your table is Innodb then surround the whole thing in a transaction and this will perform very fast. If the table is MyIsam then there is no need for a transaction, the inserts will be fast anyway. For this purpose prepared statements fit very well, just make one statement exactly like your $stmt3 and then loop over you data and in each iteration use mysqli_stmt_bind_param() and mysqli_stmt_execute() on the same statement to execute the insert. StarLion's idea is all fine, just a bit complicated IMHO for such a simple task.
    I appreciate that, but my goal is to learn: "How do I use a Prepared Statement to do an INSERT of Multiple Records at one time?"

    The more ways I know how to solve the same problem, the better...

    Sincerely,


    Debbie

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I see you like arrays!! (Personally, I hate them because I have never felt strong using them...)

    Wouldn't it have been easier to just build things out like this....

    PHP Code:
    $paramlist "";

    for (
    $i 0$i number_of_records$i++) {
        
    $placeholders =. '(?, ?, NOW()), ';
        
    // I guess you need a way to remove the trailing comma?!
        
    $paramlist .= "ii"

    Your comment is precisely it. You could, indeed, build the string, and then trim off the trailing comma.

    Again, maybe it would be easier to drop the array stuff and just append my string from above to this Query String?
    Is how it would be done, yes.

    Not to sound like a broken record, but is it necessary to only use arrays and array function here? (Just asking.)
    Actually here it IS required to use the arrays. bind_param doesn't take strings in 'correctly' (or at least, in the way you might expect), so you have to compile a list of parameters as an array and then use the call to push all the elements of the array onto the function. Even if you'd assembled the parameters as a string, you'd have to split the string into an array at this point.


    You lost me in what the variable $parameters holds...
    $parameters holds the data you actually want to go into your database. So for the int case, you'd have an array of 6 (or X * 2, in generic terms) integers.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


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
  •