PHP mysqli reusing prepared statements - arrays

Question

I have a script that inserts an array of rows into a database, where each row is represented by a subarray. Recently, I realized that I was using prepared statements in PHP/MySQLi incorrectly, as I was re-preparing the same statement as I iterated through the big array. I then converted the code to the following:

$t = reset($data);
$stmt = $con->prepare("INSERT INTO tlist VALUES (?,?,?)");
$stmt->bind_param('isi',$t['id'],$t['flag'],$t['cost']);
foreach ($data as $t) {
    $stmt->execute();
}
$stmt->close();
$con->close();

However, this now attempts to insert the values in the first sub-array of $t every iteration of the loop. What am I doing wrong here? Is there a way to retain the increased performance of the prepared statement without having to manually assign the subarrays' values into standard variables first?

EDIT: try to remove duplicate tag

Possible duplicate of [How to bind param multiple times with MySQLI?](http://stackoverflow.com/questions/7980140/how-to-bind-param-multiple-times-with-mysqli) — Jeff, Jul 09 '16 at 21:23
In this doc (http://www.php.net/manual/en/mysqli-stmt.execute.php) Example #1 shows params can be changed between executions without rebinding them... — Sunny, Jul 09 '16 at 21:35
This could help: http://stackoverflow.com/questions/15149157/best-way-to-insert-many-values-in-mysqli — random_user_name, Jul 09 '16 at 21:36

rhazen · Answer 1 · 2016-07-10T03:09:59.397

You're declaring the bind variables before the foreach loop. If you set auto commit to false you really get the potential out of it.

$t = reset($data);
$stmt = $con->prepare("INSERT INTO tlist VALUES (?,?,?)");
$stmt->bind_param('isi', $id, $flag, $cost);

$con->autocommit(false);

foreach ($data as $t):

    $id   = $t['id'];
    $flag = $t['flag'];
    $cost = $t['cost'];

    $stmt->execute();

endforeach;

$stmt->close();

$con->commit();
$con->close();

Edgars Aivars · Answer 2 · 2016-07-09T21:59:33.787

$question_marks = '';
$types = '';
$values = array();

foreach ($data as $t) {
  if(!empty($question_marks)){
    $question_marks.= ',';
  }
  $question_marks.= "(?,?,?)";
  $types.= 'isi';
  $values[] = $t['id'];
  $values[] = $t['flag'];
  $values[] = $t['cost'];
}
array_unshift($values,$types);
$query = "INSERT INTO `tlist` (`id`,`cost`,`flag`) VALUES ".$question_marks;
$statement = $con->prepare($query);
$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod("bind_param");
$method->invokeArgs($statement,$values);
$statement->execute();
$statement->close();

Something like that
EDITED: in my example connection variable was $db -> changed to yours -> $con

PHP mysqli reusing prepared statements - arrays

2 Answers2