Paginating query

[Shaolin]

Hi Guys

I am working on a pagination class for my website. I want to display the amount of records viewable by the user and the current amount of recoords being viewed (e.g. Mail 50 of 132). Note that the user can view a max of 50 records per page. I know how to workout the total amount of records viewable by using the following query:

SELECT COUNT(mail_id) FROM mail WHERE user_id='1';

My problem is how I should workout the current amount of records being viewed. Here is the query I have at the moment:

SELECT * FROM mail WHERE user_id='1' AND LIMIT 100, 150;
My question is how can I count ALL the records starting from 0 upto the max LIMIT ? So in the example above the max LIMIT is set to 150 but the real amount of records viewable is 132. I hope that makes sense.

[chazzy]

what DBMS are you using?

[Shaolin]

mysql

[chazzy]

use the sql function FOUND_ROWS(). If you're using PHP (which it seems like everyone on here is; but do correct me if i'm wrong) use the function mysql_num_rows() right after the query.

[Shaolin]

What if your using Java ?

[chazzy]

What if your using Java ?

if you're using plain JDBC then just use the function I gave you. if you're using a persistence framework (ie hibernate), the best way is to create 2 queries, one for the columns and another for the count. the columns would have the limit applied and the count not. it should also be noted that this function essentially wraps the functionality i just described.