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:
- prepared statement, one row at a time
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 .= " (".$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
See Question&Answers more detail:os