Insert into DB from Multi-Select Listbox
Moderator: General Moderators
Insert into DB from Multi-Select Listbox
I'm having having a problem inserting multiple selections made from a multi-select listbox into a table in my database. One selection will insert itself, but not the secord, third, etc. I think I have to make the variable as an array and then loop through it, but I'm pretty dependant on the php script that Dreamweaver writes for me. I've simplified the page to try to work on the basics and have included most of the code here. For now I want to work with the code that's generated by Dreamweaver, so I really just want to re-write what I have just enough to allow for multiple selections to be inserted (into seperate rows in the table). Thanks in advance. It's the "category" that I want to allow multiple selections for. If someone could re-write the code or point me in the right direction, I'd appreciate it. 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>
<?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>
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
multiple-select boxes return by default arrays. The "switch" you have in your code only works for non-array variables - just add an case "Array" and use foreach.I think I have to make the variable as an array and then loop through it, but I'm pretty dependant on the php script that Dreamweaver writes for me.
Insert that into the switch-statement:
Code: Select all
<?php
case "array":
case "Array":
foreach($theValue as $key=>$value)
echo "<br>$key: $value";
break;
?>Can you post the function-call for "GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") "?
I put in both cases so that no matter whether the value for $theType is "Array" or "array", it would end up in the same case-statement.
Try this code
and do post the function-call if it doesn't ouput anything.
I put in both cases so that no matter whether the value for $theType is "Array" or "array", it would end up in the same case-statement.
Try this code
Code: Select all
<?php
case "array":
case "Array":
foreach($theValue as $key=>$value)
echo "<br><xmp>$key: $value</xmp>";
break;
?>I tried the modified code and still no luck. The first time I ran it the same record got entered twice and then every time after that only the last selection gets entered, the same as before. I haven't changed any of the other code, I just put that directlhy into the existing case statement.
Here's the function call you wanted to see, although in my original post I included the entire page so you can see it there too.
GetSQLValueString($HTTP_POST_VARS['category'], "int"));
Thanks for all your help....really appreciate it
Here's the function call you wanted to see, although in my original post I included the entire page so you can see it there too.
GetSQLValueString($HTTP_POST_VARS['category'], "int"));
Thanks for all your help....really appreciate it
Maybe this is nothing, but when I define a SELECT object on a form that is going to be accepting more than one option, I usually included a [] after the name. I always thought you had to do it like that otherwise it only returned one value to the script. For example, in the above case, it would look like this:
Code: Select all
<select name="category[]" size="6" multiple="multiple">The function-call type-casts the variable - in this case "int" for integer. Insert the "array"-code above and change
to
P.S.: Someone could have picked that up easier if you had used the php code-tags for your code... 
Code: Select all
<?php
GetSQLValueString($HTTP_POST_VARS['category'], "int"));
?>Code: Select all
<?php
GetSQLValueString($HTTP_POST_VARS['category'], "array"));
?>You need to add [] to the form-field name.
Use the code below. The code only caters for the form-field "category".
Also: the case "array"-statement will only output the array, you will need to alter the code according to your database.
P.S.: You should try and learn PHP - it's so powerful. The code Dreamweaver produces almost takes the joy out of it.
Use the code below. The code only caters for the form-field "category".
Also: the case "array"-statement will only output the array, you will need to alter the code according to your database.
Code: Select all
<?php
require_once('Connections/connJobFerret.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;
case "array":
case "Array":
foreach($theValue as $key=>$value)
echo "<br><xmp>$key: $value</xmp>";
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'], "array"));
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>
?>I'm going to have to sit down with php and learn it. I've been using Dreamweaver and it's been going fine, but now that I have to do other things, I seem to be lost. I used your code and got it to display the choices I made in the listbox, so now I have to learn how to insert the choices into the database instead of just displaying them. I have most of the syntax, but the problem is that I don't have enough knowledge to fully understand the generated code...maybe I have to take a step back to move forward. Thanks for the help.