Getting total results of a query
There is a really simple way to calculate the total results a MySQL SQL query with minimal overhead. This saves an extra call to the database in which you calculate the total yourself. Just use SQL_CALC_FOUND_ROWS in your query like this:
$sql = "select SQL_CALC_FOUND_ROWS a.id, a.title,
from articles a
left join sections s on a.section_id = s.id
where t.name_short = '$type'";
// run the query (i use a db object in this example)
$db->query($sql);
As you can see you can use this in any type of select query. And then you can request the total like this:
// ask MySQL for the result total
$db->query("select found_rows() as total");
// assign to variable
$total = $db->get('total');
You still call the database a second time but the variable is already in memory so there is no overhead.
p.s.
If you come here more often you are probably not used to seeing code here but i in the future i want to try and share some more solutions to common coding problems on this blog. Mostly PHP and SQL, hopefully some more Ruby/Rails in the near future.
November 24th, 2005 at 07:49
What language is that? It doesn’t look like PHP nor Perl, but in PHP you can use the function mysql_num_rows() to get the total rows returned from a SELECT statement.
November 24th, 2005 at 09:45
Indeed, using mysql_num_rows() is the standard way to do it but uses another call to the database to retrieve that result. This method is more optimized. Oh and the example uses PHP allright but it talks to MySQL through a database object instead of directly. I must admit that’s not very obvious from the short example.