Insert into DB from Multi-Select Listbox

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Insert into DB from Multi-Select Listbox

Post by klycette »

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>
User avatar
Kriek
Forum Contributor
Posts: 238
Joined: Wed May 29, 2002 3:46 am
Location: Florida
Contact:

Post by Kriek »

In any case I recommend altering:

$HTTP_POST_VARS to $_POST

$HTTP_SERVER_VARS to $_SERVER
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

And editing your post to add [syntax=php][/syntax] tags around the code.

Mac
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

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.
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.
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Post by klycette »

Thanks for your input. Can you give me an idea of what the case "array" and foreach statement would look like?
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Insert that into the switch-statement:

Code: Select all

<?php
 case "array":
 case "Array":
         foreach($theValue as $key=>$value)
             echo "<br>$key: $value";
         break; 
?>
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Post by klycette »

I tried putting that script into what I have, but still no luck. Do I need both array cases? Do I need to change anything else in the script?
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

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

Code: Select all

<?php
case "array":
case "Array":
         foreach($theValue as $key=>$value)
             echo "<br><xmp>$key: $value</xmp>";
         break;
?>
and do post the function-call if it doesn't ouput anything.
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Post by klycette »

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
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post by SBukoski »

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">
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Post by klycette »

I thought about that too, but it doesn't seem to change anything. I know the answer is simple, I just can't figure out what I have to do.
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

The function-call type-casts the variable - in this case "int" for integer. Insert the "array"-code above and change

Code: Select all

<?php
GetSQLValueString($HTTP_POST_VARS['category'], "int")); 
?>
to

Code: Select all

<?php
GetSQLValueString($HTTP_POST_VARS['category'], "array")); 
?>
P.S.: Someone could have picked that up easier if you had used the php code-tags for your code... ;)
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Post by klycette »

Sorry about the tags...didn't realize they made such a difference. I tried altering the script as you suggested, but now I'm getting a warning:

Invalid argument supplied for foreach() on line 26, which is the foreach that you had written. Does it matter what the array consists of?
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

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.

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>
?>
P.S.: You should try and learn PHP - it's so powerful. The code Dreamweaver produces almost takes the joy out of it.
klycette
Forum Newbie
Posts: 9
Joined: Sat May 31, 2003 9:25 pm

Post by klycette »

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.
Post Reply