Dynamic Drop-Down List

SalientAnimal · April 4, 2014

Hi All,

I have the following code that I am using to create dynamic drop-down lists from my MySQL database. However, my third list isn't generating at all. Could someone please take a look and let me know what I might be missing?

Also, is this the best method to do a list like this?

?>

<!doctype html public "-//w3c//dtd html 3.2//en">

<html>

<head>
<title>Demo of Three Multiple drop down list box from plus2net</title>
<meta name="GENERATOR" content="Arachnophilia 4.0">
<meta name="FORMATTER" content="Arachnophilia 4.0">
<SCRIPT language=JavaScript>

function reload(form)
{
var val=form.primary_category.options[form.primary_category.options.selectedIndex].value; 
self.location='dd3.php?primary_category=' + val ;
}


function reload2(form)
{
var val=form.primary_category.options[form.primary_category.options.selectedIndex].value; 
var val2=form.secondary_category.options[form.secondary_category.options.selectedIndex].value; 

self.location='dd3.php?primary_category=' + val + '&secondary_category=' + val2 ;
}


function reload3(form)
{
var val=form.primary_category.options[form.primary_category.options.selectedIndex].value; 
var val2=form.secondary_category.options[form.secondary_category.options.selectedIndex].value; 
var val3=form.tertiary_category.options[form.tertiary_category.options.selectedIndex].value; 

self.location='dd3.php?primary_category=' + val + '&secondary_category=' + val2 + '&tertiary_category=' + val3 ;
}


</script>
</head>

<body>
<?php


///////// Getting the data from Mysql table for first list box//////////
$quer2="
		SELECT DISTINCT 
					primary_category
		,			category_id 
		FROM 
					category_query
		ORDER BY
			 		primary_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///// 
$cat=$_GET['primary_category']; // This line is added to take care if your global variable is off
if(isset($cat) and strlen($cat) > 0){
$quer="
		SELECT DISTINCT 
					secondary_category
		,			secondary_id 
		FROM 
					category_query 
		WHERE 
					category_id = $cat 
		ORDER BY 
					secondary_category
		"; 
}
////////// end of query for second subcategory drop down list box ///////////////////////////


/////// for Third drop down list we will check if sub category is selected else we will display all the subcategory3///// 
$cat2=$_GET['secondary_category']; // This line is added to take care if your global variable is off
if(isset($cat2) and strlen($cat2) > 0){
$quer3="
		SELECT DISTINCT 
					tertiary_catergory 
		,			tertiary_id
		FROM 
					category_query 
		WHERE 
					secondary_id = $cat2
		ORDER BY 
					tertiary_catergory
		"; 
}else{$quer3="SELECT DISTINCT subcat2 FROM subcategory2 order by subcat2"; } 
////////// end of query for third subcategory drop down list box ///////////////////////////




echo "<form method=post name=f1 action='dd3ck.php'>";
//////////        Starting of first drop downlist /////////
echo 
											"<select name='primary_category' onchange=\"reload(this.form)\"><option value=''>Select one</option>";
foreach ($dbo->query($quer2) as $notice2) {
if($notice2['category_id']==@$cat){echo 
											"<option selected value='$notice2[category_id]'>$notice2[primary_category]</option>"."<BR>";}
else{echo  									"<option value='$notice2[category_id]'>$notice2[primary_category]</option>";}
}
echo "</select>";
//////////////////  This will end the first drop down list ///////////





//////////        Starting of second drop downlist /////////
echo 
											"<select name='secondary_category' onchange=\"reload2(this.form)\"><option value=''>Select one</option>";
foreach ($dbo->query($quer) as $notice) {
if($notice['secondary_id']==@$cat2){echo 
											"<option selected value='$notice[secondary_id]'>$notice[secondary_category]</option>"."<BR>";}
else{echo  									"<option value='$notice[secondary_id]'>$notice[secondary_category]</option>";}
}
echo "</select>";
//////////////////  This will end the second drop down list ///////////





//////////        Starting of third drop downlist /////////
echo 
											"<select name='tertiary_category' onchange=\"reload3(this.form)\"><option value=''>Select one</option>";
foreach ($dbo->query($quer3) as $notice) {
if($notice['tertiary_id']==@$cat3){echo 
											"<option selected value='$notice[tertiary_id]'>$notice[tertiary_category]</option>"."<BR>";}
else{echo  									"<option value='$notice[tertiary_id]'>$notice[tertiary_category]</option>";}
}
echo "</select>";
//////////////////  This will end the third drop down list ///////////




