Multiple Table Query - (product tags)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Multiple Table Query - (product tags)

Post by simonmlewis »

Hi

We started to build a Product Tags system, so they would enter the Tag for each product, and it would go into the database table of:
id
prodid
producttag.

Then click on the Tag link, and it would go to product-tags/$producttag.

Works fine. But now we need to do it for sub categories, as most tags in a subcategory will be the same.
So if all jeans in the Sub Cat are Dark denim, Brand A, inc. Belt, then we can assign "Belt, ", "Dark Denim", "Brand A", as the Sub Cat Tags.

Fine. But we want to show both the Sub Category Tagged products, and the Product Tag products in one page.

So if individual products have been assigned Dark Denim, then we want to show both Subs and Products on the page.

That way, on the homepage all Tags from the Subs and Products will be in a block, and they can click any of them.

I did think of having two lots of Inner Join queries. One for Subs and one for Products, but the issue comes with the Page Numbering, as that can only be for one type of query.

So as of now, we are using this for Products... can it be altered to join with Sub categories as well??

Code: Select all

$query = ("SELECT t.prodid, t.producttag, p.datebackinstock, p.datepricedrop, p.id, p.catid, p.subid, p.catname, p.subname, p.title, p.price, p.photoprimary, p.comingsoon, p.rcstock, p.preorder, p.bundleroman1, p.bundleroman2, p.bundleroman3, p.bundleroman4, p.bundleroman5, p.pricedrop  FROM products AS p INNER JOIN producttags AS t ON p.id = t.prodid WHERE t.producttag = :producttag AND pause = 'off' ORDER BY rcstock = 'in stock' DESC,  rcstock = '' DESC, comingsoon = 'yes', rcstock = 'out of stock',  $order LIMIT $offset, $rowsPerPage");
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Multiple Table Query - (product tags)

Post by simonmlewis »

Update:
Have tried this, but it's producing nothing. Yet I know at least one subid uses this tag title.

Code: Select all

SELECT s.subid, s.tag, t.prodid, t.producttag, p.datebackinstock, p.datepricedrop, p.id, p.catid, p.subid, p.catname, p.subname, p.title, p.price, p.photoprimary, p.comingsoon, p.rcstock, p.preorder, p.bundleroman1, p.bundleroman2, p.bundleroman3, p.bundleroman4, p.bundleroman5, p.pricedrop  FROM products AS p 

INNER JOIN producttags AS t ON p.id = t.prodid
INNER JOIN subcategorytags AS s ON p.subid = s.subid 

WHERE t.producttag = 'shirt' OR s.tag = 'shirt'
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Multiple Table Query - (product tags)

Post by Celauran »

You're performing inner joins on two tables, so the only results returned will be ones where records exist in all three tables, which doesn't look like it's going to happen. Probably better to break it into two queries and then join the results.
Post Reply