Retunring total number of records from query

[Thierry888]

I am a newbie on forums, please forgive me any mistakes...

I'm trying to page through the data of a query.
I already know to first use ORDER BY to order the returned records.
I found how to use LIMIT x,y to get a specific part of the total query in the result set.
So far, so good...

And here it comes : how do I get the total number of records returned ? I mean by this the number of records the query would have returned without the LIMIT in place.

Base line is that I want to display a result like :

Records 1-10 from 5346

on the page, the only problem is getting the 5346 number out of the query...

Can anyone help ?

Greetz,

Thierry.

 

[PruSQLer]

You can get that with a subquery in the Select portion. You'll get it for every record but since it's only 10...

select Field1, Field2, (Select count(*) from TableA)
From TableA

 

[r937]

see the FOUND_ROWS() function in 13.8.3 Information Functions


rudy

http://r937.com/

SQL Consulting

 

[Thierry888]

Thanx for the answers guys !

First answer : count(*) does not give me the right result, since the query is much more complex with a lot of where clauses. If you mix count(*) with normal fields on the select line, you are obliged to use the GROUP BY which act as an input for count(*). I tried this and it messes up my query or gives the wrong count...

Second answer : That is really what I was looking for !!!
I tried it out and it works fine. The only thing I have to change is adding SQL_CALC_FOUND_ROWS in the select line.
This makes sure that the next time select FOUND_ROWS() is called, the right result is given, regardless if LIMIT was used in the first query or not.

Thanx for the answers, they really helped me out !