php PDO insert batch multiple rows with placeholders

Question 1

I am looking to do multiple inserts using PHP PDO.

The closest answer I have found is this one

how-to-insert-an-array-into-a-single-mysql-prepared-statement

However the example thats been given uses ?? instead of real placeholders.

I have looked at the examples on the PHP doc site for place holders

php.net pdo.prepared-statements

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Now lets say I wanted to achieve the above but with an array

$valuesToInsert = array(
  0 => array('name' => 'Robert', 'value' => 'some value'),
  1 => array('name' -> 'Louise', 'value' => 'another value')
);

How would I go about it with PDO and multiple inserts per transaction?

I imagine it would start of with a loop?

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");

foreach($valuesToInsert as $insertRow){

    // now loop through each inner array to match binded values
    foreach($insertRow as $column => value){
        $stmt->bindParam(":{$column}", value);
    }
}
$stmt->execute();

However the above does not work but hopefully will demonstrate what im trying to achieve

Question 2

First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:

How to insert multiple rows with a single query
How to generate SQL dynamically
How to use prepared statements with named place-holders.

It's fairly trivial to combine them all:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
$n = 0;
foreach ($data as $row) {
    $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
    $insertData['memberID' . $n] = $memberid;
    $insertData['programID' . $n] = $row;
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}

Question 3

You're probably looking for lastInsertId. "Returns the ID of the last inserted row or sequence value".

$insertedId = $system->db->lastInsertId() ;

Question 4

Here is the right way:

function INSERT($db)
{
    $values = array('question goes here', -1, 'a|b|c|d|e', '01', '0|', '0|', '0|', '0', '2', 'info text', '1', '23.01.2014', '-1', '28.12.2013 15:04:03');
    $stmt = $db->prepare("INSERT INTO _mcqs VALUES(?,?,?,?)"); // adjust number of ?s
    $stmt->execute($values);
}

do not connect in the every function, but use sole connection for all the application
do not use try..catch to log an error - PHP will handle it better
do no bother with listing all the fields
do not bind each value separately if you already have an array

Question 5

Multiple Values Insert with PDO Prepared Statements

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array('fielda', 'fieldb', ... );

$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);

more data values or you probably have a loop that populates data.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

That is basically how we want the insert statement to look like.

Now, the code:

function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
    $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
    $insert_values = array_merge($insert_values, array_values($d));
}

$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
       implode(',', $question_marks);

$stmt = $pdo->prepare ($sql);
$stmt->execute($insert_values);
$pdo->commit();

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

Question 6

Leave the columns you don't wanto to fill with null

INSERT INTO mytable(`People`, `Places`, Things, Ideas) 
VALUES ('40', '15', null, null),(null, null, 100, 20)

php PDO insert batch multiple rows with placeholders

Answers

php,insert,pdo,bulkinsert