echo "<input type=submit value='Submit the form data'></form>";
?>
<br><br>
<a href=dd3.php>Reset and Try again</a>
<br><br>
 
</body>
</html>

Thanks

Edited April 4, 2014 by SalientAnimal

ginerjm · April 4, 2014

Have you run this code? Do you get any errors? I would think you do since you don't seem to use quotes on your indices. And the use of an @ on the variable that you are trying to retrieve and include in your list is kind of odd since there s/b no errors.

Run the code with php's error checking turned on, clean up any errors that occur then tell us what goes wrong.

Psycho · April 4, 2014

Tip #1: Don't put your PHP code in-line with your HTML. It makes it difficult to maintain your code and is harder to debug. Put the PHP code at the top of the page, before you start the <HTML> tag, and create the dynamic data and store in variables. Then just echo the variables in the HTML code.

Tip #2: Don't copy/paste code. If you need to do the same thing multiple times, create a function/process that takes the necessary parameters to produce the similar outputs. This applies to both your PHP and JavaScript code. When you try and copy/paste code it becomes easy to miss a simple mistake.

Tip #3: Your database seems to have a flawed design. You don't need separate columns for primary, secondary & tertiary. Instead each category can have a parent ID. The primary IDs would use 0 for the parent. The secondary categories would use the ID of their associated primary category and the tertiary categories would use the associated ID of the secondary category.

I didn't read through all of your code because, to be honest, it was very unorganized. So, I don't know what the problem is. But, here is a rewrite in a much more logical format. I didn't test as I don't have your database. So, I am sure there are some minor syntax errors to fix. But, the logic is sound.

<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('tab_test');
 
//Functions to create HTML for options list
function createOptions($optionList, $selectedValue)
{
    $options = '';
    foreach ($optionList as $option)
    {
        $selected = ($option['value']==$selectedValue) ? ' selected="selected"' : '';
        $options .= "<option value='{$option['value']}'{$selected}>{$option['label']}</option>\n"
    }
    return $options;
}
 
//Determine selected options passed on query string
$primary_category   = isset($_GET['primary_category'])   ? intval($_GET['primary_category'])   : false;
$secondary_category = isset($_GET['secondary_category']) ? intval($_GET['secondary_category']) : false;
$tertiary_category  = isset($_GET['tertiary_category'])  ? intval($_GET['tertiary_category'])  : false;
 
//Generate options for primary category
$query = "SELECT DISTINCT category_id AS value, primary_category AS label
          FROM category_query
          ORDER BY primary_category"; 
$optionList = $dbo->query($query);
$primary_category_options = createOptions($optionList, $primary_category);
 
//Generate options for secondary category
if($primary_category)
{
    $query = "SELECT DISTINCT secondary_id AS value, secondary_category AS label
              FROM category_query
              WHERE category_id = $primary_category
              ORDER BY secondary_category";
    $optionList = $dbo->query($query);
    $secondary_category_options = createOptions($optionList, $secondary_category);
}
 
//Generate options for tertiary category
if($secondary_category)
{
    $query = "SELECT DISTINCT tertiary_catergory AS value, tertiary_id AS label
              FROM category_query
              WHERE secondary_id = $secondary_category
              ORDER BY tertiary_catergory";
    $optionList = $dbo->query($query);
    $tertiary_category_options = createOptions($optionList, $tertiary_category);
}
 
?>
<!doctype html public "-//w3c//dtd html 3.2//en">
 
<html>
 
<head>
<title>Demo of Three Multiple drop down list box from plus2net</title>
<meta name="GENERATOR" content="Arachnophilia 4.0">
<meta name="FORMATTER" content="Arachnophilia 4.0">
    <script language="JavaScript">
 
        function getSelectValue(selectID)
        {
            var optionObj = document.getElementById(selectID);
            return optionObj.options[optionObj.selectedIndex].value;
        }
 
        function reload(form)
        {
            //Adding the unselected options should work fine
            var locationURL = 'dd3.php?';
                locationURL += 'primary_category='   + getSelectValue('primary_category');
                locationURL += 'secondary_category=' + getSelectValue('secondary_category');
                locationURL += 'tertiary_category='  + getSelectValue('tertiary_category');
            //Perform the reload
            self.location = locationURL;
        }
    </script>
</head>
 
<body>
 
