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.