Cant get total amount of records when trying to create pagination

Question

0.00/5 (No votes)

See more:

Hi,

Im trying to set up some pagination. Currently I have the actual pagination working but I cannot get the total amount of records returned (to create the amount of pages needed to contain all the records). I am getting an error in the SQL:

'Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 no such function: SQL_CALC_FOUND_ROWS'

PHP

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = isset($_GET['per-page']) && $_GET['per-page'] <= 20 ? (int)$_GET['per-page'] : 5;
				
$start = ($page > 1) ? ($page * $perPage) - $perPage : 0;
				
$replies = $db->prepare("
SELECT SQL_CALC_FOUND_ROWS (*) FROM replies JOIN profile ON replies.replyBy = profile.username WHERE replyThreadID=:replyThreadID LIMIT {$start}, {$perPage}");
$replies->bindParam(':replyThreadID', $ID);
$replies->execute();
$replies = $replies->fetchAll(PDO::FETCH_ASSOC);
				
$total = $db->query("SELECT FOUND_ROWS() as total");
echo $total;

I followed a tutorial and it should work.

Posted 7-Apr-15 4:37am

jba1991

Add a Solution

Comments

jba1991 7-Apr-15 17:31pm

anyone?

ramyajaya 7-Apr-15 20:05pm

Check if your query is manually running properly in your mysql db

2 solutions

Solution 2

Your going about the pagination in the wrong manner - a dependency upon foreknowledge of the incoming text.

A better strategy is to take into account the font size (more specifically, the line height) and then, as you print each line, you keep a running tally of the page location relative to the top. When you're close enough to the bottom (and have more text) then you start new page.

This also allows you to change font size around the page: just keep that reference updated.

Note: this is based upon your ability to 'page' based upon line count. Paging with contiguous (wrapping) text requires additional strategies.

Posted 8-Apr-15 1:37am

W∴ Balboos, GHB

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Accepted Answer · 2015-04-07T18:40:00

Solution 1

SQL_CALC_FOUND_ROWS is an option for the query-execution, it's not a function, so the braces around the asterisk (*) are causing the problem.

But it might be better to achieve this with two queries anyway; one for the actual records you want to display and one to get the total amount of records, please refer to:
http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count[^]

Posted 7-Apr-15 18:40pm

Sascha Lefèvre

Comments

jba1991 8-Apr-15 8:03am

Thanks for the info and the link, it seems that using 2 queries to get the count is a prefered way by most.

For future reference though, I took the braces off and I am now getting this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "FROM": syntax error'

Sascha Lefèvre 8-Apr-15 8:12am

Hm.. curious.. thank you for the notification, I'll try to check that.

jba1991 9-Apr-15 7:42am

Did you find anything?

Sascha Lefèvre 9-Apr-15 8:07am

Didn't get to it yet, sorry. Will try later today.

jba1991 10-Apr-15 8:42am

Did you find out what the error was?

jba1991 14-Apr-15 7:05am

Hi. I still cant get this working, Ive tried everything! Did you have a look at the error I was getting?

Sascha Lefèvre 14-Apr-15 8:04am

Oh, I'm sorry, I thought you got it to work with the two-queries-approach and the reason why you kept asking would just be out of interest how to get that SQL_CALC_FOUND_ROWS-thing to work instead.

Did you give the two-queries-approach a try? Please do if you haven't already and in case it's not working, post your code.

jba1991 14-Apr-15 8:19am

This is my pagination code so far:

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;

$perPage = isset($_GET['per-page']) && $_GET['per-page'] <= 20 ? (int)$_GET['per-page'] : 5;

$start = ($page > 1) ? ($page * $perPage) - $perPage : 0;

$replies = $db->prepare("
SELECT SQL_CALC_FOUND_ROWS * FROM replies
WHERE replyThreadID=:replyThreadID LIMIT {$start}, {$perPage}");
$replies->bindParam(':replyThreadID', $ID);
$replies->execute();
$replies = $replies->fetchAll(PDO::FETCH_ASSOC);

$totalQuery = $db->query("SELECT COUNT(*) FROM replies WHERE replyThreadID=:threadID");
$totalQuery->bindParam(':threadID', $ID);
$total = $totalQuery->execute();
echo $total;

And this is the error I get:

PDOException: SQLSTATE[HY000]: General error: 1 near "FROM": syntax error

The error is in the $replies SQL statement

Sascha Lefèvre 14-Apr-15 8:44am

You have to completely remove SQL_CALC_FOUND_ROWS from the query. My original answer, stating that you should remove (only) the braces, was an attempt to get the single-query-approach to work but for the two-query-approach it's not needed at all, that's what the second query is for.

Sascha Lefèvre 14-Apr-15 8:46am

Also, $total won't contain your query result but a true/false, indicating whether the query was successful executed. You should know from our other conversations how to get the actual query result value ;-)

jba1991 14-Apr-15 12:12pm

I got it! :). thanks, I think I need to do a bit of research on how to do a count properly cause its caused me so much stress!

Thank again Sascha, youve been great.

Sascha Lefèvre 14-Apr-15 12:17pm

You're welcome! Glad I could help :-)

Cant get total amount of records when trying to create pagination

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Cant get total amount of records when trying to create pagination

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us