<form method=post name=f1 action='dd3ck.php'>
    <select name='primary_category' id='primary_category' onchange="reload(this.form)">
        <option value=''>Select one</option>
        <?php echo $primary_category_options; ?>
    </select>
 
    <select name='secondary_category' id='secondary_category' onchange=\"reload(this.form)\">
        <option value=''>Select one</option>
        <?php echo $secondary_category_options; ?>
    </select>
 
    <select name='tertiary_category' id='tertiary_category' onchange=\"reload(this.form)\">
        <option value=''>Select one</option>
        <?php echo $tertiary_category_options; ?>
    </select>
 
    <input type=submit value='Submit the form data'>
</form>
 
<br><br>
<a href=dd3.php>Reset and Try again</a>
<br><br>
 
</body>
</html>

Edited April 4, 2014 by Psycho

Psycho · April 4, 2014

OK, I created a mock database so I could test. If I understand your database schema correctly, this should work Although you should really consider changing the DB schema.

<?php
 
//Function to create HTML for options list
function createOptions($optionList, $selectedValue)
{
    $options = '';
    foreach ($optionList as $option)
    {
        $selected = ($option['value']==$selectedValue) ? ' selected="selected"' : '';
        $options .= "<option value='{$option['value']}'{$selected}>{$option['label']}</option>\n";
    }
    return $options;
}
 
//Determine selected options passed on query string
$primary_category   = isset($_GET['primary_category'])   ? intval($_GET['primary_category'])   : false;
$secondary_category = isset($_GET['secondary_category']) ? intval($_GET['secondary_category']) : false;
$tertiary_category  = isset($_GET['tertiary_category'])  ? intval($_GET['tertiary_category'])  : false;
 
//Generate options for primary category
$query = "SELECT DISTINCT category_id AS value, primary_category AS label
          FROM category_query
          ORDER BY primary_category"; 
$optionList = $dbo->query($query);
$primary_category_options = createOptions($optionList, $primary_category);
 
//Generate options for secondary category
if($primary_category)
{
    $query = "SELECT DISTINCT secondary_id AS value, secondary_category AS label
              FROM category_query
              WHERE category_id = $primary_category
              ORDER BY secondary_category";
    $optionList = $dbo->query($query);
    $secondary_category_options = createOptions($optionList, $secondary_category);
}
 
//Generate options for tertiary category
if($secondary_category)
{
    $query = "SELECT DISTINCT tertiary_id AS value, tertiary_category AS label
              FROM category_query
              WHERE secondary_id = $secondary_category
              ORDER BY tertiary_category";
    $optionList = $dbo->query($query);
    $tertiary_category_options = createOptions($optionList, $tertiary_category);
}
 
?>
<!doctype html public "-//w3c//dtd html 3.2//en">
 
<html>
 
<head>
<title>Demo of Three Multiple drop down list box from plus2net</title>
<meta name="GENERATOR" content="Arachnophilia 4.0">
<meta name="FORMATTER" content="Arachnophilia 4.0">
    <script language="JavaScript">
 
        function getSelectValue(selectID)
        {
            var optionObj = document.getElementById(selectID);
            return optionObj.options[optionObj.selectedIndex].value;
        }
 
        function reload(form)
        {
            //Adding the unselected options should work fine
            var locationURL = 'dd3.php';
                locationURL += '?primary_category='   + getSelectValue('primary_category');
                locationURL += '&secondary_category=' + getSelectValue('secondary_category');
                locationURL += '&tertiary_category='  + getSelectValue('tertiary_category');
            //Perform the reload
            self.location = locationURL;
        }
    </script>
</head>
 
<body>
 
<form method=post name=f1 action='dd3ck.php'>
    <select name='primary_category' id='primary_category' onchange="reload(this.form)">
        <option value=''>Select one</option>
        <?php echo $primary_category_options; ?>
    </select>
 
    <select name='secondary_category' id='secondary_category' onchange="reload(this.form)">
        <option value=''>Select one</option>
        <?php echo $secondary_category_options; ?>
    </select>
 
    <select name='tertiary_category' id='tertiary_category' onchange="reload(this.form)">
        <option value=''>Select one</option>
        <?php echo $tertiary_category_options; ?>
    </select>
 
    <input type=submit value='Submit the form data'>
</form>
 
<br><br>
<a href=dd3.php>Reset and Try again</a>
<br><br>
 
</body>
</html>

Edited April 4, 2014 by Psycho

SalientAnimal · April 7, 2014

Hi there,

Thanks this really helped me a lot. I have to admit that I am a newbie at coding and every bit of help I get helps me just understand things a bit better.

