Insert multiple rows to database from HTML form containing repeated fields 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

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();
}

Alexander Haas · Accepted Answer · 2014-11-21 15:02:36Z

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, 2014 at 15:02

Alexander Haas

2883 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Over a year ago

bind_param() doesn't need to be iterated -- move that line above the for() line.

mickmackusa · Accepted Answer · 2020-01-06 21:19:50Z

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 6, 2020 at 21:19

answered Jan 4, 2020 at 21:31

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Comments

vaso123 · Accepted Answer · 2014-11-21 14:59:13Z

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, 2014 at 14:59

vaso123

12.4k4 gold badges37 silver badges68 bronze badges

2 Comments

Smush Over a year ago

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"

mickmackusa Over a year ago

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().

Collectives™ on Stack Overflow

Insert multiple rows to database from HTML form containing repeated fields using MySQLi

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related