SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,068
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dependent Drop Down Lists

    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:

    Code:
    <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

    Thank you in advance

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,068
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    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:

    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:

    Code:
    <cfif isDefined('Form.p_cat_id')>
    <cfset page.select_category = p_cat_id />
    </cfif>
    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.

    Hope this is useful fore someone

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    http://www.forta.com/blog/index.cfm/...elated-Selects

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We must have posted at the same time.

    Yeah, you can resubmit the form. If you prefer not to reload the page to refresh the lists, go with w/the ajax option.

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,068
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, ok You could also use plain javascript or jquery with mx6/7. But what you've got already works fine too.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •