If we have repeated mysql inserts in a while or For loop, we can delay the inserts
and save the data in each loop into an array first then insert only once with the
array when the loop is completed.
Eg. in a php while loop, you may insert a row in the mysql for every while loop,
if there is 100 iterations, you insert 100 times.
The techniques will allow you to save all insert into an array first and insert once
after the iterations are completed.
I did this because i noticed each of my data inserts in the php FOR loop caused
too much delay and processing. So save them into array and insert when exist
from the loop.
How much saving? if there are 100 iterations, i save 90% of processing time!
Example:
//////////////// CREATE Array for INSERT ONCE Logging: Part 1 ///////////////////////////
// Ref: http://bytes.com/topic/php/answers/644742-insert-array-values-into-mysql
////////////////////////////////////////////////////////////////////////////////////////
// Prepare the insert command (part 1)
$v_insert_array_query_string = "INSERT INTO call_jobs_details_posted
(
job_id, to, name, total_cost, message, date_joined, date_logged
)
VALUES " ;
//////////////// CREATE Array for INSERT ONCE Logging: Part 1 ///////////////////////////
// The loop
// $output_array is an array of data you want to loop through;
for ($k=0;$k <count($output_array);$k++) // loop through individual record for the output_array
{
$v_id = $output_array[$k]["id"]; // detail_id
$v_job_id = $output_array[$k]["job_id"];
$v_to = $output_array[$k]["to"];
$v_name = $output_array[$k]["name"];
$v_total_cost = $output_array[$k]["cost1"]+$output_array[$k]["cost2"];
$v_message = "Data logged at loop $k);
$v_date_joined =$output_array[$k]["date_joined"];
// do something here if needed .....
/////////////////// Create Single SQL Inset for Logging Table: Part 2 ////////////////////
$v_insert_array_query_string .=
"(
{$v_job_id},'{$v_to}','{$v_name}',{$v_total_cost},'{$v_message}','{$v_date_joined}', NOW()
)" ;
// Add a comma if this is not the last batch for the logging Array
if($k <count($output_array)-1) { $v_insert_array_query_string .= ", "; }
// Remarks: ON DUPLICATE KEY UPDATE Syntax -- if already data exists, update
// Create unique index or use the primary key as checking.
// If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY,
// an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE
// INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
// Same as: UPDATE table SET c=c+1 WHERE a=1;
// So our array commands for the last record need to append the "on duplicate" syntax
if($k == count($output_array)-1 )
{ $v_insert_array_query_string .= " ON DUPLICATE KEY UPDATE date_joined = NOW() "; }
} // for ($k=0;$k <count($output_array);$k++) // For Loop 2
echo "<BR><BR>Echo the logging array formed:". $v_insert_array_query_string;
/ it looks like this:
INSERT INTO call_jobs_details_posted
(
job_id, to, name, total_cost, message, date_joined, date_logged
)
VALUES
(
{$v_job_id},'{$v_to}','{$v_name}',{$v_total_cost},'{$v_message}','{$v_date_joined}', NOW() ) ,
{$v_job_id},'{$v_to}','{$v_name}',{$v_total_cost},'{$v_message}','{$v_date_joined}', NOW() ) ,
...
{$v_job_id},'{$v_to}','{$v_name}',{$v_total_cost},'{$v_message}','{$v_date_joined}', NOW() )
)
ON DUPLICATE KEY UPDATE date_joined = NOW()
/
// Now insert the logging array into database:
$Result_Insert_Logging_Table = mysql_query($v_insert_array_query_string);
$dc_log = mysql_affected_rows();
// That's it. All the data are stored in an array and insert once after the loop.
// If you have 100 iterations in a loop, you need to insert 100 times using normal insert in each loop.
// With this, you only inset once.
///
// Note: do not create large array, as the logging data will be gone if something fails in the loop.