Multiple Option HTML insert into MySQL

Mendez1979 · May 2, 2019, 11:24am

I’m trying to insert from a multiple option form into MySQL. I’ve tried numerous methods and have had issues such as blank entries being inserted for each specified column, to the last value selected from the form being inserted multiple times, but I never get the desired outcome. Any guidance on how to insert from this type of HTML form? I have no issues inserted from a normal form

                <form action ='insert.php' method='POST'>>
                <select name= 'choices[]' multiple ="multiple">>
                <option value ="Red" >Red</option>
                <option value ="Blue" >Blue</option>
                <option value ="Green" >Green</option>
               <input type="submit" name="send" value="submit" />
              </form>

Here’s one option I’ve tried for PHP

<?php

$servername ="localhost";
$username = "test";
$password = "test";
$dbname = "Test_DB";

$conn = new mysqli ($servername, $username, $password, $dbname);

 if($conn-> connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

{
if(isset($_POST['submit']))

    //check if any option is selected
    if(isset($_POST['choices']))
    
        //Retrieving each selected option
        foreach ($_POST['choices'] as $choice)

   $sql = "INSERT INTO Colours_test  (Choice 1, Choice 2, Choice 3, Choice 4) VALUES         ('$choice','$choice', '$choice','$choice')";

   if ($conn->query($sql) === TRUE)  {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>"  .  $conn->error;
}
}

$conn->close();

?>

chorn · May 2, 2019, 11:55am

Your HTML is invalid, always check this first to prevent browser issues:

http://validator.w3.org/

then check what data you actually get:

var_dump($_GET, $_POST);

also you should use prepared statements so random people don’t just delete your database:

https://www.php.net/manual/de/mysqli.quickstart.prepared-statements.php

your numbered columns seem like there’s a lack of qualified database normalization and you will get in trouble using the data in this form for any relations

also Choice 1 is no valid columns name and yoiu should enable displaying errors on your development machine

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

and at least your foreach loop is missing brackets to work properly for multiple inserts

https://www.php.net/manual/en/control-structures.foreach.php