SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getrows for Categories then products

    Hi, well im developing a Product configurator (just like alienware use) where in my database i have
    (systems table) a list of PC systems
    systemID
    (categories table) a list of PC product categories (ie. CPU, RAM...)
    categoryID
    (products table) a list of all products
    productID, catID
    (link table)
    sysID and prodID

    Now before i put the code ive done so far, let me explain that the first page lists all systems wich then gives the systemID for the link table. Once this page loads it needs to go through each category then list the products available to that category where sysID = systemID. Now a query i put together to list all products for that sysID is:

    select * from PRODUCTS inner join link on link.prodID = PRODUCTS.productID where link.sysID=systemID

    Now this query works great and lists all of the products for that systemID, but what im unable to do is (using GetRows()) have the category name listed as a title then have each product under the respected category.

    MY CODE:
    'LIST CATEGORY NAME THEN PRODUCTS IN THAT CATEGORY WHERE SYSTEM ID = 1
    response.write "<p><b>Using System ID 1</b><p>"
    prods.Open "select * from PRODUCTS inner join link on link.prodID = PRODUCTS.productID where link.sysID=1", Conn, 1, 3
    'do until prods.eof
    'response.write prods("productName") & "<br>"
    'prods.movenext
    'loop
    products = prods.GetRows()
    prods.close

    Dim iR, iC
    For iR = 0 To UBound(products, 2)
    For iC = 0 To UBound(products, 1)
    Response.Write products(iC, iR) & "<br>"
    Next
    Next


    Now i wasnt expecting that array loop to produce the results I wanted rather atleast product 7 results (as there are currently only 7 records in the link table). If you look at the commented out code before the loop; this result gave 7 product names. Looking for some guidance as I am stumped! (also please dont tell me to post this in SQL forum as im needing ASP and SQL help here thanks! )

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first thing I want to say is this:
    How are you getting the category NAME (not just the ID) in the query involving PRODUCTS inner join LINK? It seems to me like you would only get the CategoryID, not the name. 'Name' belongs in the Categories table, no? You didn't join to that table and part of my answer to you is to add a join. You need triple join sytax?

    Give me a better sample of the data returned by that query (show me at least the names of fields you're talking about, like catname) Assuming this query IS correct, are you saying that you would still have a problem without using Getrows() and arrays?

    I know that you want to list the results of your query as "products under this systemid", but what exactly is the problem?
    i wasnt expecting that array loop to produce the results I wanted
    Is this because you're not sure if the query is correct or you're not familiar with the nature of how the array loops work? Could you get this to work by simply looping through the recordset object? (It wouldn't hurt you that much..)

    All in all, it just sounds like you need to add a join to Categories. I would recommend looping through the recordset in order to get the expected output. THEN switch it to getrows, which I know is a little perf booster, but you have bigger things to worry about...

    Dan

  3. #3
    Guru Bullschmidt's Avatar
    Join Date
    Apr 2002
    Location
    USA
    Posts
    524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And perhaps this may hopefully give you some ideas. It's got a downloadable sample and does retain values after the post back:

    Classic ASP Design Tips - Dependent Listboxes
    http://www.bullschmidt.com/devtip-de...tlistboxes.asp
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


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
  •