I'm surprised I have not come across this earlier.
Often when writing scripts for paging between sets of results or lists of products I have processed two queries. The first runs the required query with no limit clause and COUNT(*) and the second with a set of column names and a LIMIT clause.
NO MORE!
SQL_CALC_FOUND_ROWS (released in MySQL 4.0.0 onwards) tells MySQL to calculate how many rows would be returned if the limit clause was missing. If you want to get the result you can do Select FOUND_ROWS().
Check out the following code example if you dont believe me!
<?php
//Running the query here. Lets assume the products table has 100+ entries
$result = mysql_query(“SELECT SQL_CALC_FOUND_ROWS * FROM `products` LIMIT 20”);
//Lets get the number of results actually returned (20)
$iRows = mysql_num_rows($result);
$totalResult = mysql_query(“Select FOUND_ROWS()”);
$totalRow = mysql_fetch_assoc($totalResult);
//The following will return the number of rows that our first query returned if the LIMIT clause has been missing
$iTotalRows = $totalRow[“FOUND_ROWS()”];
?>
//Running the query here. Lets assume the products table has 100+ entries
$result = mysql_query(“SELECT SQL_CALC_FOUND_ROWS * FROM `products` LIMIT 20”);
//Lets get the number of results actually returned (20)
$iRows = mysql_num_rows($result);
$totalResult = mysql_query(“Select FOUND_ROWS()”);
$totalRow = mysql_fetch_assoc($totalResult);
//The following will return the number of rows that our first query returned if the LIMIT clause has been missing
$iTotalRows = $totalRow[“FOUND_ROWS()”];
?>
This was actually introduced to MySQL to help with paging and solve the two query problem.
I know some people still prefer using two queries, but if you didn't know this existed. Then use it as you wish...
Posted by epic at 22:24pm
No comments yet. Be the first to add one!
Leave a Reply
