Page 1 of 1
3 table join with category headings
Posted: Wed Jan 23, 2008 11:43 am
by dannyd
How can I have the output of these tables to look like this:
Beauty
product 1
product 2
product 3
Fitness
product 4
product 5
etc...until it loops through all cateogories and products
from these 3 tables
product table(prodid,prod_name)
product_bridge(prodid, catid)
categories(catid,catname)
Re: 3 table join with category headings
Posted: Wed Jan 23, 2008 12:01 pm
by Zoxive
Code: Select all
SELECT pt.product_name,c.catname FROM product_table pt
LEFT JOIN product_bridge pb on pb.prodid = pt.prodid
LEFT JOIN categories c on c.catid = pd.catid
ORDER BY c.catname ASC;
Very basic way to do it, I believe this will work.
Re: 3 table join with category headings
Posted: Wed Jan 23, 2008 1:13 pm
by dannyd
Thanks for the reply .. that seems output to match products with respective categories .. example below:
Makeup Kit Beauty
Makeup Kit 2 Beauty
AB Hammer Fitness
Leg Slimmer Fitness
SunTravel Travel
How would you display the results in a php so it would Display:
Beauty
Makeup Kit 1
Makeup Kit 2
Fitness
AB Hammer
Leg Slimmer
Travel
SunTravel
etc...
Re: 3 table join with category headings
Posted: Wed Jan 23, 2008 2:04 pm
by dannyd
Here is my code for attempting to do this . I guess having two stored sql queries
1. for categories
2. for products within those categories
What i was trying to do here is output a category then products within that category. Not sure what the correct syntax is...
$sql1 = 'SELECT DISTINCT (c.catlabel) FROM product_categories c JOIN product_bridge b ON b.catid = c.catid JOIN products a ON a.cadid = b.cadid AND a.disabled = "N" AND a.startdate <= now( ) AND a.stopdate >= now( ) ORDER BY c.catlabel ASC; ';
$result1 = mysql_query($sql1);
while(list($catlabel) = mysql_fetch_row($result1)){
echo $catlabel . '<BR>';
$sq2 = 'SELECT a.clientname FROM products a, product_bridge b, product_categories c WHERE a.cadid=b.cadid AND b.catid=c.catid AND c.catlabel="' . $catlabel . '";';
$result2 = mysql_query($sql2);
while(list($clientname) = mysql_fetch_row($resultl2)) {
echo $clientname;
}
}
any ideas ?
Re: 3 table join with category headings
Posted: Wed Jan 23, 2008 2:27 pm
by dannyd
I guess what im trying to do is a loop through categories and then loop through the products respective to the categories... not sure how the php mysql syntax would work for that.