More efficient way of inserting data into mysql using PDo

Question

Ok so I execute the following code for inserting data into database:

$db_conn->beginTransaction();
$query = $db_conn->prepare('INSERT INTO mytable(name, user) VALUES(:name, :user)');
foreach($UploadData AS $DataValue)
{
    $query->execute(array(':name' => $DataValue['Name'],':user' =>$_SESSION['user']));
}
$db_conn->commit();

Now in this code block execute() runs 100s time if I have that much data. Like before I use to do with basic mysqli concatenation and executes the query only once.

Will that can be done here with PDO also?

http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query — voodoo417, Jan 25 '14 at 13:17

Simone Nigro · Accepted Answer · 2014-01-25T13:42:11.330

1

$SQL = 'INSERT INTO mytable (name, user) VALUES';

foreach( $UploadData AS $DataValue)
    $SQL .= sprintf(" ( '%s', '%s' ),", $DataValue['Name'], $DataValue['user'] );

$SQL = substr($SQL, -1);

$query = $db_conn->prepare($SQL);
$query->execute();

Result

INSERT INTO mytable (name, user) VALUES ('VAL', 'VAL'), ('VAL', 'VAL') ....

edited Jan 25 '14 at 13:42

answered Jan 25 '14 at 13:27

Simone Nigro

4,717
2
37
72

1

read http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query?lq=1 – Simone Nigro Jan 25 '14 at 13:43

More efficient way of inserting data into mysql using PDo

1 Answers1