|MySql array? When php is in a loop can the data be inserted later?|
| 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.
| 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):
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)