help auto populating multiple drop downs.

[jbrill]

hey guys,

 

im trying to create two dropdowns, i need the first one to be the category and the second one to be the subcategory.  The category drop down autopopulates with the correct info from the database. and uses the table "category", the value of each drop down is represented by the "cat" field in the table (cat is basically and integer id number) and "Category" is used as what the user actually sees in the drop down (category is the actual word of the category).

 

Once the category is selected i would like to have the sub category auto populate with everything that has the same values as the selected category (cat)

 

Here is a break down of how the tables work.

 

Table 1 Name: "category"

Fields for Table 1:  "cat" (the id number), "category" ( the actual name of the category)

 

Table 2 Name: "subcategory"

Fields for Table 2: "cat" (corresponds with the cat id from table 1 to pull the correct data), "subc" (the basic id of the subcategory), "subcat" the actual name of the subcategory.

 

so the way i see it, have a normal drop down populated by a php query. then on change, populate subcategory drop down where cat = cat and display sub category.

 

i just need to put it into code. can someone please help me.

 

[GingerRobot]

Take a look into php and AJAX. You'll need to use ajax to fill the second drop down box after searching the database.

[jbrill]

i figured ajax would be the answer, 

can anyone help write the script? i really know very little about ajax

[jbrill]

anyone?

[mpharo]

Thats very easy to do with PHP and Javascript, you dont need to use AJAX. All you do is create a form then onchange submit the form, then in the second select box you check for a non default value and if so then query for the possible subcategories and display the results. If you post an example I can show you.

[per1os]

If you search this forum you should find a few examples. I know in someone's signature there is a link to a page of how to do it. Forget his name but it can be found on these forums.

 

http://snippets.tzfiles.com/snippet.php?id=10

 

May be a start for you.

[jbrill]

when u say post an example what do u need? is what i posted in my first post not enough?

[mpharo]

a snippet of your current code would be most helpful in generating code that you can use.

[jbrill]

ok heres what i have so far,

This pulls all the categories from the category table:

 

		
<select name="cat">
<option selected value=''>Select Category</option>
<?

$catsql = "SELECT DISTINCT cat, category FROM category";
$cats = mysql_query($catsql);
$category = mysql_fetch_array($cats);

do

{
echo "<option value='".$category["category"]."'>".$category["category"]."</option>";


} while ($category = mysql_fetch_array($cats));



?>
</select>

 

and this is currently pulling all the sub categories (obviously i want this to correspond only with the chosen category):

<select name="subcat">
<option selected value=''>Select Category</option>
<?

$subcatsql = "SELECT DISTINCT subc, subcat FROM subcategory";
$subcats = mysql_query($subcatsql);
$subcategory = mysql_fetch_array($subcats);

do

{
echo "<option value='".$subcategory["subcat"]."'>".$subcategory["subcat"]."</option>";


} while ($subcategory = mysql_fetch_array($subcats));



?>    
</select>

 

[Psycho]

Can you provide an idea of how many categories and the average number of subcategories each category has? If you are talking about a relatively small number of options, then you can do it ALL with just JavaScript. Use the PHP to dynamically create the JavaScript necessary.

 

I will post some sample code in a few minutes.

[jbrill]

ok let me explain a bit about the project, im creating a job management program online for a client.

 

the project is for a machine shop, they can create a category, this category lets say is called "drill" and then they can add subcategories to that category, lets call this subcategory "small drill press" and it will display as:  "drill" using "small drill press".

 

i would say there are going to be about 12 categories and about 3 subcategories per category (approx figures)

[Barand]

The baaSelect link in my sig will give you a purely javascript solution. Or there's an AJAX sample there too.

[Psycho]

With that small number of options, I would definitely go with a JavaScript only solution. Here is a working JavaScript solution as an example. You would just need to use the PHP to dynamically create the JavaScript arrays.

 

<html>
<head>

<script type="text/javascript">


// State lists
var states = new Array();

states['Canada'] = new Array('Alberta','British Columbia','Ontario');
states['Mexico'] = new Array('Baja California','Chihuahua','Jalisco');
states['United States'] = new Array('California','Florida','New York');


