PHP MYSQLI insert multiple rows in DB table

[lunaticbit]

Seekin a way to insert multiple rows In a DB Table. The number of rows is not the same every time, but can vary every time.
The aim is to use a form that can accept the fields (column values) for each row.
Every attempt to find help was unsuccessfully.

[Nobbie]

You can insert multiple rows from one table into another table by using "INSERT into TABLE SELECT ...." Statement. Google for that statement in order to find documentation and tutorials.

You cannot insert multiple rows from a HTML table or from PHP data, only from one SQL Table to another. On the other hand, it is very easy in PHP to loop over a given number of fields and do an INSERT on the current value. There is no need for a multiple row input in one SQL Statement.

Example:

Code: Select all

<?php
...
     foreach ($myarray as $myvalue) {
               $sql = "INSERT into Table (myfield) VALUES ('$myvalue')";
               $res = $mysqli->query($sql);

              if (!$res)
                  die($mysqli->error);
     }
...
?>

[Altrea]

You cannot insert multiple rows from a HTML table or from PHP data, only from one SQL Table to another.

Sorry, but that is wrong. You can create a statement like this with PHP

Code: Select all

INSERT INTO `table` (`field1`, `field2`, `field3`) VALUES ('value 1', 'value 2', 'value 3'), ('value 11', 'value 12', 'value 13');

if you want to loop over a PHP value array, do this to create such a SQL statement to insert multiple rows with just one SQL command.
Please never ever start to learn to create single statements in a PHP loop. This will work too but is a bad practice and will be one of the first places you will need to start optimizing your code.

Even better use MySQLi prepared statements for multiple inserts. Examples can be found via google, for example here:
http://stackoverflow.com/questions/1951 ... sert-query

[Nobbie]

You cannot insert multiple rows from a HTML table or from PHP data, only from one SQL Table to another.

Sorry, but that is wrong. You can create a statement like this with PHP

Code: Select all

INSERT INTO `table` (`field1`, `field2`, `field3`) VALUES ('value 1', 'value 2', 'value 3'), ('value 11', 'value 12', 'value 13');

Of course, that works. But you have to create the INSERT Statement dynamically depending on the number of rows and then it looks quite easer (to me) simply to INSERT one row per loop.

Even better use MySQLi prepared statements for multiple inserts. Examples can be found via google, for example here:
http://stackoverflow.com/questions/1951 ... sert-query

Prepared statements for a couple of rows? Overkill.

[Altrea]

Of course, that works. But you have to create the INSERT Statement dynamically depending on the number of rows and then it looks quite easer (to me) simply to INSERT one row per loop.

You can do it even without a loop

Code: Select all

$array = array('value1', 'value2', 'value3');

$sql = "INSERT INTO `table` (`field1`) VALUES ('";
$sql .= join("'), ('", $array);
$sql .= "');"; 

even as 1-liner

Code: Select all

$array = array('value1', 'value2', 'value3');

$sql = "INSERT INTO `table` (`field1`) VALUES ('" .join("'), ('", $array). "');";
 

If each array row has multiple values you surely need to adjust that code, but that is the same with your statement.

Prepared statements for a couple of rows? Overkill.

No. I use prepared statements with every statement. You just don't speed up adding multiple rows, you add an amount of security to prevent SQL Injections.

[Nobbie]

No. I use prepared statements with every statement. You just don't speed up adding multiple rows, you add an amount of security to prevent SQL Injections.

Prepared statements do not speed up modern PCs at first. Maybe a slight advantage for 1 Million rows in a loop. The SQL Interpreter time is nearly zero in comparison to the physical input/output. At next, prepared statements conflicts badly with the idea of modern programming languages, as they are NOT part of the programming logic, but only for hardware and runtime optimizing. That is in fact bad practice and this kind of optimization *should* be done by the interpreter (or compiler), but NOT by the application programmer.

Last not least, SQL injections can be easily avoided even without prepared statements, mostly SQL injections are due to sluggish programmers.

I never ever used prepared statements and i will never do so.