Page 2 of 2

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 9:15 am
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?!

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 9:32 am
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?

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 9:58 am
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...?

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:07 am
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.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:19 am
by simonmlewis
Sorry I don't see it............?! :(

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:21 am
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

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:24 am
by simonmlewis
AS d... ?? Sorry are you basically wrapping it in on itself.
I don't understand.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:27 am
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.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:29 am
by simonmlewis
So what's the rest of that queries, as u cut it off at "As d", which I don't have?!

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:33 am
by Celauran
There is no rest of anything. That's the entire query.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:36 am
by simonmlewis
Brilliant. Sorry just looked odd with the As d on the end.
That was a toughy!

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:38 am
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.

Re: PHP Page Number failure - one extra erroneous page

Posted: Fri Jul 01, 2016 10:46 am
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. :)