Insert multiple rows to database from HTML form using MySQLi

Question

I'm trying to make a form that uses arrays so once it is submitted and processed multiple rows get inserted into my database. My main program is more complex than below but I could not get it working so I decides to create a small simple program to understand the basic syntax better then apply the techniques to the main program. I have got it to work using the depreciated MySQL but converting it to MySQLi is causing problems that I wonder if I can get help with.

My form is set up like this

<html>
<title>multi row insert test form</title>
<body>
<table>
<form action="process2.php" method="post">
<tr>
<th>forename</th>
<th>surname</th>
<th>level</th>
</tr>
<tr>
<td><input type="text" name="fname[]"></td>
<td><input type="text" name="sname[]"></td>
<td> 
<select name="level[]">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select> 
</td>
</tr>
<tr>
<td><input type="text" name="fname[]"></td>
<td><input type="text" name="sname[]"></td>
<td> 
<select name="level[]">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select> 
</td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Submit"></td>
</tr>
</form>
</table>
</body>
</html>

and the php page that updates the database using MySQLi is as below

<?php
include 'dbconnect2.php';
$fname = $_POST['fname'];
$sname = $_POST['sname'];
$level = $_POST['level'];


if ($stmt = $mysqli->prepare("INSERT INTO people (fname, sname, level) values (?, ?, ?)")) {

$stmt->bind_param('ssi', $fname, $sname, $level);

for ($i=0; $i<2; $i++)
{
$fname[$i] = $fname;
$sname[$i] = $sname;
$level[$i] = $level;

$stmt->execute();
echo "Done";
}

$stmt->close();
}
?>

score 1 · Accepted Answer · answered Nov 21 '14 at 15:02

1

Or, with less rewriting your existing code:

$fnames = $_POST['fname'];
$snames = $_POST['sname'];
$levels = $_POST['level'];

$stmt = $mysqli->prepare("INSERT INTO people (fname, sname, level) values (?, ?, ?)")

for ($i=0; $i<count($fnames); $i++) {
    $fname = $fnames[$i];
    $sname = $snames[$i];
    $level = $levels[$i];
    $stmt->bind_param('ssi', $fname, $sname, $level);

    $stmt->execute();
}
echo "Done";

$stmt->close();

answered Nov 21 '14 at 15:02

Alexander Haas

278
2
11

2

`bind_param()` doesn't need to be iterated -- move that line above the `for()` line. – mickmackusa Jan 04 '20 at 21:19

mickmackusa · Answer 2 · 2020-01-06T21:19:50.687

1

It looks like you merely got your declaration syntax reversed in your loop.

Also, the first three declarations after your connection include are not necessary.

include 'dbconnect2.php';
$stmt = $mysqli->prepare("INSERT INTO people (fname, sname, level) VALUES (?, ?, ?)");
$stmt->bind_param('ssi', $fname, $sname, $level);
foreach ($_POST['fname'] as $index => $fname) {
    $sname = $_POST['sname'][$index];
    $level = $_POST['level'][$index];
    $stmt->execute();
}

edited Jan 06 '20 at 21:19

answered Jan 04 '20 at 21:31

mickmackusa

43,625
12
83
136

I would avoid putting `prepare()` inside of `if` statement. It adds no benefit and is creating a very messy code. – Dharman Jan 06 '20 at 20:09
Well, the benefit intended is that no subsequent errors will occur while binding or executing on an unsuccessfully initialized statement. In other answers, I include the failure checking for the binding and the execution. However, I don't really feel like entering the drawn out discussion on how to all developers should be logging errors in every project. I'll agree that writing conditions for each check point makes the script more bloated and harder to read. I'll just remove the condition. – mickmackusa Jan 06 '20 at 21:22

score 0 · Answer 3 · answered Nov 21 '14 at 14:59

0

Try this. You need to iterate on all your post data, and bind them IN the loop.

include 'dbconnect2.php';
for ($i = 0; $i < count($fname); $i++) {
    $stmt = $mysqli->prepare("INSERT INTO people (fname, sname, level) values (?, ?, ?)");
    $stmt->bind_param('ssi', $_POST["fname"][$i], $_POST["sname"][$i], $_POST["level"][$i]);
    $stmt->execute();
}
echo "Done";
$stmt->close();

answered Nov 21 '14 at 14:59

vaso123

12,347
4
34
64

The code you provided doesn't submit anything to the database but gives the error "Call to a member function close() on a non-object" – Smush Nov 24 '14 at 12:45
1

Do not iteratively instantiate prepared statements -- you only need one (which can and should be re-used). If you overwrite the bound variables, like in Alexanders answer, you won't need to make iterated calls of `bind_param()`. – mickmackusa Jan 04 '20 at 21:22

Insert multiple rows to database from HTML form using MySQLi

3 Answers3

Linked

Related