CODEWITHSUNDEEP
phpmysqlselect
user96828
user96828

Reputation: 513

sending multiple records to MySQL from multiple select box

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";
?>

Upvotes: 2

Views: 13095

Answers (3)

ahmed
ahmed

Reputation: 1

// first you need to define your output as one variable if you don't like the loop 
   if($_POST){
   $sum = implode(", ", $_POST[select2]);
   echo $sum.".";
}
// the variable sum is the one you are seeking for you can insert it to the database
// if you want to enter every peiece of the array in a new field you should use
// different select names

Upvotes: 0

Jeremy Smyth
Jeremy Smyth

Reputation: 23503

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());
}

Upvotes: 3

Matt Bridges
Matt Bridges

Reputation: 49415

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...
}

Upvotes: 2

Related Questions