up vote 0 down vote favorite

I have the following code used to insert a record using PHP into a MySQL database. The form element is a multiple select that works fine when only one option is selected. When I choose more than 1 option, only the last option is inserted. How do I have the form create a new row for each option selected in the multiple select?

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "InsertForm")) {
  $insertSQL = sprintf("INSERT INTO emplikons (EmpNo, IconId) VALUES (%s, %s)",
                       GetSQLValueString($_POST['insertRecordID'], "text"),
                       GetSQLValueString($_POST['icons'], "int"));

  mysql_select_db($database_techsterus, $techsterus);
  $Result1 = mysql_query($insertSQL, $techsterus) or die(mysql_error());

This is the code of the form element. It uses a recordset to dynamically pull values from another table:

<select name="icons" size="10" multiple="multiple">
            <?php
do {  
?>
            <option value="<?php echo $row_icons['id']?>"><?php echo $row_icons['name']?></option>
            <?php
} while ($row_icons = mysql_fetch_assoc($icons));
  $rows = mysql_num_rows($icons);
  if($rows > 0) {
      mysql_data_seek($icons, 0);
      $row_icons = mysql_fetch_assoc($icons);
  }
?>
          </select>
share
    
Can you add the code of your form, especially the multi-select input? – thaJeztah Mar 19 '13 at 20:29
2  
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. – Benjamin Gruenbaum Mar 19 '13 at 20:29
    
@thaJeztah , question is updated now. thanks. – Rocco The Taco Mar 19 '13 at 20:45
1  
@BenjaminGruenbaum thanks for the info, I will check it out and see about stopping using it, appreciate the links. – Rocco The Taco Mar 19 '13 at 20:59
up vote 3 down vote accepted

add [] to the name of the select, to get an array of all selected values.

<select name="icons[]" size="10" multiple="multiple">

Then, read from $_POST / $_GET... (assuming form method="post")

$sql = '';
foreach ($_POST['icons'] as $icon) {

    // no idea where to get EmpNo from, let's assume it is in $empno.
    $sql .= "('$empno','$icon'),";

}

$sql = "INSERT INTO tablename (EmpNo, IconId) VALUES " . trim($sql,',');

// Now, please use mysqli_* or PDO instead of mysql_*
share
    
However, you'll also have to update the insert query, because the current query will only insert a single row – thaJeztah Mar 19 '13 at 20:59
    
@michi EmpNo is a session variable but I get a 500 server error when I insert that it like this: $row_WADAsess['EmpNo'] Is it because of the 'EmpNo'? How do I escape that? – Rocco The Taco Mar 19 '13 at 21:28
1  
@RoccoTheTaco: what about $_SESSION['nameofvariable']?? – michi Mar 19 '13 at 21:51
    
OH MY, duh! I feel like a dummy LOL...perfect, worked great. Thanks so much! – Rocco The Taco Mar 19 '13 at 22:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.