Hi all. I have three tables (categories, Subcategories and Products) On the front end I have a query that generates the category/subcategory menu items depending on what products are in the database ant it works fine (It only shows categories and subcategories if there are products in the database with those cat_id and subcat_id). This is the query I use at the front end:
<cfquery name="getMenuitems" datasource="#arguments.dsn#">
SELECT DISTINCT p.p_cat_id, p.p_subcat_id, c.p_cat_name, s.p_subcat_name
FROM LProducts p
LEFT JOIN LCategories c ON p.p_cat_id = c.p_cat_id
LEFT JOIN LSubcategories s ON p.p_subcat_id = s.p_subcat_id
GROUP BY p_cat_name, p_subcat_name
ORDER BY p_cat_name, p_subcat_name
</cfquery>
Now In my CMS I need two dependent drop downs (Categories and Subcategories) to be able to update a certain Subcategory)
Note: doing this without dropdown list gives me an endless list of Subcategories
I tried some Javascript that I found but they don’t give me the result I’m looking for. What I’m looking for is this: A dropdown from Categories ( Only the categories from products in the database with those cat_id’s). When choosing one the dependent dropdown (Subcategories) need to generate the subcategories belonging to that category. When I choose a subcategory from the list and press the submit button I go to the subcategory update page for that particular subcategory
I wish I could Like I said in one of my other posts, I’m still waiting for my hosting company to upgrade to CF8 Untill then I have to do with this kind of solutions. But I will have a look at the article anyway and save it for future purpose. Thank you for that.
For CF8+ you could use ajax. There’s an example for 8.0 in the link below. As of 8.0.1 updater (I think it’s 8.0.1) it’s even simpler. You can skip the arrays and return a query from the cfc’s.
I found a solution, which I would like to share with you all. (Just a little javascript involved) Maybe it is useful for someone: Here is the code:
<cfquery name="getCategories" datasource="#Request.dsn#">
SELECT DISTINCT p.p_cat_id, c.p_cat_name
FROM LProducts p
LEFT JOIN LCategories c USING(p_cat_id)
ORDER BY p_cat_name;
</cfquery>
<cfoutput>
<form action="" method="post" name="addForm" id="addForm">
<div>
<div>
<cfif isDefined('Form.p_cat_id')>
<cfset page.select_category = p_cat_id />
</cfif>
<label for="p_cat_id" class="left">Category:</label>
<select name="p_cat_id" id="p_cat_id" class="selectwide validate[required]" onchange="this.form.submit();">
<option value="" selected>select</option>
<cfloop query="getCategories">
<option value="#p_cat_id#"<cfif isDefined('Form.p_cat_id')><cfif Form.p_cat_id eq "#p_cat_id#">selected</cfif></cfif>>#p_cat_name#</option>
</cfloop>
</select>
</div>
<div>
<label for="subcategory_id" class="left">Brand:</label>
<cfif isDefined('page.select_category')>
<cfquery name="getSubcategories" datasource="#Request.dsn#">
SELECT DISTINCT p.p_subcat_id, s.p_cat_id, s.p_subcat_name
FROM LProducts p
LEFT JOIN LSubcategories s USING(p_subcat_id)
WHERE s.p_cat_id = #page.select_category#
ORDER BY p_subcat_name
</cfquery>
<select name="p_subcat_id" id="p_subcat_id" class="selectwide">
<option value="" selected>select</option>
<cfloop query="getSubcategories">
<option value="update_brands.cfm?sub=#p_subcat_id#">#p_subcat_name#</option>
</cfloop>
</select>
<cfelse>
<select name="p_subcat_id" class="selectwide ">
<option value="" selected>select</option>
</select>
</cfif>
</div>
<div style="margin-top:10px">
<label for="classified_button" class="left"></label>
<input type="button" class="btn" onClick="location=document.addForm.p_subcat_id.options[document.addForm.p_subcat_id.selectedIndex].value;" value="update" style="margin-left:105px;">
</div>
</div>
</form>
</cfoutput>
What it basically does is that I first created a category query coming from the products table. As you can see making a choice from the category dropdown submits the form: onchange="this.form.submit();. With that In mind I declared a variable:
Then with the <cfif isDefined(‘page.select_category’)> followed by the subcategories query (also coming from the products table) within the form I could create the dependent subcategory dropdown.
You could also use plain javascript or jquery with mx6/7. But what you’ve got already works fine too.
Like I said in one of my other posts, I’m still waiting for my hosting company to upgrade to CF8 
Untill then I have to do with this kind of solutions. But I will have a look at the article anyway and save it for future purpose. Thank you for that.