PHP PDO batch insert from multi dimensional array failing to insert into MySQL

Question

I have an array like this which contains a lot of row and I need to insert it into a MySQL database using PDO

array(3067) {
  [0]=>
  array(2) {
    ["order"]=>
    string(7) "2854811"
    ["consignment"]=>
    string(0) ""
  }
  [1]=>
  array(2) {
    ["blah"]=>
    string(7) "2854811"
    ["whatever"]=>
    string(2) "someval"
  }
  [4]=>
  array(2) {
    ["blah"]=>
    string(7) "2864412"
    ["whatever"]=>
    string(0) ""
  }

I have tried various combinations of suggestions made on here but each suggestion gets a different error message

php PDO insert batch multiple rows with placeholders

PDO MySQL: Insert multiple rows in one query

I have tried this

$db->beginTransaction();

$stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

foreach($test as $insertRow){

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

$db->commit();

but i get this error message

Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

and I've also tried

$sql = 'INSERT INTO mytable (blah, whatever) VALUES ';
$insertQuery = array();
$insertData = array();
$n = 0;
foreach ($input as $row) {
    $insertQuery[] = '(:blah' . $n . ', :whatever' . $n . ')';
    $insertData['blah' . $n] = $row['blah'];
    $insertData['whatever' . $n] = $row['whatever'];
    $n++;
}

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

but i get this error message which makes no sense as each length of 'blah' are the same

Uncaught exception 'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'order_number' at row 1625'

How can i get my array to insert into the database? I'm not fussy if i have to execute a load of times or just once as long as I can get it to insert.

EDIT

What I am trying to do is read in a text file and insert it into an array which works perfectly so all i'm left with is an associative array with about 3000 rows and they each contain a field called 'blah' and 'whatever'.

After I get my array, i need to insert it into a MySQL database

CREATE TABLE IF NOT EXISTS `tracker` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `blah` varchar(8) NOT NULL,
  `whatever` varchar(25) NOT NULL,
  `input_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

I should end up with about 3000 rows that was inserted from my array.

I hope this makes sense. If not I'll add some more

Answer 1

I believe you almost had it with this example:

$db->beginTransaction();

$stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

foreach($test as $insertRow){

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

$db->commit();

The problem you are running into is you are calling execute() before you have bound the proper number of parameters. Instead, you need to bind all of your parameters first, then call execute().

$db->beginTransaction();

$stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

foreach($test as $insertRow){

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

}

// NOW DO EXECUTE
$stmt->execute();

$db->commit();

EDIT

In response to your comment, it's hard to tell exactly what you are trying to accomplish, but if you are only receiving one record, then it is because of what Gerald brought up, these are separate queries to all be transacted at once. Take a look at this revision:

// Start Transaction
$db->beginTransaction();

// Insert each record
foreach($test as $insertRow){

   // Prepare statement
   $stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

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

   // Execute statement to add to transaction
   $stmt->execute();

   // Clear statement for next record (not necessary, but good practice)
   $stmt = null;
}

// Commit all inserts
$db->commit();