CODEWITHSUNDEEP
phploopsmysqlisql-insertsubmission
ak85
ak85

Reputation: 4264

Create an HTML form with repeatable fields and insert form submission as multiple rows into database

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?

Upvotes: 0

Views: 5727

Answers (2)

HexAndBugs
HexAndBugs

Reputation: 5789

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.

Upvotes: 5

mickmackusa
mickmackusa

Reputation: 47764

@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:

  1. Check that there was a submission containing name data.
  2. 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();
    }
}

Upvotes: 2

Related Questions