SalientAnimal · April 7, 2014

Hi there,

Sorry I need to complicate things a bit here....

So the first set of Multiple drop-downs works, however I need to incorporate a second set of drop-downs, which will be dependent on each other, but not on the first lot of drop-downs. How do I go about adding the next set?

There are three additional sets I need to add.

One set will only have two drop-downs

Second set will also have 3 drop downs

Third will only have one option.

All the drop downs reference to different tables in my database.

Thanks again.

Psycho · April 7, 2014

The process I provided is very flexible. I'm not sure where the confusion lies. You need to add logic to the top portion to determine what data will be displayed in any of the select lists (same as you have now for the first three). Then run the necessary query to get the data and call the function createOptions() just like I did for the other three lists.

SalientAnimal · April 7, 2014

Of so I understand the logic you gave, the only problem that does seem to be happening, because the second list is not dependent on the first list. So the onChange causes all previous values to be reset if they are not part of the list.

I'm not sure if I am explaining this correctly?

Psycho · April 7, 2014

As I said, I created a mock database and tested the script to ensure it works. However, based upon your queries the database appears to be poorly constructed. I mocked the database to how I think you have it constructed and made the script work. You should fix your database.

SalientAnimal · April 8, 2014

Could I ask how you constructed your test database? I have this set of options in one table. So yes, the dynamic list works exactly as expected in what you gave me here.
The problem on exists when I want to add another multi tier list. So as and example on the form, first dynamic list will focus on 3 tiers. These options are all dependent on each other.

I then want to add another dynamic list, with 2 tiers, but on the same form.

What then happens, is when I select an option in the first item of the second list, the previous three get reset, because they do not impact on each other.

The other problem that exists is that all other form data that has been captured also gets cleared due to the onchange="reload(this.form) function.

I'm not sure how database structure would effect this?

Psycho · April 8, 2014

Sorry to be blunt, but your explanation are not very enlightening. But, your DB structure is still flawed. You're right that it should not have any impact on the current issue, but I almost bailed ont his thread because of the unnecessary work it entailed for me to replicate.

If changes to another set of "chained" select lists are affecting another set it would have to be due to the logic you are applying in the code to determine what the lists should contain. Are you using multiple forms? If so, only the data from one form would be passed and that would cause the other set of select lists to think there was nothing selected. Or, the onchange event is not getting ALL of the selected values from all the set's of select lists. That is why I rebuilt the JavaScript to have a single onchange function instead of multiple. Did you create new ones for each set? Please say you didn't.

SalientAnimal · April 9, 2014

I'm not sure how to explain it. But to answer your question on the onchange event, I did not create another event no. I Took the script exactly as you supplied it. Only difference being that I didn't have the entire form in the code I posted. Would you like to see the entire code?

I do admit that my DB structure isn't perfect, but what exactly am I doing wrong? Should I have separate tables for each menu item? What do you suggest to improve this structure? And what other information can I give you to help you in assisting me?

Thanks though for all the help you have given me thus far.

Psycho · April 9, 2014

I do admit that my DB structure isn't perfect, but what exactly am I doing wrong? Should I have separate tables for each menu item? What do you suggest to improve this structure? And what other information can I give you to help you in assisting me?

I already answered your first question in my first response under Tip#3. You have separate columns to indicate whether a record is a primary, secondary or tertiary. This is a poor structure. You only need columns for the category ID, the category Name and the parent ID. From that you can logically get the data for primary, secondary, tertiary or four, five, six, etc. levels deep. No need to create separate columns.

id | name        | parent
 1   Cat One     0  <== 0 indicates a parent category
 2   Cat Two     0
 3   Cat Three  0
 4   Cat 1-A      1 <== This is a secondary cat of Cat One
 5   Cat 1-B      1
 6   Cat 1-A-1   4 <== This is a tertiary category of Cat One - Cat 1-A

I really am not interested in seeing all of your code it it is haphazard as what you originally posted. You can go ahead and post it, but if it is going to take me too long to clean it up just to try and make sense of it I may not be able to help further.

SalientAnimal · April 10, 2014

Thanks, that made more sense.

I ended up using the code you provided to create the dynamic drop-downs.

Psycho · April 10, 2014

Thanks, that made more sense.

I ended up using the code you provided to create the dynamic drop-downs.

Ok, but if you fix your database structure as I've suggested, which you should really do, the queries become simpler. In fact, you can create ONE SINGLE QUERY to use for all of the lists. Just create a function to pass an optional parameter

