PHP MYSQLI insert multiple rows in DB table

Problems with the Windows version of XAMPP, questions, comments, and anything related.

PHP MYSQLI insert multiple rows in DB table

Postby lunaticbit » 11. January 2016 11:27

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.
lunaticbit
 
Posts: 21
Joined: 09. April 2015 16:24
Operating System: Windows 7

Re: PHP MYSQLI insert multiple rows in DB table

Postby Nobbie » 11. January 2016 12:00

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);
     }
...
?>
Nobbie
 
Posts: 8766
Joined: 09. March 2008 13:04

Re: PHP MYSQLI insert multiple rows in DB table

Postby Altrea » 11. January 2016 13:18

Nobbie wrote: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
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8288
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: PHP MYSQLI insert multiple rows in DB table

Postby Nobbie » 11. January 2016 13:56

Altrea wrote:
Nobbie wrote: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.

Altrea wrote: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.
Nobbie
 
Posts: 8766
Joined: 09. March 2008 13:04

Re: PHP MYSQLI insert multiple rows in DB table

Postby Altrea » 11. January 2016 14:18

Nobbie wrote: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.

Nobbie wrote: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.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8288
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: PHP MYSQLI insert multiple rows in DB table

Postby Nobbie » 11. January 2016 14:56

Altrea wrote: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.
Nobbie
 
Posts: 8766
Joined: 09. March 2008 13:04


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 60 guests