PDO MySQL: Insert multiple rows in one query

Question

Hello I am making a class for doing multiple insert in pdo.

It is something like this

INSERT INTO $table (key1,key2,key3,etc) VALUE (value1,value2,value3,etc), (value1,value2,value3,etc), (value1,value2,value3,etc)

After searching I found out that I have to build something like

INSERT INTO $table (key1,key2,key3,etc) VALUE (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc)

then execute with this $this->execute($data); where $data is

 0 => 
    array
      'key1' => 'value1'
      'key2' => 'value2'
      'key3' => 'value3'
 1 => 
    array
      'key1' => 'value1'
      'key2' => 'value2'
      'key3' => 'value3'

 etc

the problem is i still get an error Array to string conversion on $insert->execute($data); how can i fix that?

here's a snippet of what I'm making.

public function multipleInsert($table, $data = array()) 
{

    # INSERT (name) VALUE (value),(value)
    if (count($data) > 1) 
    {
        $fieldnames = array_keys($data[0]);
        $count_inserts = count(array_values($data));
        $count_values = count(array_values($data[0]));

        # array(????) untill x from first data
        for($i = 0; $i < $count_values; $i++)
        {
            $placeholder[] = '?';
        }

        # array((????),(????),(????)) for query
        for ($i=0; $i < $count_inserts; $i++) 
        { 
            $placeholders[] = '('. implode(',',$placeholder) . ')';
        }

        $query  = 'INSERT INTO '. $table;
        $query .= '(`'. implode('`, `', $fieldnames) .'`)';
        $query .= ' VALUES '. implode(', ', $placeholders);

        $insert = $this->start->prepare($query);

        $i = 1;
        foreach($data as $item) 
        {
            foreach ($item as $key => $value) 
            {
               $insert->bindParam($i++, $item[$key]);
            }
        }

        echo $query;
        $insert->execute();

        $return['status'] = true;
        $return['lastid'] = $this->start->lastInsertId();

        return $return;
    } 
    else 
    {
        die('$data is less then two array, use single insert instead.');
    }
}

score 35 · Accepted Answer · edited Apr 22 '15 at 09:00

35

An easy way for this avoiding the complications would be something like this

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);
    $stmt->execute();
}

However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.

Here is an example of how we can do this.

$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .=  implode(",",$qPart);
$stmt = $dbh -> prepare($query); 
$i = 1;
foreach($data as $item) { //bind the values one by one
   $stmt->bindValue($i++, $item['key1']);
   $stmt->bindValue($i++, $item['key2']);
}
$stmt -> execute(); //execute

edited Apr 22 '15 at 09:00

aleemb

31,265
19
98
114

answered Apr 08 '12 at 06:00

Starx

77,474
47
185
261

so what it does it execute 4 times? im looking a way to insert multiple data in a single query, by looking at your code it execute multiple times dependent on how much data. – Adam Ramadhan Apr 08 '12 at 06:01
2

@AdamRamadhan, As I told you it is a way to do avoiding complications. I will update with the combined one a bit later. – Starx Apr 08 '12 at 06:07
ok let me try to figure out with an unfixed input like `0 => array 'key1' => 'value1' 'key2' => 'value2' 'key3' => 'value3' 1 => array 'key1' => 'value1' 'key2' => 'value2' 'key3' => 'value3' etc` – Adam Ramadhan Apr 08 '12 at 06:25
hello! thanks it work! anyway please see my updated code, is there anything that i miss or not good? – Adam Ramadhan Apr 08 '12 at 07:16
@AdamRamadhan, Of course it works :P and your code looks fine. – Starx Apr 08 '12 at 07:18
3

wouldn't this throw a SQL syntax error because there's one left "," in the query? I find it strange that PDO doesn't include a way to bind arrays of values – Sandro Antonucci Apr 17 '12 at 22:00
@SandroAntonucci. Good Spot. Fixed it :) – Starx Apr 18 '12 at 01:19
how do you insert by checking for duplicates? @Starx – shanks Nov 12 '12 at 16:06
It would also be adviseable to array_chunk your array in case they size is very large - it would take another loop, but its a bit easier on mySQL. Also, don't forget "on duplicate key update..." – Ross Aug 07 '13 at 22:37
@Ross, I dont understand your point. What are you trying to say and to whom? – Starx Aug 08 '13 at 03:48
This might cause SQL server errors like "too many queries" depending on the max query limit. – kubilay Jan 09 '14 at 07:23
1

It will not work because it always gets the last `$item` value from the loop since `bindParam` works by reference and not by value. You need to use `bindValue` instead. – Capsule Mar 24 '15 at 01:11
I like your solution. Thank you for your time. – kta Apr 01 '15 at 00:34
I used this as well. Thanks – J2112O Jan 13 '21 at 22:26

PDO MySQL: Insert multiple rows in one query

1 Answers1

Linked

Related