PHP Page Number failure - one extra erroneous page

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

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

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

Sorry... 18 at the top, 21 at the bottom.
Yes.. that means the second one is finding more... but how if the script is the same?!
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: PHP Page Number failure - one extra erroneous page

Post by Celauran »

So

Code: Select all

SELECT p.id AS id, catid, category, c.priority AS priority
FROM products AS p
INNER JOIN categories AS c ON p.catid = c.id
WHERE pause <> 'on' AND producttype <> 'ancilliaries'
GROUP BY category
ORDER BY category
returns 18 rows

and

Code: Select all

SELECT COUNT(p.id) AS numrows, catid, category, c.priority AS priority
FROM products AS p
INNER JOIN categories AS c ON p.catid = c.id
WHERE pause <> 'on' AND producttype <> 'ancilliaries'
GROUP BY category
returns 21.

That's unexpected. Have you looked at the actual results?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

Something peculiar.

If I run both queries in phpmyadmin, they both come back with 18 results.
When I run the second query, it shows me each category in question and the "numrows"... of those, the first one comes out at 21. That's strange. It is somehow picking out that to use for the page numbering by mistake...?
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: PHP Page Number failure - one extra erroneous page

Post by Celauran »

Oh, of course! Can't believe I missed that. COUNT is going to give you the count per category. Wrapping the whole thing in a count will give you the result you're looking for.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

Sorry I don't see it............?! :(
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: PHP Page Number failure - one extra erroneous page

Post by Celauran »

Code: Select all

SELECT COUNT(*)
FROM (
    SELECT COUNT(p.id) AS numrows, catid, category, c.priority AS priority
    FROM products AS p
    INNER JOIN categories AS c ON p.catid = c.id
    WHERE pause <> 'on' AND producttype <> 'ancilliaries'
    GROUP BY category
) AS d
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

AS d... ?? Sorry are you basically wrapping it in on itself.
I don't understand.
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: PHP Page Number failure - one extra erroneous page

Post by Celauran »

Each row in the inner query is giving you the number of products in that category because of the COUNT and GROUP BY. The number you want is the number of rows returned, which the outer COUNT provides. I'm sure there are more efficient ways of computing it, but let's start here and ensure it gives the expected result.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

So what's the rest of that queries, as u cut it off at "As d", which I don't have?!
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: PHP Page Number failure - one extra erroneous page

Post by Celauran »

There is no rest of anything. That's the entire query.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

Brilliant. Sorry just looked odd with the As d on the end.
That was a toughy!
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: PHP Page Number failure - one extra erroneous page

Post by Celauran »

Does look a bit odd. Derived tables need aliases, though. Hopefully that fixes your pagination issues, but I'd spend some time thinking about a better query to sub in there.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP Page Number failure - one extra erroneous page

Post by simonmlewis »

Ok. Well it works a treat. Without that, the page is very long and lots of 200-300kb images. so this will help that. :)
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply