Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MySql array? When php is in a loop can the data be inserted later?

3:48 pm on Aug 8, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member sgt_kickaxe is a WebmasterWorld Top Contributor of All Time 5+ Year Member

joined:Apr 14, 2010
votes: 0

My php script runs a loop. Each time the loop runs one course the database is asked to create a new row. There are anywhere from 40 to 120 such loops per page load resulting in 40-120 different requests on the database.

While performance doesn't seem to be suffering there has to be a better way (before my host comes screaming). How would I run a loop 40 times and THEN make a database call to store the results of all loops together at once?

example: 4 fields containing name, number, address, phone number. One page load yields 40-120 new 'rows' to insert, the latest clients to join the club.
9:44 am on Aug 10, 2011 (gmt 0)

Preferred Member

5+ Year Member

joined:Aug 18, 2008
votes: 0

Though I've never used multiple VALUES lists within INSERT statements myself, they say it's possible and much quicker than multiple individual INSERT statements.

Speed of INSERT statements [dev.mysql.com]
If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.

INSERT syntax [dev.mysql.com]
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The values list for each row must be enclosed within parentheses.

Actually, now that I know this it will make a project of mine which I was dreading much quicker and easier too.

So, for your case, each time the loop runs push the values as a string into an existing array (pseudo example php code):
array_push($my_VALUES_array, "($name,$number,$address,$phone_number)");
You may need to play with quote marks and such, make sure strings are quoted and properly cleansed of course.
Then when you've collected all the values and are ready to make your single INSERT statement,
$query = 'INSERT into the_table_name (name, number, address, phone_number) VALUES '.implode(',', $my_VALUES_array)
$resutl = mysql_query($query)