Page 1 of 1

GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 3:46 am
by simonmlewis
Bit of a problem with GROUP BY.

So the query at the top finds all the relevant products, and then GROUPs then by ID to avoid duplicates (as there will be because it's looking for the TAGS in two database tables. However.....

Code: Select all

$query = ("SELECT 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 

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

WHERE t.producttag =:producttag OR s.tag =:producttag AND p.pause = 'off' GROUP BY p.id ORDER BY rcstock = 'in stock' DESC,  rcstock = '' DESC, comingsoon = 'yes', rcstock = 'out of stock',  $order  LIMIT $offset, $rowsPerPage");
... the page numbering at the bottom cannot be GROUPed because if I do, it just kills the page numbering.
So how do I do the page numbering at the bottom using this query, effectively?

Code: Select all

$query   = "SELECT COUNT(p.id) AS numrows FROM products AS p 
LEFT JOIN producttags AS t ON p.id = t.prodid
LEFT JOIN subcategorytags AS s ON p.subid = s.subid 
WHERE (t.producttag =:producttag OR s.tag =:producttag) AND p.pause = 'off'";

	$result  = $pdo->prepare($query);
  $result->execute(array(':producttag' => $producttag));
  $numrows = $result->fetchColumn();
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pagenum)
   {
      $nav .= " <div class='pagelinkactive'>$page</div> "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"/product-tags/page/$page/$producttagDash/\" class='pagelink'>$page</a>";
   }
}

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:08 am
by Celauran
Instead of getting a single row back as you'd expect, you're getting one row per ID because of the grouping. What if you just counted the number of rows you got back?

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:17 am
by simonmlewis
Do you mean from the first query?
Our category page work like this, but it's a problem because I have to group it it to avoid duplicate IDs showing.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:20 am
by Celauran
No, I mean your second query. You're saying it won't work because of the grouping. I'm saying instead of relying on the value of COUNT(id), which will be grouped by row, leave the grouping in the query and count the number of rows returned. Your second query should very closely resemble your first, just without the limit and offset, and you'd only need to select the ID.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:22 am
by simonmlewis
Like this instead you mean?

Code: Select all

$query   = "SELECT p.id AS numrows FROM products AS p 
LEFT JOIN producttags AS t ON p.id = t.prodid
LEFT JOIN subcategorytags AS s ON p.subid = s.subid 
WHERE (t.producttag =:producttag OR s.tag =:producttag) AND p.pause = 'off'";
	$result  = $pdo->prepare($query);
  $result->execute(array(':producttag' => $producttag));
  $numrows = $result->rowCount();
$maxPage = ceil($numrows/$rowsPerPage);

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:23 am
by Celauran

Code: Select all

$query = "SELECT p.id
    FROM products AS p
    LEFT JOIN producttags AS t ON p.id = t.prodid
    LEFT JOIN subcategorytags AS s ON p.subid = s.subid
    WHERE t.producttag =:producttag 
    OR s.tag =:producttag 
    AND p.pause = 'off' 
    GROUP BY p.id";

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:25 am
by Celauran
Yeah, pretty much. You'll want to leave the grouping in there to avoid having duplicate rows returned, and you don't need COUNT since you'll be using the number of rows returned

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:26 am
by Celauran
You'll also want to filter rows where p.id is null since you're doing left joins.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:48 am
by simonmlewis
p.id is never NULL, as it's the ID of any products it finds.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 6:51 am
by simonmlewis

Code: Select all

$query   = "SELECT p.id AS numrows FROM products AS p 
LEFT JOIN producttags AS t ON p.id = t.prodid
LEFT JOIN subcategorytags AS s ON p.subid = s.subid 
WHERE (t.producttag =:producttag OR s.tag =:producttag) AND p.pause = 'off' AND p.id IS NOT NULL";
	$result  = $pdo->prepare($query);
  $result->execute(array(':producttag' => $producttag));
  $numrows = $result->rowCount();
$maxPage = ceil($numrows/$rowsPerPage);
This is still pulling more "pages" than needed.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 7:17 am
by Celauran
I don't see the GROUP BY statement in there.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 7:27 am
by simonmlewis
Yes I think that has got it.
I Take it the problem in using my original method over this other, is because I need the p.id as part of the query...
Or can I just use the other method anyway?

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 7:46 am
by Celauran
simonmlewis wrote:Yes I think that has got it.
I Take it the problem in using my original method over this other, is because I need the p.id as part of the query...
Or can I just use the other method anyway?
The problem with your initial code is that you weren't using GROUP BY, so you were getting a completely different result set. Beyond that you just need to keep in mind that aggregate functions like GROUP are going to return multiple rows for COUNT rather than just one.

Re: GROUP BY not working because of Page Numbering needs.

Posted: Mon Nov 09, 2015 7:58 am
by Celauran
My best suggestion, really, is to run the two queries in the console or phpMyAdmin (or HeidiSQL, or whatever you use) and compare the results.