PHP multiple select form to insert multiple rows in MySQL

Question

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>

Can you add the code of your form, especially the multi-select input? — thaJeztah
– thaJeztah, Commented Mar 19, 2013 at 20:29
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
– Benjamin Gruenbaum, Commented Mar 19, 2013 at 20:29
@BenjaminGruenbaum thanks for the info, I will check it out and see about stopping using it, appreciate the links. — Rocco The Taco
– Rocco The Taco, Commented Mar 19, 2013 at 20:59

michi · Accepted Answer · 2013-03-19 21:07:54Z

5

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_*

edited Mar 19, 2013 at 21:07

answered Mar 19, 2013 at 20:56

michi

6,6255 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

thaJeztah Over a year ago

However, you'll also have to update the insert query, because the current query will only insert a single row

Rocco The Taco Over a year ago

@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?

michi Over a year ago

@RoccoTheTaco: what about $_SESSION['nameofvariable']??

Rocco The Taco Over a year ago

OH MY, duh! I feel like a dummy LOL...perfect, worked great. Thanks so much!

Collectives™ on Stack Overflow

PHP multiple select form to insert multiple rows in MySQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related