Foreach loop ONLY adds last value in array to table

Question

Forgive me for I am relatively new to PHP and MYSQL and I believe this maybe be an easy question to answer(or maybe not, i'm not sure). Here is my HTML form of checkboxes

<form method="post" action="process.php">
<input type="checkbox" name="athlete[]" value="1">athlete 1
<br>
<input type="checkbox" name="athlete[]" value="2">athlete 2
<br>
<input type="checkbox" name="athlete[]" value="3">athlete 3
<br><br>
<input type="submit" value="Submit">
</form>

Fairly simple. Then I have this PHP in process.php:

$checkboxes = isset($_POST['athlete']) ? $_POST['athlete'] : array();
foreach($checkboxes as $value) {
    $sql = "INSERT INTO draftPick(user_id, athlete_id)VALUES('77', '$value' )";
}

if(mysqli_query($conn,$sql)) {

    echo 'Data added sucessfully';
}
else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);

I am attempting to make a foreach loop with my checkboxes and then insert each checkbox value into a new row in my MYSQL table. However when I run this PHP, it only inserts the last checked checkbox value into the MYSQLtable and does not insert all. How can I loop it so it will insert all checked values into my table. Thank you for the help!

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Feb 13 '20 at 01:56
Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era and should not be used in new code. — tadman, Feb 13 '20 at 01:56

score 2 · Answer 1 · answered Feb 13 '20 at 01:38

You can also take this opportunity to correct the SQL injection vulnerability.

$stmt = $conn->prepare('INSERT INTO draftPick (user_id, athlete_id) VALUES ('77', ? )');
$stmt->bind_param('i', $value);
foreach($checkboxes as $value) {
    $stmt->execute();
}

Cloud Soh Jun Fu · Accepted Answer · 2020-02-14T01:39:08.200

What you did is just replacing the string $sql, but your execution of actually inserting into database is written after loop, that's why it's only inserting the last checkbox.

Try this:

$checkboxes = isset($_POST['athlete']) ? $_POST['athlete'] : array();
foreach($checkboxes as $value) {
    $sql = "INSERT INTO draftPick(user_id, athlete_id)VALUES('77', '$value' )";

    if(mysqli_query($conn,$sql)) {
        echo 'Data added sucessfully';
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

}

mysqli_close($conn);

Also, please note that the way you are inserting(looping) is very slow because it's executing quite a number of queries. You should use batch insert instead.

Warning: As mentioned by Darman in the comment

You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough!

I know it's the prepared statements are good to know for the question. But the question itself didn't mention anything about security, I'm answering his question and my answer solves his question. Just saying to those who downvoted — Cloud Soh Jun Fu, Feb 14 '20 at 01:30
Not using prepared statements is a bigger problem. When answering you need to fix all the problems or your answer will be downvoted for containing wrong information. — Dharman, Feb 14 '20 at 01:32
You should also show how to enable MySQLi exceptions. In summary, your answer is not a full answer! — Dharman, Feb 14 '20 at 01:34

score -1 · Answer 3 · answered Feb 13 '20 at 01:27

when you're setting :

 $sql = "INSERT INTO draftPick(user_id, athlete_id)VALUES('77', '$value' )";

it is only a string bieng set to $sql varaible where

mysqli_query($conn,$sql)

is which executing the SQL query to insert the data into the table so move

foreach($checkboxes as $value) {
  $sql = "INSERT INTO draftPick(user_id, athlete_id)VALUES('77', '$value' )";
  mysqli_query($conn,$sql);
}

Foreach loop ONLY adds last value in array to table

3 Answers3