Cleanup Those MySQL Results!

You’re off to querying your database using MySQL. So far you’ve pulled 5 queries that are hanging out waiting to be used. Is this a big deal? Not really. Each time you load up your MySQL cache with some database information you only pull a small section of your web server’s memory. Let’s face it, your web server is quite busy. You are running your MySQL, PHP, Apache (we hope), email processes, SSL sockets, FTP servers and much more. If you were to take a moment and add a line to the bottom of each of your queries you could potentially save yourself some major headache wondering why your MySQL is running like the junk. Here’s how to do it.

$sql_result = mysql_query("SELECT s1, s2 FROM table WHERE this=that") or die (mysql_error());

while ( list($s1, $s2) = mysql_fetch_array($sql) )
{
[tab]echo "Your $s1 and $s2 here";
}

Now, we would add this nice little line to the bottom of this query:

mysql_free_result($sql_result);

Note: this function has no effect when using mysql_pconnect(); And you have just cleaned up the MySQL query from your server’s memory. Simple but effective! If you are using a class to handle MySQL query, and grab the result, you can simply call it when your function is done with the process. I have a small class called Table, its job is to handle all MySQL queries dynamically, and there is a function called get_rows(), what I did was to call the mysql_free_result function at the end of this function when my return array is built, the code as follows:

function get_rows(//parameters)
{
[tab]// *
[tab]// Code to build query
[tab]// *

[tab]$result = $this->db->execute_query($this->sql);

[tab]// *
[tab]// Error Checking
[tab]// *

[tab]$values = array();
[tab]while ($row = $this->db->fetch_assoc())
[tab]{
[tab][tab]$values[] = $row;
[tab]}

[tab]mysql_free_result($result);

[tab]return $values;
}