Sometimes we all need to insert multiple rows into our database. Data can come from various sources like feeds, excel sheets etc.

So how to do that?

Goal: Insert multiple rows in efficient way.

Solution:

A direct and simple approach can be iterating the records and execute multiple insert statements.

<?php 

$array = array("name"=>"Shubham", "name"=>"Alex");

$connectionId = @mysql_connect (HOST, USER, PASS);
@mysql_select_db (DBNAME, $connecttionId);

foreach($array as $key=>$value) {
  $query = "INSERT INTO users (name) VALUES ('{$value}')";
  @mysql_query ($query);
}

@mysql_close();

?>

Above approach might not be a good one because of multiple query execution and can take time and resources.

So, is there any better approach?

The Better Way

Yes we can insert multiple rows in much more efficient way by executing a single insert statement for multiple rows. Below is the syntax.

INSERT INTO users (name) VALUES ("Shubham"), ("Alex");

Above approach can be implemented in PHP as below

<?php 

$array = array("name"=>"Shubham", "name"=>"Alex");

$connectionId = @mysql_connect (HOST, USER, PASS);
@mysql_select_db (DBNAME, $connecttionId);
$query = "INSERT INTO users (name) VALUES ";
foreach($array as $key=>$value) {
  $query .= "('{$value}'),";
}
$query = rtrim ($query, ","); // trim trailing comma.
@mysql_query ($query);
@mysql_close();

?>

That’s it guys.

Cheers!!

Happy Learning!!