tijs.org

Now even those who insist on using stored procedures and other such ‘enterprise grade’ technology no longer have an excuse to use Oracle: MySQL 5.0 Downloads. I must say i tend towards the school of thought which thinks that a well designed application is best served by staying away from such database ’shortcuts’.

update: David has a much more nicely worded explanation of my viewpoint. And the discussion it raises is rather interesting too if you like that sort of thing.

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.