sending multiple records to MySQL from multiple select box

Question

I'm trying to insert multiple rows into a MySQL table depending on the number of options selected from a multiple select box. currently it is inserting one row (regardless of how many options are selected) but the 'strategyname' column is empty each time.

Any ideas on how to insert multiple rows and why the values of the options aren't being sent to the table?

Here is the form:

<form method="POST" action="update4.php">

<input type="hidden" name="id" value="1">

<p class="subheadsmall">Strategies</p>

<p class="sidebargrey">

<?php

            $result = mysql_query("SELECT strategyname FROM sslink WHERE study_id = '{$_GET['id']}'");
                if (!$result) {
                    die("Database query failed: " . mysql_error());
                }

while($row = mysql_fetch_array($result)) {
    $strategyname = $row['strategyname'];


    echo $strategyname.'<br />';
}

?>
        <p class="subheadsmall">Add a strategy... (hold down command key to select more than one)</p>

<select name="strategylist" multiple="multiple">
     <?php

            $result = mysql_query("SELECT * FROM strategies");
                if (!$result) {
                    die("Database query failed: " . mysql_error());
                }

while($row = mysql_fetch_array($result)) {
    $strategylist = $row['name'];
    $strategyname = htmlspecialchars($row['name']);
    echo '<option value="' . $strategylist . '" >' . $strategyname . '</option>' . '\n';
}

?>
</select>
    </p>



<input type="submit" class="box" id="editbutton" value="Update Article">

</form>

And this is what sends it to the database:

<?php
$id=$_POST['id'];
$test=$_POST['strategylist'];
$db="database";

$link = mysql_connect("localhost", "root", "root");
//$link = mysql_connect("localhost",$_POST['username'],$_POST['password']);
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Select Error: ".mysql_error());
//for($i=0;$i<sizeof($_POST["test"]);$i++)
//{
//$sql = "insert into tbl_name values ($_POST["test"][$i])"; }
//sql = "INSERT INTO table_name VALUES ('" . join(",",$_POST["test"]) . "')";
$result=mysql_query("INSERT INTO sslink (study_id, strategyname) VALUES ('$id','" . join(",",$_POST["strategylist"]) . "')")or die("Insert Error: ".mysql_error());
mysql_close($link);
print "Record added\n";
?>

Solution 1

Couple of points:

• your select needs to be named strategylist[] in order to tell PHP that it will contain an array rather than a single value
• Your insert code then needs to iterate over that array, creating a new insert for each element it contains, unless (as it seems) you want all those options to be concatenated into a single row's field.

At the moment, your form only returns a single option (from PHP's perspective), so it's only going to insert a single row.

To iterate over the array, use something like this:

foreach($_POST["strategylist[]"] as $s) {
    # do the insert here, but use $s instead of $_POST["strategylist[]"]
    $result=mysql_query("INSERT INTO sslink (study_id, strategyname) " .
       "VALUES ('$id','" . join(",",$s) . "')")
        or die("Insert Error: ".mysql_error());
}

Solution 2

Two things:

1. If you view the source of page with the multiple select in it, can you see the <option value="something"> lines there? Are the values empty? It seems strange to me that at the top of your file you are using $row['strategyname'] and later you are using $row['name']. I suspect this may be the cause of the empty StrategyName column.

2. To handle multiple selections, you should specify the select tag as

   <select name="strategylist[]" multiple="multiple">
   

   The extra [] tells PHP to form an array with all of the selections in it. You can then loop over the array like:

   $strategylist = $_POST['strategylist'];
   for ($i = 0; $i < count($strategylist); $i++) {
       $strategyname = $strategylist[$i];
       // Insert a record...
   }