Insert multiple rows using single query

Question

I want to insert multipl rows in single query.

$firstname = array('Arfan','Awais','Ahmad'.....);
$lastname = array('Haider','k','Raza'..........);

Insert Data:

INSERT INTO `table_nmae`(firstname,lastname)
VALUES
('Arfan','Haider'),('Awais','k'),('Ahmad','Raza');

I can do it very easily if these are only three record.
If I did not know how many records are present then I can not able to use the above.
Then what I did do?

UPDATE:
If I can not know the numbers of record in array.I can simply use foreach loop to do this But this decrease the performance.

foreach($firstname as $f){
foreach($lastname as $l){
   INSERT INTO `table_name`(firstname,lastname)
   VALUES('$f','$l');
}

}

Now this time I am using multi query.I think single query is good to performance.

FOR ONE COLUMN:
If I had only one column it is very easy to use single query for this.

INSERT INTO `table_name`(firstname) VALUES
.implode(',', $firstname);

But here is multi column how can I do that for those.
Thanks.....

Why can't you dynamically construct the insert query to include as many rows to insert as you need? — Patashu, Jun 12 '13 at 04:08
check this, http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql — Vidya L, Jun 12 '13 at 04:10

score 0 · Answer 1 · answered Jun 12 '13 at 04:12

Use for loop if $firstname and $lastname array are of same length

$str = "INSERT INTO `table_nmae`(firstname,lastname) VALUES"; // create string of query
for($i = 0 ;$i< count($firstname); $i++) {
    $str .= "('" . $firstname[$i] . "','" . $lastname[$i] . "'),"; //append the values
}
$str = trim($str, ","); // remove last ,

score 0 · Answer 2 · answered Jun 12 '13 at 04:14

Try

$size_of_array = count( $firstname );

$query = "INSERT INTO `table_nmae`(firstname,lastname) VALUES ";
for($i = 0; $i < $size_of_array; $i++ )
{
    if( $i !== ($size_of_array-1))
      $query .= "('{$firstname[$i]}', '{$lastname[$i]}'),";
    else
     $query .= "('{$firstname[$i]}', '{$lastname[$i]}')";
}
echo $query;
?>

score 0 · Answer 3 · answered Jun 12 '13 at 04:37

0

Suppose you have array of values e.g. $values_arr.

$count = count( $values_arr );

$query = "INSERT INTO `table_nmae`(firstname,lastname) VALUES ";
for($i = 0; $i < $count; $i++ )
{
    if( $i == 0)
      $query .= "('{$values_arr[$i]['firstname']}', '{$values_arr[$i]['lastname']}')";
    else
     $query .= ",('{$values_arr[$i]['firstname']}', '{$values_arr[$i]['lastname']}')";
}
echo $query;

answered Jun 12 '13 at 04:37

Amar Banerjee

4,992
5
34
51

This is multi querys You always used `$query` to perfom this task. – Axeem Jun 12 '13 at 05:01
No, here is only 1 insert query, but inserting multiple records with it. So it will be more optimized. – Amar Banerjee Jun 12 '13 at 07:16

Insert multiple rows using single query

3 Answers3