Insert multiple rows into a single query from an array

Question

I have an array from the message:

//this are arrays $name = $_POST["name"]; $age = $_POST["age"]; $date = $_POST["date"]; 

I have an insert request in PDO:

 $stmt = $db->prepare("INSERT INTO staff (name, age, address) VALUES (:name, :age, :address)"); 

my question is how can I generate / or execute a request using php that looks like this:

 INSERT INTO staff (name, age, address) VALUES ($name[0], $age[0], $address[0]), ($name[1], $age[1], $address[1])... etc 

I am trying to maintain a low server load.

Answer 1

This is not possible with prepared instructions.

If you want to insert a variable number of records in one query, then the SQL query must be generated dynamically, which makes it impossible to prepare it beforehand.

If you use the database driver function escape_string (which would be quote() for MySQL), you can create a query and still receive the security information that the instructions prepared.

 $query = "INSERT INTO table (a, b, c) VALUES"; $tuple = " ('%s', '%s', '%s'),"; foreach ($items as $item) { $a = $db->quote($item['a']); $b = $db->quote($item['b']); $c = $db->quote($item['c']); $query .= sprintf($tuple, $a, $b, $c); } $query = rtrim($query, ","); // Remove trailing comma // Use the query... 

Addendum:

If you use prepared instructions, you can insert records separately and not worry about pasting them at a time. This is actually the whole point of the prepared statement.

Unlike the old old SQL queries, which are a one-step process and are simply sent and then forgotten ...

 $db->query("INSERT INTO table (a, b, c) VALUES ('a', 'b', 'c')"); 

... prepared statements are a two-step process.

First you create a prepared statement. This statement is then sent to the database, telling it that "this is what you expect." The database often also optimizes the query to make it faster. This step then returns you an instruction handle (often called $stmt in the PHP documentation).

 $stmt = $db->prepare('INSERT INTO table (a, b, c) VALUES (:a, :b, :c)'); 

Secondly, with this descriptor you can go to the insert:

 foreach ($records as $record) { $stmt->execute(array( ':a' => $record['a'], ':b' => $record['b'], ':c' => $record['c'], )); } 

Since the database already knows what to expect, it can optimize INSERT s speed, which means that you do not need to go through the material mentioned above in this appendix.

Wikipedia has a pretty nice record of ready-made statements.

Answer 2

You can prepare an appropriate statement, for example:

 $sql = "INSERT INTO staff (name, age, address) VALUES "; $values = array(); for ($i = 1; $i <= $count; $i++) { $values[] = "(:name$i, :age$i, :address$i)" } $stmt = $db->prepare($sql . implode(', ', $values)); 

But, imho, you are likely to be as good at each set of values. This is not a big part of the server load, and your code will be simpler.

Answer 3

If you use PDO, you must prepare your statement and execute it with different values:

 $stmt = $db->prepare("INSERT INTO staff (name,age,address) VALUES (:name,:age,:address)"); for($i=0;$i<count($name);$i++) { $stmt->execute(array( ":name" => $name[$i], ":age" => $age[$i], ":address" => $address[$i])); } 

However, if you want to put it in one request, you should use $ db → exec ()

 $query = "INSERT INTO staff (name,age,address) VALUES "; $values = array(); for($i=0;$i<count($names);$i++) { array_push($values, "('" . $name[$i] . "','" . $age[$i] . "','" . $address . "')"); } $db->exec($query . implode("," . $values)); 

Warning This method, contrary to the preparation and execution method, is vulnerable because it does not validate input

Answer 4

Add the delayed keyword to the insert ( insert delayed ... ) to reduce server load, and just loop the prepared statement with all the values. MySQL will queue it up anyway as separate insert , if you do a large set of inserts, the extended syntax is just syntactic sugar.