function setStates(){

  cntrySel = document.getElementById('country');
  cntryVal = cntrySel[cntrySel.selectedIndex].value;

  if (states[cntryVal]) {
    stateList = states[cntryVal];
  } else {
    stateList = false;
  }

  changeSelect('state', stateList);

}

function changeSelect(fieldID, newList) {

  selectField = document.getElementById(fieldID);
  selectField.options.length = 0;

  if (newList!=false) {

    for (i=0; i<newList.length; i++) {
      selectField.options[selectField.length] = new Option(newList[i], newList[i]);
    }
    selectField.disabled = false;

  } else {

    selectField.options[selectField.length] = new Option('Not Applicable', '');
    selectField.disabled = true;

  }
}

</script>

</head>

<body onload="setStates();">
<form name="test" method="POST" action="processingpage.php">

Country: 
<select name="country" id="country" onchange="setStates();">
  <option value="Canada">Canada</option>
  <option value="Mexico">Mexico</option>
  <option value="United States">United States</option>
  <option value="CountryA">CountryA</option>
  <option value="CountryB">CountryB</option>
</select>
<br>

State: 
<select name="state" id="state">
  <option value="">Not Applicable</option>
</select>
<br>

</form>
</body>
</html>

[jbrill]

the only problem witht he javacript option is that it does not allow my client to add categorys and subcategories. i reallly need to be able to have that option so i must pull the data from the database...

[Barand]

with baaSelect all you need is this to populate them from the database

 

<?php

include 'db.php';  // connect stuff

include 'baaSelect.php';
$sel = new baaSelect();

$sel->addSelect('category', 'category', 'categoryID', 'cat_desc','',1,'--Category--');
$sel->addSelect('subcat', 'subcategory', 'subcatID', 'subcat_desc','cat_ID',1,'--Sub_category--');
?>

<HTML>
<HEAD>
<meta Name="generator" content="PHPEd Version 4.5 ">
<title>Sample</title>

<? $sel->makeScript() ?>             <!-- creates all the js code for you -->

</HEAD>
<BODY>
<form method=get>
<?
$sel->makeSelect('category');         // this creates the HTML
$sel->makeSelect('subcat');
?>
</form>
</BODY>
</HTML>

[jbrill]

getting this error,

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/morow/public_html/test.php on line 8

with the following code:

<?php

include 'includes/dbconnect.php';
include 'baaSelect.php';

$sel = new baaSelect();

$sel->addSelect('category', 'category', 'cat', 'cat_desc','',1,'--Category--');
$sel->addSelect('subcat', 'subcategory', 'subc', 'subcat_desc','cat',1,'--Sub_category--');
?>

<HTML>
<HEAD>
<meta Name="generator" content="PHPEd Version 4.5 ">
<title>Sample</title>

<? $sel->makeScript() ?>             

</HEAD>
<BODY>
<form method=get>
<?
$sel->makeSelect('category');         
$sel->makeSelect('subcat');
?>
</form>
</BODY>
</HTML>

 

 

[Barand]

I can't spot a parse error. Could be something upstream.

 

Takes out the 2 include lines and see if you still get a parse error.

 

If not, add them back one at a time and see if one of those is the cause.

[jbrill]

just tried your suggestion, still and error on this line:

$sel->addSelect('category', 'category', 'cat', 'cat_desc','',1,'--Category--');

 

[jbrill]

figured out the error... now to get the script working.. wat does cat_desc do?

[Psycho]

the only problem witht he javacript option is that it does not allow my client to add categorys and subcategories. i reallly need to be able to have that option so i must pull the data from the database...

 

Which is why I stated you would use PHP to dynamically create the JS data (from the database). I have not used baaSelect, but it seems it will be an easier implementation for you.

[jbrill]

how do i go about fixing this?

 

 

[Barand]

$sel->addSelect('category', 'category', 'cat', 'cat_desc','',1,'--Category--');

 

The arguments are

- name for the < select >

- table containing the data

- id field in your table for option values

- decription field in your table which appears in the dropdown

- foreign key (blank in first dropdown) l

- sequence preference (1 = sort by description)

- text to appear when no selection made

[jbrill]

I would like to take the time to say "you are and effing genius!" 

[Barand]

You got it working, then ?

[jbrill]

int he test i got it working, just gotta apply it to the site now