View Full Version : Select input from database not inserting right

07-24-2012, 10:59 PM
Hi, I have two selects pulling from a mysql database with php, one select is pulling from cat database table, the other from subcat database table. This is what the code looks like so far:

$db = new mysqli('localhost','root','*****','****');//set your database handler
$query = "SELECT id,cat FROM cat";
$result = $db->query($query);

while($row = $result->fetch_assoc()){
$categories[] = array("id" => $row['id'], "val" => $row['cat']);

$query = "SELECT id, catnumber, subcat FROM subcat";
$result = $db->query($query);

while($row = $result->fetch_assoc()){
$subcats[$row['catnumber']][] = array("id" => $row['id'], "val" => $row['subcat']);

$jsonCats = json_encode($categories);
$jsonSubCats = json_encode($subcats);


<script type='text/javascript'>
echo "var categories = $jsonCats; \n";
echo "var subcats = $jsonSubCats; \n";
function loadCategories(){
var select = document.getElementById("categoriesSelect");
select.onchange = updateSubCats;
for(var i = 0; i < categories.length; i++){
select.options[i] = new Option(categories[i].val,categories[i].id);
function updateSubCats(){
var catSelect = this;
var catid = this.value;
var subcatSelect = document.getElementById("subcatsSelect");
subcatSelect.options.length = 0; //delete all options if any present
for(var i = 0; i < subcats[catid].length; i++){
subcatSelect.options[i] = new Option(subcats[catid][i].val,subcats[catid][i].id);

and then later in the body part of my html

<select id='categoriesSelect' name="cselect1">
<option value='0' selected="selected">Category</option>
<select id='subcatsSelect' name="sselect1">

and then to insert this I use ( just the cselect1 and sselect1 ones are for this)

$sql = "INSERT INTO `company` ( `name` , `phone` , `cat1` , `cat2` , `cat3` , `zipcode` , `city` , `address` , `address2` , `website` , `product1` , `product2` , `product3` , `product4` , `product5` , `product6` , `product7` , `subcat1` , `subcat2` , `subcat3` ) VALUES( '{$_POST['name']}' , '{$_POST['phone']}' , '{$_POST['cselect1']}' , '{$_POST['cselect2']}' , '{$_POST['cselect3']}' , '{$_POST['zipcode']}' , '{$_POST['city']}' , '{$_POST['address']}' , '{$_POST['address2']}' , '$biz_url' , '{$_POST['product1']}' , '{$_POST['product2']}' , '{$_POST['product3']}' , '{$_POST['product4']}' , '{$_POST['product5']}' , '{$_POST['product6']}' , '{$_POST['product7']}' , '{$_POST['sselect1']}' , '{$_POST['sselect2']}' , '{$_POST['sselect3']}' ) ";
mysql_query($sql) or die(mysql_error());

This makes whatever the user clicks on in the cat select ( the first select) make show the corresponding subcategories in the second select. Unfortunately, when it inserts it puts numbers (which I don't know why, could someone explain why?) How can I get it to insert the name of category and the name of subcategory? The row cat and the subcat contain the names for each. Also, how could I get the default option to be something with no value like "Please insert a Category" for the first select? Thanks for all help... If this is confusing (it is a little bit even to me and I wrote it :) ) then just reply and I'll try and help.

Also, I'm not sure if I put this thread in the correct place...I'm a little new to CodingForums. THanks!

07-24-2012, 11:35 PM
Your select options have integer values, so of course it'll insert integers. Change the values to be what you want to insert.

Also, you'll want to consider reorganizing your database. Its messy, and what happens the day you want to add another category or product? You'll have to add a new column, write whole new sets of code, etc. You should have products and categories as separate tables, referencing this main table.