homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

WebmasterWorld Senior Member sgt_kickaxe us a WebmasterWorld Top Contributor of All Time

Msg#: 4349043 posted 3:48 pm on Aug 8, 2011 (gmt 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.



5+ Year Member

Msg#: 4349043 posted 9:44 am on Aug 10, 2011 (gmt 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)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved