3 table join with category headings

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

3 table join with category headings

Post 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)
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: 3 table join with category headings

Post 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.
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: 3 table join with category headings

Post 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...
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: 3 table join with category headings

Post 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 ?
dannyd
Forum Commoner
Posts: 56
Joined: Wed Jan 23, 2008 11:31 am

Re: 3 table join with category headings

Post 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.
Post Reply