Page 1 of 1

Multiple Table Query - (product tags)

Posted: Thu Nov 05, 2015 3:46 am
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");

Re: Multiple Table Query - (product tags)

Posted: Thu Nov 05, 2015 4:17 am
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'

Re: Multiple Table Query - (product tags)

Posted: Thu Nov 05, 2015 6:36 am
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.