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)

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

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)

5+ Year Member

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)

Featured Threads

Hot Threads This Week

Hot Threads This Month