for this second blog article I've decided to explain this neat little feature of MySQL: SQL_CALC_FOUND_ROWS and FOUND_ROWS().
This article is about MySQL only, it is likely that these keywords/functions exist in other SQL-based languages but I've only ever used them with MySQL.

What's the point, you ask?
When working with (my)SQL databases, you often find yourself using the LIMIT keyword, in order for example to limit the results of a large search. Let's study a particular case.
- You're working on the "employees" table, which contains, say 1000 data rows.
- You wish to display the list of employees in a particular department, 10 by 10.
- You wish to display the amount of employees in this department.

What would you normally do?
Here's a rough example:
$result = mysql_query("SELECT * FROM employees WHERE department='sales' LIMIT 0,10 ");
$total = mysql_num_rows($result);
-> This is incorrect because $total will never exceed 10 due to the LIMIT clause.

You could also do this:
$result = mysql_query("SELECT COUNT(*) cnt FROM employees WHERE department='sales'");
$count = mysql_result($result, 0, "cnt");
$result = mysql_query("SELECT * FROM employees WHERE department='sales' LIMIT 0,10 ");
-> But this means you have to process two SQL queries.

So how do you fix this?
Using the SQL_CALC_FOUND_ROWS keyword in your SQL query will allow you to fetch the total amount of results without being limited by the LIMIT clause.
Here's how you use it:

$data_result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE department='sales' LIMIT 0,10");
$count_result = mysql_query("SELECT FOUND_ROWS() cnt");
$employees_count = mysql_result($count_result, 0, "cnt");
echo "$employees_count employees found in the sales dept. Showing first 10: ";

As you can see, using the SQL_CALC_FOUND_ROWS keyword allows you to save the amount of results before applying the LIMIT clause; and this amount can be retrieved with a simple "SELECT FOUND_ROWS()" after the query is executed.

I've found this to be pretty useful when designing search pages, directories, and so on...
If you want more information about the FOUND_ROWS() function, and other useful functions aswell, check this page on the official MySQL documentation.

Okay, that's all folks!


timdebrie said...

Great explanation !

After hours of searching for information how to use this FOUND_ROWS() thing, I finally found your page.
And with this clear and complete example I finally got it working in no time.

Thanks a lot !

BobM said...

A somewhat old post, but mat be worth a read: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

do friv jogos said...

Great post. i like it. feeling great when reading your post . juegos.com | juegos de matar | jogos de friv

unblocked games said...

Guidelines or that, I would try play unblocked games very nice , free unblocked games online to play , descargar whatsapp gratis chat limit , unblocked games , unblocked games , descargar whatsapp gratis

descargar whatsapp gratis said...

story with the young man who receives Geneva that he and you love each other
descargar whatsapp fast , use app baixar whatsapp very comfortable , play mobogenie games hot descargar whatsapp gratis , baixar whatsapp gratis , descargar mobogenie gratis

h1z1 maps said...

yet available but most sparkling, she bought one female h1z1 download , grand theft auto 5 cheats , city skylines , h1z1 maps , gta 5 cheats ps4 , cities skylines mods

cool math 4 kids said...

I did it, thank you very much girl games , coolmathgames , friv 4 , friv online , girlsgogames , coolmath4kids , jogos de friv , jogos do friv

kizi said...

Error has been remedied very good juegos kizi , juegos de los simpson 2 , kids games online , kizi games , juegos de los simpson , games for kids online

Search This Blog