I have a dynamic drop down box value working a treat and have incorporated it into a form. In iy you choose a categiry from the first drop down and this then produces ther relating sub categories in the second drop down where multiple choices can be made. I am having trouble though having the category and sub categories insert into my database table along with the remaining values of the form.
here is my form
<?php
if($session->logged_in){
echo '<h1>Add Service Provider Profile<br/>';
echo "<h1>Logged In</h1>";
echo "Welcome <b>$session->username</b>, you are logged in. <br><br>"
."[<a href=\"userinfo.php?user=$session->username\">My Account</a>] "
."[<a href=\"useredit.php\">Edit Account</a>] ";
echo "[<a href=\"process.php\">Logout</a>]";
echo '<strong>';
?>
<input type="hidden" name="username" value="<?=$_SESSION['username']?>" />
<meta name="GENERATOR" content="Arachnophilia 4.0">
<meta name="FORMATTER" content="Arachnophilia 4.0">
<SCRIPT language=JavaScript>
function reload(form)
{
var val=form.category.options[form.category.options.selectedIndex].value;
self.location='dd_test.php?category=' + val ;
}
</script>
<?php
@$cat=$_GET['category']; // Use this line or below line if register_global is off
//@$cat=$HTTP_GET_VARS['category']; // Use this line or above line if register_global is off
/// Getting the data from Mysql table for first list box//////
$quer2=mysql_query("SELECT DISTINCT category,cat_id FROM category order by category");
///////////// End of query for first list box////////////
/////// for second drop down list we will check if category is selected else we will display all the subcategory/////
if(isset($cat) and strlen($cat) > 0){
$quer=mysql_query("SELECT DISTINCT subcategory FROM subcategory where cat_id=$cat order by subcategory");
}else{$quer=mysql_query("SELECT DISTINCT subcategory FROM subcategory order by subcategory"); }
////////// end of query for second subcategory drop down list box ///////////////////////////
echo "<form method=post name=f1 action='dd_test_handler.php'>";
/// Add your form processing page address to action in above line. Example action=dd-check.php////
////////// Starting of first drop downlist /////////
echo 'Select ONE category';
echo '<br><br>';
echo "<select name='category' onchange=\"reload(this.form)\"><option value=''>Select one</option>";
while($noticia2 = mysql_fetch_array($quer2)) {
if($noticia2['cat_id']==@$category){echo "<option selected value='$noticia2[cat_id]'>$noticia2[category]</option>"."<BR>";}
else{echo "<option value='$noticia2[cat_id]'>$noticia2[category]</option>";}
}
echo "</select>";
////////////////// This will end the first drop down list ///////////
////////// Starting of second drop downlist /////////
echo 'Select one or more specialisms';
echo '<br><br>';
echo "
<select name=\"subcat[]\" size=\"5\" multiple=\"multiple\">
<option value=\"\"> Select one or more subcategories </option>";
while($noticia = mysql_fetch_array($quer)) {
echo "<option value='$noticia[subcategory]'>$noticia[subcategory]</option>";
}
echo "</select>";
////////////////// This will end the second drop down list ///////////
//// Add your other form fields as needed here/////
?>
<table width="700" border="0">
<tr>
<td>Location</td>
<td><label>
<select name="location[]" size="3" multiple="multiple">
<?php
$result = mysql_query('SELECT * FROM location') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo '<option value="' . $row['location_id'] . '">' . $row['location'] . '</option>';
}
?>
</select>
</label></td>
</tr>
<tr>
<td>Industries</td>
<td><select name="industry[]" size="3" multiple="multiple">
<?php
$result = mysql_query('SELECT * FROM industry') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo '<option value="' . $row['industry_id'] . '">' . $row['industry'] . '</option>';
}
?>
</select></td>
</tr>
<tr>
<td>Rate</td>
<td><?
$result = mysql_query('SELECT * FROM rate') or exit(mysql_error());
echo '<select name="rate">';
while ($row = mysql_fetch_assoc($result))
{
echo '<option value="' . $row['rate_id'] . '">' . $row['rate'] . '</option>';
}
echo '</select>';
?></td>
</tr>
<tr>
<td>Will work onsite </td>
<td><select name="onsite" id="onsite">
<?php
$values = array('Yes', 'No', 'Not selected');
foreach($values as $val)
{
printf("<option%s>$val</option>\n", ($val == $row['onsite']) ? " selected" : "");
}
?>
</select></td>
</tr>
<tr>
<td>Contract</td>
<td><select name="permanent" id="permanent">
<?php
$values = array('Any', 'Permanent', 'Temporary','Project based','Negotiable');
foreach($values as $val)
{
printf("<option%s>$val</option>\n", ($val == $row['permanent']) ? " selected" : "");
}
?>
</select></td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td><span style="font-weight: bold">Qualifications</span></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td><?
$result = mysql_query('SELECT * FROM qualification') or exit(mysql_error());
echo '<select name="qual1">';
while ($row = mysql_fetch_assoc($result))
{
echo '<option value="' . $row['qual_id'] . '">' . $row['qualification'] . '</option>';
}
echo '</select>';
?></td>
///////////////////////////////////etc
here is my formhandler
error_reporting(E_ALL);
ini_set('display_errors', '1');
include("include/session.php");
require_once('common_functions.php');
$_SESSION['form1_data']= $_POST;
$username = $_SESSION['username'];
$sql = NULL;
include("connect.php");
// build main query from form
foreach ($_SESSION['form1_data'] as $col =>$val ){
if($col <> 'submit'){
if ($col=='subcat'){
//ignore and process later below for seperate table insert
}
elseif ($col=='location'){
//ignore and process later below for seperate table insert
}
elseif ($col=='industry'){
//ignore and process later below for seperate table insert
}
else {
$sql .=", $col = '$val' ";
}
}
}
mysql_query($sql, $connection) or die(mysql_error());
//get last insert id
$sql ="select LAST_INSERT_ID() as id from sp_profile";
$result = mysql_query($sql, $connection) or die(mysql_error());
$row=mysql_fetch_array($result);
$id = $row['id'];
//update subcategory table
if($_SESSION['form1_data']['subcat']){
foreach($_SESSION['form1_data']['subcat'] as $fvalue){
$sql ="INSERT INTO sp_subcategory SET spsubcategory_id = NULL , spprofile_id = '$id', subcategory_id = '$fvalue' ";
mysql_query($sql, $connection) or die(mysql_error());
}
}
// update location table
if($_SESSION['form1_data']['location']){
foreach($_SESSION['form1_data']['location'] as $fvalue){
$sql ="INSERT INTO sp_location SET splocation_id = NULL , spprofile_id = '$id', location_id = '$fvalue' ";
mysql_query($sql, $connection) or die(mysql_error());
}
}
// update industry table
if($_SESSION['form1_data']['industry']){
foreach($_SESSION['form1_data']['industry'] as $fvalue){
$sql ="INSERT INTO sp_industry SET spindustry_id = NULL , spprofile_id = '$id', industry_id = '$fvalue' ";
mysql_query($sql, $connection) or die(mysql_error());
}
}
?>
the feedback i have is:
Array ( [username] => testing123 [url] => /icommission/dd_test.php [form1_data] => [userid] => 72eac5b7c68fdf9f6cf6c618b71ffb8f ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' category = '' , rate = '17' , onsite = 'Yes' , permanent = 'Any' , qual1 = '' ,' at line 1
the data is not being inserted into the tables. As you can see I have a table sp_profile and some tables that store the choices of sub categories, locations and industries. Any help is greatly appreciated