insert multiple rows into database via form

Question

In php I can add multiple rows into my database using the below

$sql = 'INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data3', 'data4'),
    ('data5', 'data6'),
    ('data7', 'data8');
';

But I can't work out how to create a form that will allow me to add multiple rows into the database.

In the past when I just want to add in one row at a time I have been able to do something like this in my form.

<input type="text" name="name" value="">

and the below in my php

$_POST['name']

but this doesn't work when you want to insert multiple rows. Can someone please point me in the right direction here?

Use the [`implode()`](http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql) PHP function that will allow you to insert multiple rows with only a single SQL statement.. — Lion, Jul 02 '12 at 22:14
"but this doesn't work" - Well... obviously. Your PHP code is clearly wrong for multiple reasons - but I guess that it's just because you posted psuedocode, right? What error message do you get? — Mark Byers, Jul 02 '12 at 22:15
Yes, I don't get any errors as such I just get the last instance of the name field entered in my database as I keep over riding it because it has the same name. — ak85, Jul 03 '12 at 00:16

HexAndBugs · Accepted Answer · 2020-03-31T11:22:59.237

5

You can use php arrays, so you do

<input type="text" name="name[]" value="">

note the square brackets added after name. You can have multiple instances of this in your form and then refer to it in the php as:

$_POST['name'][$i]

where $i is a variable indexing the value in the array.

edited Mar 31 '20 at 11:22

answered Jul 02 '12 at 22:18

HexAndBugs

5,549
2
27
36

Thanks this is helpful thanks. I needed a ( after the for so it look like this for ($1=0; now that I have done this I get no errors but the data still doesn't save in the database. Can anyone pick any syntax errors here? Or maybe I need to look at the for loop again? – ak85 Jul 03 '12 at 11:42
I suggest you output the value of $sql after the for loop has completed and post that here. I'm guessing there's some minor syntax error that will be obvious when we see the value of $sql. – HexAndBugs Jul 03 '12 at 13:28

score 2 · Answer 2 · answered Feb 11 '20 at 22:52

@HexAndBugs's answer is incorrectly quoting the table name and columns in the query. Backticks should replace these single quotes. That said, even if the syntax is corrected, the query is still insecure and unstable. An entry like Seamus O'Brien will cause the script to fail, not to mention the query is vulnerable to more sinister injection attacks. That answer simply should not be used by anyone for any reason.

Here is a complete solution assuming your form only includes name fields:

<input type="text" name="name[]">
<input type="text" name="name[]">
...repeat as many times as you like inside the form

Because the submission is intended to write data into the database, POST is the appropriate method. (GET should only be used when the intention is to fetch/read data from the database.)

In your submission receiving script, use the following snippet to:

Check that there was a submission containing name data.
Use a prepared statement with bound parameters and iterated executions for stability/security. I will not include a sanitizing step because I don't know your requirements, but this would be advisable so that your database data remains clean.

Code:

if (!empty($_POST['name'])) {
    $conn = new mysqli("localhost", "root", "", "yourDB");  //apply your credentials
    $stmt = $conn->prepare("INSERT INTO `tableName` (`name`) VALUES (?)");
    $stmt->bind_param('s', $name);
    foreach ($_POST['name'] as $name) {
        // to sanitize the name string, perform that action here
        // to disqualify an entry, perform a conditional continue to avoid the execute call
        $stmt->execute();
    }
}

insert multiple rows into database via form

2 Answers2

Linked