Many INSERT Statements

[AltarofScience]

I need to insert a large variable number of records into a mysql table.

I can do this using a while loop and insert queries in the loop but its slow and ive been given to understand its a bad idea.

I have heard that I can insert multiple rows with one query, but how to construct the query?

 

This code can perfectly and reliably display a statement with echo:

<?php
$querytestquery='"INSERT INTO test(qval1, qval2) VALUES';
echo $querytestquery;
$querymax=10;
$querystart=0;
while ($querystart<$querymax) {
$qval1=1*$querystart;
$qval2=2*$querystart;
$queryadd="($qval1, $qval2)";
echo $queryadd;
if ($querystart<$querymax-1) {
echo ',';
}
$querystart++;
}
echo '"';
?>

 

"INSERT INTO test(qval1, qval2) VALUES(0, 0),(1, 2),(2, 4),(3, 6),(4, ,(5, 10),(6, 12),(7, 14),(8, 16),(9, 18)"

 

But I don't understand how to set a variable equal to the displayed query so that I can have php construct the statement based on the values and the number of records I want to update or insert.

[litebearer]

The following example presumes you know how to add your data to an array.

<?PHP
/* start by putting all your values into an array */
$my_values_array = array ("0,0","1,2","2,4","3,6","4,8","5,10","6,12","7,14","8,16","9,18");

/* count the elements */
$c = count($my_values_array);

/* set the begining of the query string */
$query = "INSERT INTO test(qval1, qval2) VALUES";

/* loop thru the elements, adding them to the query string */
for($i=0;$i<$c;$i++) {
$query = $query . "(" . $my_values_array[$i] . "), ";
}

/* clean up the query string  - removing the lsat comma and space */
$query = trim($query);
$query = substr($query, 0, -1);

echo $query;
?>

[AltarofScience]

that will work even though every time I run the script I have a different number of values?

[AltarofScience]

Nvm. I totally figured it out and no need for arrays.

 

<?php
$querytestquery='"INSERT INTO test(qval1, qval2) VALUES';
$querymax=10;
$querystart=0;
while ($querystart<$querymax) {
$qval1=1*$querystart;
$qval2=2*$querystart;
$queryadd="($qval1, $qval2)";
if ($querystart==$querymax-1) {
$querytestquery=$querytestquery.$queryadd;
} else {
$querytestquery=$querytestquery.$queryadd.',';
}
$querystart++;
}
$query=$querytestquery.'"';
echo $query;
?>

 

Result:

"INSERT INTO test(qval1, qval2) VALUES(0, 0),(1, 2),(2, 4),(3, 6),(4, ,(5, 10),(6, 12),(7, 14),(8, 16),(9, 18)"

 

And if I change $querymax it still works perfectly.

[AltarofScience]

Okay, now I want to be able to do the same thing with UPDATE. It appears I have 3 options.

I can use REPLACE

I can use ON DUPLICATE KEY UPDATE

I can use a Temporary Table

 

Which of these methods is faster to update multiple rows in a table?

[PFMaBiSmAd]

A multi-value REPLACE query is the fastest. The code is also the same as for your multi-value INSERT query but with the REPLACE keyword instead of INSERT.

[AltarofScience]

A multi-value REPLACE query is the fastest. The code is also the same as for your multi-value INSERT query but with the REPLACE keyword instead of INSERT.

 

sweet thanks. saves me a lot of trouble so I don't have to create a whole new while loop format.

[SergeiSS]

Just one remark about your code. You are trying to create the whole string during while() loop. Do it in another way. I'll show the scheme that could be useful in many situations:

 

$query_parts=array();
while( ...any condition...)
{
  ... // some code
  $query_parts[]="($qval1, $qval2)"; // or any other code to do it
}
$querytestquery='"INSERT INTO test(qval1, qval2) VALUES ". implode( ' , ' , $query_parts); 

echo $querytestquery; // if you need it, of course

[AltarofScience]

Just one remark about your code. You are trying to create the whole string during while() loop. Do it in another way. I'll show the scheme that could be useful in many situations:

 

$query_parts=array();
while( ...any condition...)
{
  ... // some code
  $query_parts[]="($qval1, $qval2)"; // or any other code to do it
}
$querytestquery='"INSERT INTO test(qval1, qval2) VALUES ". implode( ' , ' , $query_parts); 

echo $querytestquery; // if you need it, of course

 

Yeah I don't really understand or use explode and implode.

 

In any case I can now handle 40x as many inserts or updates of rows as I could before I did the multi row queries. 50 rows to 2000. Thanks guys.

[SergeiSS]

Implode, I was correct. The only thing - I used one extra (unnecessary) apostrophe in the beginning of the string, before INSERT.