php pdo multi array insert

Question

I've been playing around for few hours and trying to sort this out but looks like a hard nut to crack.

I'm able to do a single array insertion

$person = array('name' => 'Wendy', 'age' => '32');

but if I want multiple like this:

$person = array(array('name'=>'Dan', 'age'=>'30'), array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32'));

It's not working? Any help would be appreciated.

For multiple insertion:

public function insertPdo($table, $data){
    try{
        if (!is_array($data) || !count($data)) return false;

        $bind = ':' . implode(', :', array_keys($data));      
        $sql = 'INSERT INTO ' . $table . ' (' . implode(', ',array_keys($data)) . ') ' . 'values (' . $bind . ')';

        $sth = $this->__dbh->prepare($sql);
        $result = $sth->execute($data);

    }
    catch(PDOException $e){
        echo $e->getMessage();
    }
}

For Single Insertion

$person = array('name'=>'Dan', 'age'=>'30');
$db->insertPdo('test_pdo',$person);

// For Multi Insertion, I'm trying to use this in above function
foreach ($data as $row) {
    $result = $sth->execute($row);
};

$person = array(array('name'=>'Dan', 'age'=>'30'), array('name' => 'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32'));
$db->insertPdo('test_pdo',$person);

And the error:

Error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Show us how you perform the single array insertion. And also the error you got when performing the multiple. — j0k, Sep 04 '12 at 11:35

score 3 · Answer 1 · answered Aug 30 '12 at 21:34

3

To take advantage of the insert speed of multiple inserts in MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html ), you can use a prepared statement that builds the larger query. This does add complexity over an more iterative approach, so is probably only worth it for high-demand systems or largish data sets.

If you have your data as you proposed above:

$person = array(array('name'=>'Dan', 'age'=>'30'), array('name' =>
'John', 'age' => '25'), array('name' => 'Wendy', 'age' => '32'));

We're looking to generate a query that looks something like this:

insert into table (name, age) values (?,?), (?,?), (?,?);

To pull this together you'll want something not totally unlike this:

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

$sql = "INSERT INTO table_name (name, age) VALUES " . implode(',', $question_marks);

$stmt = $pdo->prepare ($sql);
try {
    $stmt->execute($insert_values);
} catch (PDOException $e){
    // Do something smart about it...
}
$pdo->commit();

answered Aug 30 '12 at 21:34

acrosman

12,814
10
39
55

1

Unless you have issues with performance - this solution is a terrible overcomplication. Prepared statements were designed to perform the same query with different parameters several times. And there are rare reasons to not follow this straightforward idea. – zerkms Aug 30 '12 at 22:36
@acrosman - i've tried this one but its not working out ... no error no insert ... – Dan Sep 04 '12 at 10:16
1

@Dan Did you put something in the catch clause of the try statement? Otherwise it could be silently stopping after an error. – acrosman Sep 04 '12 at 13:20
@acrosman - fantastic m8 .. its working ... however its not giving me the last inserted id ... i'm using this $this->__dbh->lastInsertId(); any thought on this? – Dan Sep 04 '12 at 13:52
@Dan @acrosman, I think what may be the issue here, is that you're flattening the value array with `array_merge`, so that instead of having 22 records of 'name' & 'age' (a multidimensional array), you would just have 44 records in a flat array? `[0] => "John", [1] => 24, [2] => "Sally", [3]=> 39, ... , [42] => "Mohammed", [43] => "42"` – bafromca Jan 19 '16 at 19:01

score 2 · Answer 2 · answered Aug 30 '12 at 21:00

2

You cannot do that automatically. Instead you have to iterate it manually and execute each record:

for ($person as $row) {
    $sth->execute($row);
}

answered Aug 30 '12 at 21:00

zerkms

249,484
69
436
539

hey thanks for the post .. .i tried but it's throwing an error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens – Dan Aug 30 '12 at 21:11
@Dan: if it works with a single row array - it would work with 2dimensional array as well. Anyway - without some particular code it's impossible to give you any further help. Short answer: it should work, and if it doesn't - you've made some mistake – zerkms Aug 30 '12 at 21:13
1

You do not have to iterate over every record. You can use the multiple insert functionality, but you will need to transform the array a bit (more complete answer coming but see: http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query in the meantime). – acrosman Aug 30 '12 at 21:17
Well, that's what I've added. foreach ($data as $row) { print_r($row); $sth->execute($row); }; Array ( [name] => Dan [age] => 30 ) SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens – Dan Aug 30 '12 at 21:20
@acrosman: if you don't have hundreds of inserts per second - using that solution is just an overcomplication – zerkms Aug 30 '12 at 21:24

php pdo multi array insert

2 Answers2

Linked