Pagination of query results in mysql

[eluser]agdelacruz02[/eluser]
Hi! I have a species table with fields 'id','species_name' and 'genus'. I need to view a list of all the species in the database with pagination. Can you suggest a link that could help me with this problem. Tnx.

[eluser]gRoberts[/eluser]
Using a combination of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is always my choice of how to do things.

http://dev.mysql.com/doc/refman/4.1/en/i...tions.html

i.e. (pseudo code)

Code:

$Species = $this->db->query('select SQL_CALC_FOUND_ROWS id, species_name, gunus from Species order by species_name asc limit 0, 10', false);
$TotalCount = $this->db->query('select ROWS_FOUND() as Count', false);

`$Species` contains your first 10 species and `$TotalCount` contains the actual amount of rows (as if you hadn't used limit)

From there, you can use math and for statements to create a list of buttons for each of your pages.

HTH