INSERT multiple records?

[DoubleDee]

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…

	$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

[StarLion]

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

[DoubleDee]

I’m not seeing how you go from this…

$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…

$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

[StarLion]

$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)

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

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.

[DoubleDee]

StarLion,

Deep post!!

@StarLion

$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…

$paramlist = "";

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

@StarLion

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

$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?

@StarLion

This then gets Prepared.

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

@StarLion

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

[Lemon_Juice]

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.

[DoubleDee]

@Lemon_Juice

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

[Lemon_Juice]

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.

[DoubleDee]

@Lemon_Juice

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…

@Lemon_Juice

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

[StarLion]

@DoubleDee

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…

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