Loop through POST array for database insert

Question

I am trying to make a form that has several checkboxes input the value of each checked checkbox into a saeparate row on my mySQL database.

Here is my HTML:

<form action="testsent.php" method="post" novalidate>
  <div id="currentWork">
<label for="currentWork">Mark all that apply.</label><br>
  <input type="checkbox" name="chk1[]" value="Bernards Township">Bernards Library<br>
  <input type="checkbox" name="chk1[]" value="Boonton Holmes">Boonton Library<br>
  <input type="checkbox" name="chk1[]" value="Butler">Butler Library<br>
  <input type="checkbox" name="chk1[]" value="Chatham">Chathams Library<br>
  <input type="checkbox" name="chk1[]" value="Chester">Chester Library<br>
  <input type="checkbox" name="chk1[]" value="Denville">Denville Library<br>
  <input type="checkbox" name="chk1[]" value="Dover">Dover Library<br>
  <input type="checkbox" name="chk1[]" value="East Hanover">East Hanover Library<br>
</div>
<input type="Submit" value="Submit" name="Submit"/>
</div>
</form>

And here is my PHP:

if(!empty($_POST['chk1'])) {
foreach($_POST['chk1'] as $check) {
        //echo $check;
        $sql="INSERT INTO $usertable (library) VALUES ('.$check.')";
 }
}

if(mysqli_query($link, $sql)) 
    {
        echo 'Thanks for submitting';
    } 
else {
        echo 'ERROR: Could not execute $sql.' . mysqli_error($link);
    }

This is WORKING except it only inputs the final item checked. If Boonton is checked as well as Chester and Denville, it will only input 'Denville' into the form.

I thought having a foreach loop will iterate over the checkboxes, but I'm not sure where I'm going wrong. I'm not getting any error messages. I have clicked every single link that comes up when I search for this problem on stackoverflow. The problem is that there seems to be so many different ways to make this work and most of these questions are marked as having not enough information or being too generic.

If there's ANY extra info you need in order to help me resolve this, I'll be glad to provide it! I'm holding nothing back. Thanks!

Well for starters, you're not writing anything to your database. Secondly, you're dumping user-supplied data into an SQL query. Good way to get hacked! — miken32
– miken32, Commented Sep 12, 2018 at 20:17
@miken32 I must be writing something though, because when I submit the form, a value does appear in my database under the 'Library' column. — M. Rose
– M. Rose, Commented Sep 12, 2018 at 20:20
@ryantxr yes, I omitted that part, I can add it to the original post now. — M. Rose
– M. Rose, Commented Sep 12, 2018 at 20:21
You are writing to the database outside of the loop. Therefore, you are only inserting the last one. Move mysqli_query($link, $sql) inside the loop. — ryantxr
– ryantxr, Commented Sep 12, 2018 at 20:29

mickmackusa · Accepted Answer · 2020-01-06 21:26:24Z

2

You're writing to the database outside the loop. So the only value written is the last one that went through the loop. Move your insert into the loop.

if(!empty($_POST['chk1'])) {
    foreach($_POST['chk1'] as $check) {
        //echo $check;
        $sql="INSERT INTO $usertable (library) VALUES ('.$check.')";
        mysqli_query($link, $sql);
    }
}

What you need to be doing is using prepared statements to sanitize user data. This will also be much more efficient as the query is only sent to the database once:

if (!empty($_POST["chk1"])) {
    $stmt = $mysqli->prepare("INSERT INTO $usertable (library) VALUES ?");
    $stmt->bind_param("s", $check);
    foreach($_POST['chk1'] as $check) {
        $stmt->execute();
    }
    $stmt->close();
}

edited Jan 6, 2020 at 21:26

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Sep 12, 2018 at 20:31

miken32

42.5k16 gold badges127 silver badges177 bronze badges

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

7 Comments

M. Rose Over a year ago

This is exactly what I was looking for. Thanks for the info on sanitizing user data. The input is going in like '.Denville.' with the periods on either side.I tried the second code, the sanitized one, and I got this error: 'Call to a member function prepare() on null in /home/mainlib/public_html/subs/testsent.php on line 29'

miken32 Over a year ago

Well of course you’ll need to ensure $mysqli is a properly initialized mysqli connection.

M. Rose Over a year ago

Oh, I see, so in other words everything should match. Makes good sense! Thank you so much, I really appreciate it. I work for a small business with very few resources for training employees and I've offered to learn to collect and analyze data and this is a huge help to go alongside what I can pick up in books/online courses.

M. Rose Over a year ago

The issue seems to be with the line: '$stmt->bind_param("s", $check);' I have checked that the statement is initialized. Still an error. But the un-sanitized version works fine and nothing has changed, how can that be?

M. Rose Over a year ago

Sorry to comment again - can't edit my original. The only thing that differs from the 'working' code and the 'sanitized' code is that the Value is "?" which calls to the foreach loop right? Could this have something to do with the form submitting unchecked checkboxes as false? Even when I check every box, I get the same error.

|

Collectives™ on Stack Overflow

Loop through POST array for database insert

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related