MultiSelect SElection box and inserting MySQL query help

[WizyWyg]

Im in need of some assitance in order to implement a multiselect selection box and then inserting the choices as seperate records into a table in a MySql DB.

For the form, Im pulling information from two tables, then inserting it into a third as seperate records, based on what is chosen in the multiselect box.

From table 1 , Im pulling the last record inserted.
From table 2 , Im pulling in the memberid and membername (multiselect box)

For table 3, should insert whatever is selected in the multiselect box as seperate records from table 1.

Ie, if last record was #45
Multiselect box chosen were id's 3,4,6,9 (shown name of member, but only their id will be inserted into the table)
Table 3 would result in :
45 | 3
45 | 4
45 | 6
45 | 9


My multiselect box code looks like this:

<select size=10 multiple name=&quot;who&quot;>
<?php
	
  $q=&quot;select member_id, membername from members
  order by member_id asc&quot;;
  $r=mysql_query($q) or die(mysql_error().$q);
   while($row=mysql_fetch_array($r)) {
    echo &quot;<option value=\&quot;&quot;.$row[&quot;member_id&quot;].&quot;\&quot;>&quot;.$row[&quot;membername&quot;].&quot;</option>&quot;;
  }
  ?>
</select>

How can I construct the MySQL insert query so that it loops through the chosen selections and inserts them until all are inserted?

 

[DRJ478]

You need to name the select element as an array:

<input .... name=&quot;who[]&quot;>

When you POST your form there will be an array $_POST[who] ($HTTP_POST_VARS[who] before PHP 4.1.2).

I suggest:

if (is_array($_POST[who])){ # make sure something was selected
   # loop through all the selections
   foreach ($_POST as $key=>$value){
      # compose SQL here
      $SQL = "INSERT INTO .......
      # execute the query
      ...
   }
}

 

[DRJ478]

Of course:

if (is_array($_POST[who])){ # make sure something was selected
   # loop through all the selections
   foreach ($_POST[who] as $key=>$value){
      # compose SQL here
      $SQL = "INSERT INTO .......
      # execute the query
      ...
   }
}

 

[WizyWyg]

Let me add to this, noticing that my question was somehow truncated during the post, so here is a more clearer explanation:

Im in need of some assitance in order to implement a multiselect selection box and then inserting the choices as seperate records into a table in a MySql DB.

For the form, Im pulling information from two tables, then inserting it into a third as seperate records, based on what is chosen in the multiselect box.

From table 1 , Im pulling the last record inserted.
From table 2 , Im pulling in the memberid and membername (multiselect box)

For table 3, should insert whatever is selected in the multiselect box as seperate records from table 1.

Ie, if last record was #45
Multiselect box chosen were id's 3,4,6,9 (shown name of member, but only their id will be inserted into the table)
Table 3 would result in :
45 | 3
45 | 4
45 | 6
45 | 9

Im currently modifying a code that only inserts 1 record at a time below is the code:

//===============================
// Record Form Queries
//-------------------------------
function participant_action($sAction)
{
  global $db;
  global $tpl;
  global $sForm;
  global $sparticipantErr;
  $bExecSQL = true;
  $sActionFileName = "";
  $sParams = "?";
  $sWhere = "";
  $bErr = false;
  $pPKid = "";
  $fldeventid = "";
  $fldwho = "";
// Event begin
  $sActionFileName = "admin_event.php";
  $sParams .= "eventid=" . urlencode(get_param("Trn_eventid"));

// CANCEL action
  if($sAction == "cancel")
  {
 header("Location: " . $sActionFileName . $sParams);
    exit;
  }

// Build WHERE statement
  if($sAction == "update" || $sAction == "delete") 
  {
    $pPKid = get_param("PK_id");
    if( !strlen($pPKid)) return;
    $sWhere = "id=" . tosql($pPKid, "Number");
  }


// Load all form fields into variables
  $fldeventid = get_param("eventid");
  $fldwho = get_param("who");

// Create SQL statement
  switch(strtolower($sAction)) 
  {
    case "insert":
        $sSQL = "insert into participants (" . 
          "eventid," . 
          "who)" . 
          " values (" . 
          tosql($fldeventid, "Number") . "," . 
          tosql($fldwho, "Number") . 
          ")";
    break;
    case "update":
        $sSQL = "update participants set " .
          "eventid=" . tosql($fldeventid, "Number") .
          ",who=" . tosql($fldwho, "Number");
        $sSQL .= " where " . $sWhere;
    break;
    case "delete":
        $sSQL = "delete from participants where " . $sWhere;
    break;
  }
// Execute SQL statement
  if(strlen($sparticipantErr)) return;
  if($bExecSQL)
  $db->query($sSQL);
  header("Location: " . $sActionFileName . $sParams);
  exit;
}

The selection form name is "who" and that's the only thing that can be drawn from it.

I've tried chaning the case "insert" to create a loop (both a if and a while) but im coming up with empty inserts or just one record being inserted.

Any ideas on how to approach this?