Getting total records outside LIMIT

[quiettech]

Hello,

 

I'm currently using LIMIT to reduce the number of returned records to a variable number. However I need to know the total number of records, were LIMIT not being used.

 

For that, I first run the query with COUNT(*) and then rerun the query with LIMIT.

 

Is there a more elegant way?

[aschk]

Yes, you use the SQL_CALC_FOUND_ROWS

e.g.

SELECT SQL_CALC_FOUND_ROWS *
FROM <table name here>
LIMIT 3,21

 

Then you can do

SELECT FOUND_ROWS()

 

This IS 2 statements but eliminates the need for a COUNT(*) in separate statement as the variable has already been set.

[cooldude832]

if u are writing a pagination script you should

1) Query for total count  (150)

2) Query for specific limits  (25-50)

Total-Specific = unlimited count  (125)

[quiettech]

Thanks for the replies. Solved.