To query the Primary Categories would look like

SELECT category_id, category_name
FROM categories
WHERE parent_id = 0

To query the secondary categories based upon a specific primary category

SELECT category_id, category_name
FROM categories
WHERE parent_id = $primary_category_id

To query the tertiary categories based upon a specific secondary category

SELECT category_id, category_name
FROM categories
WHERE parent_id = $secondary_category_id

Note the only difference in the parent_id value. So, you can create a function to use for all of them. Here is the code I posted revised on how it could be done if the DB was properly built. As you can see it is so, so much simpler. There are just two functions and then only two lines of code to build each select list!

<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('tab_test');
 
//Functions to create HTML for options list
function createOptions($optionList, $selectedValue)
{
    $options = '';
    foreach ($optionList as $id => $label)
    {
        $selected = ($id==$selectedValue) ? ' selected="selected"' : '';
        $options .= "<option value='{$id}'{$selected}>{$label}</option>\n"
    }
    return $options;
}
 
//Function to get a list of categories
function getCategories($parent_id=0)
{
    $query = "SELECT category_id, category_name
              FROM categories
              WHERE parent_id = {$parent_id}
              ORDER BY category_name"; 
    $results = $dbo->query($query);
    $categories = array();
    foreach($results as $cat)
    {
        $categories[$cat['category_id']] = $cat['category_name'];
    }
    return $categories;
}
 
//Determine selected options passed on query string
$selected_primary   = isset($_GET['primary_category'])   ? intval($_GET['primary_category'])   : false;
$selected_secondary = isset($_GET['secondary_category']) ? intval($_GET['secondary_category']) : false;
$selected_tertiary  = isset($_GET['tertiary_category'])  ? intval($_GET['tertiary_category'])  : false;
 
//Generate options for primary category
$primary_categories = getCategories(); //Get the primary categories
$primary_category_options = createOptions($primary_categories, $selected_primary);
 
//Generate options for secondary category
$secondary_categories = getCategories($selected_primary); //Get secondary categories based on selected primary
$secondary_category_options = createOptions($secondary_categories, $selected_secondary);
 
//Generate options for tertiary category
$tertiary_categories = getCategories($selected_secondary); //Get secondary categories based on selected secondary
$tertiary_category_options = createOptions($tertiary_categories, $selected_tertiary);
 
?>
<!doctype html public "-//w3c//dtd html 3.2//en">
 
<html>
 
<head>
<title>Demo of Three Multiple drop down list box from plus2net</title>
<meta name="GENERATOR" content="Arachnophilia 4.0">
<meta name="FORMATTER" content="Arachnophilia 4.0">
    <script language="JavaScript">
 
        function getSelectValue(selectID)
        {
            var optionObj = document.getElementById(selectID);
            return optionObj.options[optionObj.selectedIndex].value;
        }
 
        function reload(form)
        {
            //Adding the unselected options should work fine
            var locationURL = 'dd3.php?';
                locationURL += 'primary_category='   + getSelectValue('primary_category');
                locationURL += 'secondary_category=' + getSelectValue('secondary_category');
                locationURL += 'tertiary_category='  + getSelectValue('tertiary_category');
            //Perform the reload
            self.location = locationURL;
        }
    </script>
</head>
 
<body>
 
<form method=post name=f1 action='dd3ck.php'>
    <select name='primary_category' id='primary_category' onchange="reload(this.form)">
        <option value=''>Select one</option>
        <?php echo $primary_category_options; ?>
    </select>
 
    <select name='secondary_category' id='secondary_category' onchange=\"reload(this.form)\">
        <option value=''>Select one</option>
        <?php echo $secondary_category_options; ?>
    </select>
 
    <select name='tertiary_category' id='tertiary_category' onchange=\"reload(this.form)\">
        <option value=''>Select one</option>
        <?php echo $tertiary_category_options; ?>
    </select>
 
    <input type=submit value='Submit the form data'>
</form>
 
<br><br>
<a href=dd3.php>Reset and Try again</a>
<br><br>
 
</body>
</html>

SalientAnimal · April 13, 2014

Ok, I see what you've been saying and yes it makes a lot of sense. Thanks for this. I really do appreciate it.

So one question here:

Why is your code different on the "onchange"

You have onchange=\ "reload(this.form)\"> on the second two selects but on the first you have onchange="reload(this.form)">

Psycho · April 14, 2014

The second two are typos

SalientAnimal · April 14, 2014

Thanks.

Sign In

Dynamic Drop-Down List

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information