PHP Page Number failure - one extra erroneous page
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
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?!
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.
All the best from the United Kingdom.
Re: PHP Page Number failure - one extra erroneous page
So
returns 18 rows
and
returns 21.
That's unexpected. Have you looked at the actual results?
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 categoryand
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 categoryThat'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
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...?
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.
All the best from the United Kingdom.
Re: PHP Page Number failure - one extra erroneous page
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
Sorry I don't see it............?! 
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: PHP Page Number failure - one extra erroneous page
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
AS d... ?? Sorry are you basically wrapping it in on itself.
I don't understand.
I don't understand.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: PHP Page Number failure - one extra erroneous page
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
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.
All the best from the United Kingdom.
Re: PHP Page Number failure - one extra erroneous page
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
Brilliant. Sorry just looked odd with the As d on the end.
That was a toughy!
That was a toughy!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: PHP Page Number failure - one extra erroneous page
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
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.
All the best from the United Kingdom.