This content has been marked as final. Show 12 replies
What is MT.Prod_ID34?
ok lets start at the beginning as i have been on this for about 3 weeks now!!!
if you look at http://www.southerncooling.com/Parts.cfm then use the selection BRAND, CATEGORY, MODEL
THE RESULTS PAGE SHOULD SHOW ALL PRODUCTS THAT MATCH THE MODEL SELECTED, AND THEN SHOW NEXT TO THE PRODUCTS THE RANGE NAME FOLLOWED BY THE MODELS NUMBERS THAT THE PRODUCT IS ACCOCIATED WITH.
884443 // EA-34, 45, 56 EF-34,45,46
THEN HAVE THEM GROUPED INTO CATEGORYS FROM THE CATEGORIES TABLE
HOPE THIS MAKES SENSE
i have the following tables
and this is the full output i need
<table width="100%" border="0">
<cfoutput query="Products" group="Cat_Name">
<td bgcolor="B02300" class="MainMidWhite"><strong>#Products.Cat_Name#</strong></td>
<td><table width="100%" border="0">
<td bgcolor="##000000" class="fixed5"><div align="left" class="MainMidWhite"><strong>Part No </strong></div></td>
<td bgcolor="##000000"><div align="left" class="MainMidWhite"><strong>Discription</strong></div></td>
<td bgcolor="##000000" class="fixed5"> </td>
<td bgcolor="#bgcolor#" class="fixed5">#Products.Part_No#</td>
<td bgcolor="#bgcolor#"><div align="left" class="Main1Black">#Products.Discription# </div></td>
<td bgcolor="#bgcolor#" class="fixed5"><div align="center"><span class="style3">#Products.Range_Name#-#Products.Model_Name#,</span> </div></td>
Could you put back the query that was in the original post?
o yes there you go!!
<cfquery name="Products" datasource="#application.ds#">
FROM Products_Table PT, category_table CT, model_prods_table MT
INNER JOIN product_to_manufact PTM
ON PT.Products_ID = PTM.PM_Product_ID
INNER JOIN product_to_category PTC
ON PT.Products_ID = PTC.PC_Product_ID
INNER JOIN manufactures_table MFT
ON MFT.Man_ID = PTM.PM_Man_ID AND MFT.Man_ID = '#session.manufac#'
INNER JOIN range_table RT
ON RT.Range_ID = MT.Range_Model
INNER JOIN model_table MMT
ON MMT.Model_ID = '#session.ModelNo#'
WHERE PT.Products_ID = MT.Prod_ID34 AND MT.Model_ID34 = '#session.ModelNo#' AND CT.Cat_ID = PTC.PC_Category_ID
GROUP BY Cat_ID
Your db is set up with a many to many relationship between product and category. This means a product could show up more than once, because the category is different. Is that the way it really is? Are these the duplicates you are seeing?
By the way, using "select *" might be easy to type, but it has some disadvantages. You might consider selecting only those fields you need for display or anchor tags.
Also, while your sql is correct, it would be easier to read if you made all your joins in the same clause. You have some in your from clause and some in your where clause.
ok thanks Dan
yes they are the duplicates i am getting, but how would i change my db so i can do this
1. i need a products table
2. products need to be linked to categories (so i can search by category)
3. products need to linked to manufacturer (so i can search by manufacturer)
4. then i have a range and model table (these link products to a range and a model, so a product may be available for 5 differnt models or ranges)
so just not sure the best way to setup the db, thought i had done it with nomialization
Let's say that you had products and categories. A product can only belong to one category, but a category can have many products. It goes something like this:
product_id not null, primary key
category_id not null, foreign key
category_id not null, primary key
and so on and so forth. You had the right idea. Just overthought the details it seems.
Ok yes i see,
but what happens when i what to link the range table and the model table
so each product may have 1 or more of a range or model
If a product can have more than one range, and a range can apply to more than one product, it's called a many to many relationship. The table structure resembles what you have now, but not for models and ranges.
Using ranges as an example, the way I like to do it is,
product_id not null, foreign key to product
range_id not null, foreign key to range
the primary key has two fields, product_id, and range_id.
ok so am i right in saying thats the table product_range as u just surgested, will be inbetween
the products table and the range table
if so i will work on it and get back to you as i know i will need some help the the query to show the results
try inner joins
ok Dan i have sorted the db, now i need to show range_name and model_name next to each product
if you look at http://www.southerncooling.com/ProductResults.cfm?Model=1
the first part no 505350 should have a discription of
Tank Beige EXH-130, 150, 170, 180 EXA-130, 150, 270, 280
but instead it only shows one becaise of the session in the where clause
I have this in the product_range table but not sure how to set the query to show