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
– j0k, Commented Sep 4, 2012 at 11:35

acrosman · Accepted Answer · 2012-08-30 21:34:56Z

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, 2012 at 21:34

acrosman

12.9k10 gold badges42 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

zerkms Over a year ago

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.

Dan Over a year ago

@acrosman - i've tried this one but its not working out ... no error no insert ...

acrosman Over a year ago

@Dan Did you put something in the catch clause of the try statement? Otherwise it could be silently stopping after an error.

Dan Over a year ago

@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?

bafromca Over a year ago

@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"

zerkms · Accepted Answer · 2012-08-30 21:00:35Z

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, 2012 at 21:00

zerkms

256k73 gold badges447 silver badges551 bronze badges

5 Comments

Dan Over a year ago

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

zerkms Over a year ago

@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

acrosman Over a year ago

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: stackoverflow.com/questions/1176352/… in the meantime).

Dan Over a year ago

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

zerkms Over a year ago

@acrosman: if you don't have hundreds of inserts per second - using that solution is just an overcomplication

Collectives™ on Stack Overflow

php pdo multi array insert

2 Answers 2

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related