Out 02 2008

Count & Display The Total Number of MySQL Queries

Categoria: PHP — =IceBurn= @ 18:42

It’s quite often to see at the end of some boards like SMF for example, the total number of the total queries made to the MySQL database.

I understand that some people believe that those queries are retrieved by an extra MySQL query, but the reality is that there is no query for that, so we have to do some extra PHP code.

SMF footer showing the total mysql queries executed

Ok, you might be thinking that a good way to do is is to count one value and store it in a variable where you add + 1 each time you execute a query.

That’s definitely NOT a “friendly” way to do it. The resolution for this has an answer, that the PHP’s static and global keywords.

So, all you need to do is to instead of using mysql_query function, create your own function.

function db_query($sql) {
 // Declare as static variable before declare global
 static $total_mysql_queries;
 // Declare as global variable
 global $total_mysql_queries;
 
 if(mysql_query($sql)) { // Query successfully executed?
 
  // Add +1 to variable $total_mysql_queries
  ++$total_mysql_queries;
 
  // Return true, the query was successfully executed
  return true; 
 
 }
// I'm sure there was a mysql error...
return false;
}

Include the db_query() function into your code, replacing all instances of mysql_query and perform your MySQL queries.

db_query('SELECT `user` FROM `users` WHERE `id` = 1 ORDER BY `id` LIMIT 1;') or die(mysql_error()); 
 
db_query('SELECT `user` FROM `users` WHERE `id` = 2 ORDER BY `id` LIMIT 1;') or die(mysql_error()); 
 
db_query('SELECT `user` FROM `users` WHERE `id` = 3 ORDER BY `id` LIMIT 1;') or die(mysql_error());

Than, at the end of your code, add:

echo 'A total of ', isset($total_mysql_queries) ? $total_mysql_queries : 0, ' MySQL queries were executed.';

In my case, the above example would print A total of 3 MySQL queries were executed.

If you already have your own function or class to perform database queries, even better, you’ll only need to update it with the global/static variable “$total_mysql_queries” and place the increment.

Happy MySQL Query counting!! :)

Certamente também irá gostar destes artigos
Gostou deste artigo? Partilhe |

RSS 2.0 Não perca os meus artigos! Subscreva a minha feed RSS.

Etiquetas: , , , , , , , , , , ,

3 Responses to “Count & Display The Total Number of MySQL Queries”

  1. Ian says:
    Outubro 27th, 2008 at 17:19

    Hi, i attempted to implement this however the query could not be executed.
    Here is what i used…
    $permission = “SELECT * from mpanel_permission where IP=’$ip’ AND status=’1′ LIMIT 1″;
    $permissioncheck = db_query($permission);
    With database connection and the above code above it, but it doesn’t work. Is there something wrong or i can’t use $permission inside db_query?

  2. Ian says:
    Outubro 27th, 2008 at 17:50

    Found the problem.
    I cant use anything like…
    $testing = db_query(“SELECT * FROM accounts WHERE status = ‘0′”) or die(mysql_error());
    $rows = mysql_num_rows(testing);
    echo $rows;
    apparently, thanks anyways :S

  3. =IceBurn= says:
    Outubro 27th, 2008 at 18:01

    Hi Ian!

    You probably have an error in your SQL statemen, please add mysql_error(), like this:

    <?php
 
$permission = "SELECT * FROM mpanel_permission WHERE `IP` = '".$ip."' AND `status` = '1' LIMIT 1";
 
$permissioncheck = db_query($permission) or die(mysql_error());
 
echo 'A total of ', isset($total_mysql_queries) ? $total_mysql_queries : 0, ' MySQL queries were executed.';
 
?>

    EDIT: I’ve replay before read your second commentary. Anyway I’m glad you’ve sorted it out. Cheers!

Comentar Este Artigo