How to insert multiple rows in mysql + php (A better way)

Standard

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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s