Click here to Skip to main content
15,124,187 members
Please Sign up or sign in to vote.
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
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

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[^]
   
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 :-)
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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900