3

I am well-versed in the old php mysql extension.

I am working on my first script that uses the mysqli extension.

I am going to be inserting a large number of rows into a table that are being generated dynamically.

Is it possible to use a prepared statement to insert multiple rows into a table without previously knowing the number of new rows that will be inserted each time?

$stmt   = $mysqli->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES ?, ?, ?;");

If that isn't possible, which would be more efficient:

  1. prepared statement, one row at a time

  2. non-prepared statement, ~50 rows at a time

    // prepared statement

$stmt   = $mysqli->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES (?, ?, ?)");

for($i=0;$i<$limit;$i++)

{

    $stmt->bind_param('iss', $id[$i], $name[$i], $type[$i]);

    $stmt->execute();

}


// non-prepared statement

$query  = "INSERT INTO `activity` (`id`, `name`, `type`) VALUES ";

for($i=0;$i<$limit;$i++)

{

    $query  .= "\n(".$mysqli->real_escape_string($id[$i]), $mysqli->real_escape_string($name[$i]), $mysqli->real_escape_string($type[$i])."),";

}

$query  = substr($query, 0, -1).';';

PHP v.5.3.8

MySQL v. 5.1.60

Austin Haskew
  • 364
  • 1
  • 8
Patrick
  • 3,142
  • 4
  • 31
  • 46
  • +1 for switching to mysqli ... the first option you wrote will not work ... as for speed, you can just test it locally and find out :D I'd be interested to know – Ja͢ck May 18 '12 at 16:30
  • You answered your question yourself . For each pair of values , bind the values and execute the statement . Idk why u asked in the first place. – Cata Cata May 18 '12 at 17:27

1 Answers1

2
$stmt = $mysqli->stmt_init();

if($stmt->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES (?, ?, ?)"))
{
   $stmt->bind_param('iss', $_id, $_name, $_type);
   for($i=0;$i<$limit;$i++)
   {
      $_id = $id[$i];
      $_name = $name[$i];
      $_type = $type[$i];
      $stmt->execute();
   }

}

should do it for you!

Richard
  • 1,024
  • 1
  • 7
  • 15
  • my problem wasn't that I needed to do multiple inserts, but that I need a variable number of parameters for a single statement. For instance, one run of the statement I would have three values fields and the next run I would have five values fields. – Patrick Aug 22 '12 at 18:17
  • Sorry, I just reread my question and I realized that you did answer the question I asked, just not the question that I THOUGHT I had asked :-) – Patrick Aug 22 '12 at 18:19
  • His question was about the performance difference between one constructed statement with all the inserts and a loop executing the prepared statements. – Barmar Aug 23 '12 at 02:43