SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Colorado
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Catagory/Subcatagory (With no Parent)

    Ok here is my problem,

    I am atempting to mod a current store front that a customer of mine uses. The database doesn't seem very well structured however they have alot of data already stored in it (witch i do not mind adding extra fields to the current Database)

    ok now on to the question:

    The table is designed as such

    Code:
    ID (Primary Key) - Text
    Name - Text
    Category - Text
    SubCategory - Text
    Description - Memo
    OnWeb - Text

    Ok so when someone does a search on the site (by catagory/keyword)
    it executes this query:

    Code:
    	SELECT * FROM Products 
            WHERE (OnWeb = 'Yes') 
    	AND (Category = '#Cat#') 
    	AND SubCategory LIKE '#SubCat#'
    	ORDER BY SubCategory
    Now I can't see a way to list the data in the following way without a parent ID (witch there is none)

    Catagory
    -Subcatagory
    -Item1
    -Item2
    -Subcategory
    -Item1
    -Item2
    -Item3

    And when someone does a master search for all to add Catagory into the drop down list

    Catagory
    -Subcatagory
    -Item1
    -Item2
    -Subcategory
    -Item1
    -Item2
    -Item3

    Catagory2
    -Subcatagory
    -Item1
    -Item2
    -Item3
    -Subcategory
    -Item1
    -Item2

    Sorry for the long post just trying to be specific
    Also if it is relevent I am useing an access database and the programing language is coldfusion (I know this is a MySQL forum however I think my error is in the SQL statment and no one in coldfusion knows the fix)

    Thanks in advance

    Mallek

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the reason nobody answered it in the coldfusion forum is because you didn't post it there

    Code:
    <cfquery name="getall">
    select Category
         , SubCategory
         , ID
         , Name
         , Description
      from Products 
     where OnWeb = 'Yes') 
    order 
        by Category
         , SubCategory
         , ID
    </cfquery>
        
    <cfoutput query="getall" group="Category">
    <p>#Category#
    <cfoutput query="getall" group="SubCategory">
    <br />- #SubCategory#
    <cfoutput>
    <br />- #Name# #Description#
    </cfoutput>
    </cfoutput>
    </p>
    </cfoutput>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Colorado
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Coldfusion Post

    I posted on were-here (another coldfusion forum)

  4. #4
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Colorado
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    Thank you for the quick response that worked perfect for the outcome i was trying to get.


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
  •