AJAX / Javascript / PHP Triple Drop Down Menu from different tables

Question

I've been searching for this for so many days but still couldn't figure it out.

I want to make a drop down menu for searching and the flow would be like this:

1st DropDown

Search By :

Category

Brand

Location

Supplier

Owner

And once the user click any of the above, the 2nd DropDown should display the respective list according to the 1st DropDown:

For example, 1st DropDown = Category:

2nd DropDown

Limit By:

Laptop

Desktop

Router

LCD

Scanner

Or 1st DropDown = Brand:

2nd DropDown

Limit By:

Dell

Sony

HP

Samsung

Acer

And the 3rd DropDown menu will be the Order By the 1st DropDown, like:

3rd DropDown

Order By:

Category

Brand

Location

Supplier

Owner

My problem is, every option in 1st DropDown has its own table, and that would trigger 2nd DropDown menu, and somehow that I can't figure out how to call it. All tutorials & examples that I found, they trigger something in 1st DropDown menu which is within 1 table & 2nd & 3rd DropDown could call another table. (For example: Country, State, City).

Therefore, I want to know if this is possible, where the 1st DropDown menu is from different tables, 2nd DropDown menu will populate from the 1st DropDown menu option choosen, and 3rd DropDown menu will order the 2nd DropDown menu with 1st DropDown menu option.

I hope you understand what I'm asking and I really hope to get the feedback very soon, it's so much needed. Thank you and I really appreciate it!

EDITED

search.html

.....
                    <form id="drop_list" name="drop_list" action="searchedasset.php" method="post" >
                    <fieldset><table><tr>

                    <thead>
                                <tr><legend><b>SEARCH ASSET</b></legend></tr>
                    </thead>
                    <tbody>
                               <tr> <!--code for the first list box-->
                                   <td>Search By :</td>

                                   <td><select name="cat_id" onChange="SelectSubCat1();" >
                                       <Option value="">Choose Search By</option>
                                       </SELECT>
                                   </td>
                               </tr>

                              <tr>
                                  <td>Limit By :</td>
                                  <td><select id="SubCat1" name="SubCat1">
                                      <Option value="">Choose Limit By</option>
                                      </SELECT>
                                  </td>
                              </tr>
                              <tr>
                                  <td>Order By :</td>
                                  <td><select name="SubCat2">
                                  <option value="">Choose Order By</option>
                                  <option value="1">Brand </option>
                                  <option value="2">Category </option>                                      
                                  <option value="3">Project </option>
                                  </select>
                                  </td>
                              </tr>

                        </tbody>
                        </tr></table></fieldset>
.....

search.js

function fillSearch(){
// this function is used to fill the category list on load
addOption(document.drop_list.cat_id, "1", "Brand", "");
addOption(document.drop_list.cat_id, "2", "Category", "");
addOption(document.drop_list.cat_id, "3", "Project", "");
}

function SelectSubCat1(){
// ON selection of category this function will work

removeAllOptions(document.drop_list.SubCat1);
//clear all the elements of the second list

addOption(document.drop_list.SubCat1, "", "Choose Limit By", "");

if(document.drop_list.cat_id.value == '1'){
addOption(document.drop_list.SubCat1,"1", "DELL");
addOption(document.drop_list.SubCat1,"2", "ACER");
addOption(document.drop_list.SubCat1,"3", "SONY");
addOption(document.drop_list.SubCat1,"4", "SAMSUNG");
addOption(document.drop_list.SubCat1,"5", "APPLE");
}

if(document.drop_list.cat_id.value == '2'){
addOption(document.drop_list.SubCat1,"='1'", "Notebook");
addOption(document.drop_list.SubCat1,"2", "Desktop");
addOption(document.drop_list.SubCat1,"3", "LCD Projector");
addOption(document.drop_list.SubCat1,"4", "Scanner");
addOption(document.drop_list.SubCat1,"5", "Printer");
}

if(document.drop_list.cat_id.value == '3'){
addOption(document.drop_list.SubCat1,"1", "1st Purchase");
addOption(document.drop_list.SubCat1,"2", "2nd Purchase");
addOption(document.drop_list.SubCat1,"3", "3rd Purchase");
addOption(document.drop_list.SubCat1,"4", "4th Purchase");
addOption(document.drop_list.SubCat1,"5", "5th Purchase");
}
}
}

.....

Note that SubCat1 was supposedly from 3 different tables which hold different name:

e.g. [cat_id.value == '1', SubCat1 == 'brandid'][cat_id.value == '2', SubCat1 == 'categoryid'][cat_id.value == '3', SubCat1 == 'purchaseid']

Therefore, is there any way we can do that? To change the SubCat1 accordingly to their name in the database? This is to display the data in the next page. Thank you.

Answer 1

You can achieve this by virtue of constructing valid views for your task. The view should be based on a structure like the one below:

column1 column2 column3

({Category},{Laptop,Desktop,Router,LCD, Scanner},{Category,Brand,Location,Supplier,Owner}), ({Brand},{Dell,Sony,HP,Samsung, Acer},{Category,Brand,Location,Supplier,Owner}) and so on.

for column2 and 3, you can call in function like group_concat(if you are making use of mysql) or similar function in other databases.

Once that is available, you can then easily populate the list. Load Column1 data to first dropdown. When that gets changed, retrieve column2 and column3 based on that selection, and load them into dropdown2 and dropdown3. You need to split the contents of column2 and column3 into arrays and load them.

Did this solve your issue?

---

You can proceed like this: Well, you can't generate all the things directly from the database.

You need to split up the process. For the rendering process, you need to get the required data built from the database to a certain extent, and using that you need to modify the other information.

I can put it in this way:

1. Define the views in the database with the required format and joins for the initial pull-up.
2. Retrieve the data in your scripting language, which in this case happens to be php.
3. Format the retrieved data by getting the appropriate queries to load the required information.
4. Cross link it with the routines on the ajax end, like, on-change for the dropdown to fetch the corresponding data.

You need to have separate functions in php and ajax to model that, namely, one to load the first dropdown, one to load the second and third dropdown. Map it with php and ajax and your issues should be gone.

Hope this gives you an idea!

Answer 2

im not sure if js syntax are correct but the idea is here:

<?php

$category = //result from table category
$brands= //result from table category

?>

<script>
var categories = <?php json_encode($category);?>
var brands= <?php json_encode($brands);?>

</script>

<select id="categories_selector" onchange="populateBrand(this.value)">
</option value="">Select Category</option>
</option value="1">Cat 1</option>
...
</option value="2">Cat 2</option>
</select>

<select id="brands_selector">
//no options yet
</select>

<script>

function populateBrand(category_id)
{

var aOptions = [];
 for(var i in brands)
  if(brands[i].category == category_id)
  {
     var oOption = document.createElement('option');
     oOption.id = brands[i].id;
     oOption.value = brands[i].name;
     aOptions.push(oOption);
  }

var oBrands = document.getElementById('brands_selector');
oBrands.addOptions(aOptions);

}
</script>