Page 1 of 1
Insert into DB from Multi-Select Listbox
Posted: Sat May 31, 2003 9:25 pm
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>
Posted: Sun Jun 01, 2003 9:02 am
by Kriek
In any case I recommend altering:
$HTTP_POST_VARS to $_POST
$HTTP_SERVER_VARS to $_SERVER
Posted: Sun Jun 01, 2003 11:05 am
by twigletmac
And editing your post to add [syntax=php][/syntax] tags around the code.
Mac
Posted: Sun Jun 01, 2003 11:13 am
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.
Posted: Sun Jun 01, 2003 12:29 pm
by klycette
Thanks for your input. Can you give me an idea of what the case "array" and foreach statement would look like?
Posted: Sun Jun 01, 2003 2:34 pm
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;
?>
Posted: Sun Jun 01, 2003 4:04 pm
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?
Posted: Sun Jun 01, 2003 6:05 pm
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.
Posted: Sun Jun 01, 2003 8:10 pm
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
Posted: Sun Jun 01, 2003 8:59 pm
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">
Posted: Sun Jun 01, 2003 9:57 pm
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.
Posted: Mon Jun 02, 2003 4:26 am
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...

Posted: Mon Jun 02, 2003 9:27 am
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?
Posted: Mon Jun 02, 2003 10:00 am
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.
Posted: Mon Jun 02, 2003 10:30 am
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.