Inserting Multiple Selections from a Listbox

[klycette]

I'm trying to insert multiple selections from a listbox, but I can't figure out what the best approach is. Right now, I've managed to insert of of the choices, but I don't know how to insert the others. I'm pretty sure I'll have to change the variable to an array and someone loop through it, but I need some help. If someone could lend a hand or direct me to a tutorial, that would be great. Thanks.

[kevinsequeira]

have the listbox like this

<SELECT NAME="choices[]" MULTIPLE="MULTIPLE">
<OPTION VALUE="1">1</OPTION>
<OPTION VALUE="2">2</OPTION>
.
.
.
</SELECT>

on the page where you submit to access the listbox like this
<?php
for ($i = 0; $i < sizeof($choices); $i++) {
echo $choices[$i];
}

// or
print_r $choices;
?>

reg
kevin

[klycette]

I'll have to try that code for accessing the selections, but what should I do about inserting the selections into the table in the first place?

[kevinsequeira]

well it depends on how your database / table is designed. do you want all the values selected to go into 1 column or into seperate rows ?

reg
kevin

[klycette]

The information that people enter will be going into different categories. Most of the information they enter will go into one table, but another table will hold two pieces of information; the first is the unique id just created from the first table (via LAST_INSERT_ID()) and the second is the category they've selected. I'm allowing them to enter several category selections, so each category will be stored in a seperate row.

I'm trying to find out how to first get these mulitiple selections into the database and then figuring out how to bring it from the database and displaying the selections back on the listbox on another page.

I hope this was clear enough. Thanks.

[kevinsequeira]

well its pretty easy to do, could you post the code u have till now.

reg
kevin

[klycette]

Here's what I have so far...most of it was auto-generated by Dreamweaver.

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "job_posting")) {
$insertSQL = sprintf("INSERT INTO job_categories (category_id, job_id) VALUES (%s, LAST_INSERT_ID())",
GetSQLValueString($HTTP_POST_VARS['lstCareers'], "int"));

This portion takes the id that was created from the first insert and puts it into another table, along with the category id. The thing is, I want people to be able to choose multiple categories and have them go into different rows in the second table.

I haven't yet built anything to bring back the categories and put them into the listbox (so people can edit them at a later time), but I'd appreciate input on that as well. Thanks

[klycette]

Sorry about the formatting by the way, I hope you can read it. Thanks in advance.

[klycette]

I'm posting this again, because I still haven'e been able to modify my existing code to allow for multiple inserts. I've simplified the page down to just one listbox and have included most of the code here. I know most of you don't like generated code, but I have to work with it for now due to time contrainsts. If someone could re-write what I have here just enough to allow multiple selections to be inserted into seperate rows, I'd really appreciate it. I think the listbox has to be an array that will have to be looped through for the insert, but I don't know how to do it. Thanks in advance. It's the "category" that I want to allow multiple selections for. Here's the code:

<?php require_once('Connections/connJobFerret.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}

$editFormAction = $HTTP_SERVER_VARS['PHP_SELF'];
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
$editFormAction .= "?" . $HTTP_SERVER_VARS['QUERY_STRING'];
}

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "insert")) {
$insertSQL = sprintf("INSERT INTO job_categories (job_id, category_id) VALUES (1, %s)",
GetSQLValueString($HTTP_POST_VARS['category'], "int"));

mysql_select_db($database_connJobFerret, $connJobFerret);
$Result1 = mysql_query($insertSQL, $connJobFerret) or die(mysql_error());
}
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<form action="<?php echo $editFormAction; ?>" method="POST" name="insert" id="insert">

<select name="category" size="6" multiple="multiple">
<option value="1">Accounting/Auditing</option>
....(etc)
<option value="81">Waste Management</option>
</select>
<input type="submit" name="Submit" value="Submit">
<input type="hidden" name="MM_insert" value="insert">
</form></td>
</tr>
</table>
</body>
</html>[/code]