GROUP BY not working because of Page Numbering needs.

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:

GROUP BY not working because of Page Numbering needs.

Post 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>";
   }
}
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: GROUP BY not working because of Page Numbering needs.

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
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: GROUP BY not working because of Page Numbering needs.

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
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: GROUP BY not working because of Page Numbering needs.

Post 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";
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

You'll also want to filter rows where p.id is null since you're doing left joins.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

p.id is never NULL, as it's the ID of any products it finds.
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: GROUP BY not working because of Page Numbering needs.

Post 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.
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: GROUP BY not working because of Page Numbering needs.

Post by Celauran »

I don't see the GROUP BY statement in there.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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?
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: GROUP BY not working because of Page Numbering needs.

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

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