Menu
I'm doing a pagination feature using Codeigniter but I think this applies to PHP/mySQL coding in general.
I am retrieving directory listings using offset and limit depending on how many results I want per page. However to know the total number of pages required, I need to know (total number of results)/(limit). Right now I am thinking of running the SQL query a second time then count the number of rows required but without using LIMIT. But I think this seems to be a waste of computational resources.
Are there any better ways? Thanks!
EDIT: My SQL query uses WHERE as well to select all rows with a particular 'category_id'
243
Since MYSQL 4.0 we can use SQL_CALC_FOUND_ROWS option in query which will tell MySQL to count total number of rows disregarding LIMIT clause. In main query add SQL_CALC_FOUND_ROWS option just after SELECT and in second query use FOUND_ROWS() function to get total number of rows without executing the query.
Counting all of the Rows in a Table. To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
MySQL Offset is used to specify from which row we want the data to retrieve. To be precise, specify which row to start retrieving from. Offset is used along with the LIMIT. Here, LIMIT is nothing but to restrict the number of rows from the output.
The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
Take a look at SQL_CALC_FOUND_ROWS
56
SELECT COUNT(*) FROM table_name WHERE column = 'value' will return the total number of records in a table matching that condition very quickly.
Database SELECT operations are usually "cheap" (resource-wise), so don't feel too bad about using them in a reasonable manner.
EDIT: Added WHERE after the OP mentioned that they need that feature.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
