I have searched everywhere but can't find a simple example of paging in MySQL that also returns the total number of rows for that certain query. In SQL Server there are numerous ways to do this, but no examples with MySQL. Does anyone have this kind of Stored Procedure that takes a page, page size, sort by, and sort direction parameters and returns the amount of rows for that page ordered, and with the total results in that query.
Have you checked out SQL_CALC_FOUND_ROWS and FOUND_ROWS()?
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
