0

I'm trying to insert multiple rows using a single prepared statement, but I can't make it work.

I have created the following helper methods:

// generate "(?, ?, ?, ?), (?, ?, ?, ?)" from cols and values
// to be used in a prepared statement
public function placeholders ($values, $cols) {
    $row = '('.implode(',', array_fill(0, count($cols), '?')).')';
    $place_holders = array_fill(0, count($values)/count($cols), $row);
    return implode(',', $place_holders);
}

// generate the "?" placeholders and make the PDO transaction
protected function insertInto ($table, array $cols, array $values) {
    $query = 'INSERT INTO `'.$table.'` (`'.implode('`,`', $cols).'`) VALUES '.$this->placeholders($values, $cols);
    return $this->makeTransaction($query, $values);
}

// prepare and execute a PDO transaction
protected function makeTransaction ($query, array $values=array()) {
    $this->pdo->beginTransaction();
    $q = $this->pdo->prepare($query);
    $q->execute($values);
    return $this->pdo->commit();
}

As for the call itself, here it is:

$this->insertInto('table', array('f_key', 'c2', 'c3'), $values);

I'm using the following $values array:

$values = array(1, 'a', 'b', 2, 'c', 'd', ...);

Now the data doesn't get into the table but at the same time I don't get any exception or error of any kind. I've tried var_dumping $this->pdo->errorInfo() after the call but it contains no error either. Any idea?

Qwindoo
  • 373
  • 1
  • 3
  • 5
  • can you make sure that PDO prepared statement emulation is turned off? That might be an issue here. – Maximus2012 Aug 05 '13 at 13:55
  • I've added `$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` and now I got this fatal error: Fatal error: Call to a member function execute() on a non-object in ... the line is that of `$q->execute($values);` – Qwindoo Aug 05 '13 at 13:59
  • Can you print out the query statements after the variables have been substituted? You'll generally find that the problem is obvious. – Gordon Linoff Aug 05 '13 at 13:59
  • Here's the generated SQL: INSERT INTO `table` (`f_key`,`c1`,`c2`,`c3`) VALUES (?,?,?,?),(?,?,?,?),... I don't know how to get the raw, substituted query however. Couldn't find anything in the doc except `$q->debugDumpParams();` which is horrible – Qwindoo Aug 05 '13 at 14:07
  • http://stackoverflow.com/a/15990858/285587 – Your Common Sense Aug 05 '13 at 14:28

0 Answers0