views:

2607

answers:

2

Hi, 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";
?>
+2  A: 

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());
}
Jeremy Smyth
Thanks! I've done what you've said to do and now the info is being passed into the database. It's inserting both options selected, but into the same row, separated by a coma. I would ideally like each option to go in it's own row. Where do I need to insert the code to iterate over the array? Thanks for your help!
At the moment you're using $_POST["strategylist[]"] in your insert, but that's an array. I've edited the post to show how to do it.
Jeremy Smyth
+2  A: 

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...
    }
    
Matt Bridges
A good idea is also to check and escape whatever you're inserting into your database, even if it's supposed source is your own output.
nikc
Thanks! I've done what you've said to do and now the info is being passed into the database. It's inserting both options selected, but into the same row, separated by a coma. I would ideally like each option to go in it's own row. Where do I need to insert the code to iterate over the array? Thanks for your help!