Resolved When to free sql results?

[rgEffects]

For a lot of my web designs I'll run a bunch of queries on the same page. I usually run the queries before any HTML and retrieve the $row_someRS['someField'] inside the HTML to echo information on the web page. After the end of the HTML I will run <?php
mysql_free_result($someRS); ?> to clean things up.

Lately I've been also embedding code like this to populate menu's and run forms:

PHP Code:

      <?php
            $parentid = 0; // assuming that 0 is the main category.
            get_sub_cats2($parentid);
            function get_sub_cats2($parentid) {
            
            $sql = "SELECT * FROM endeavors WHERE eID = ".$parentid.""; 
            $run = mysql_query($sql);
            
            echo '<ul class="aqtree3clickable">';
                while ($rec = mysql_fetch_assoc($run)) { 
                echo '<li />',
                '<input name="cBox2[]" type="checkbox" id="cBox2[]" value="', $rec['id'],'"><a href=""></aa href=""></a>SN ',
                 $rec['id'], ' - ',  $rec['eName'], ' / ', $rec['EBS'] ; 
                get_sub_cats2($rec['id']);
                }
            echo '</ul>';
            }
        ?>

Here's the question: Should I be running mysql_free_result($run); at the end of this loop or adding it to the rest of the free result methods after the HTML?

Thanks for your input.

[Fou-Lu]

You can run them as soon as you are done with them. PHP *should* release the resource at the script termination (should being the keyword) if you don't issue a shutdown. The purpose is to free up memory that you no longer require to be held, so if you use large datasets, free them as soon as you can.

MySQLi with prepared statements are different. You should close the statement as soon as you are finished with them; depending on the usage they can block other commands until they are free. Since you need to move to MySQLi or PDO soon anyways, you may as well get used to issuing free now.

[rgEffects]

So if I'm following your post correctly right after the last closing bracket in the sample code I should add the free command. It would look like this:

PHP Code:

<?php 
            $parentid = 0; // assuming that 0 is the main category. 
            get_sub_cats2($parentid); 
            function get_sub_cats2($parentid) { 
             
            $sql = "SELECT * FROM endeavors WHERE eID = ".$parentid."";  
            $run = mysql_query($sql); 
             
            echo '<ul class="aqtree3clickable">'; 
                while ($rec = mysql_fetch_assoc($run)) {  
                echo '<li />', 
                '<input name="cBox2[]" type="checkbox" id="cBox2[]" value="', $rec['id'],'"><a href=""></aa href=""></a>SN ', 
                 $rec['id'], ' - ',  $rec['eName'], ' / ', $rec['EBS'] ;  
                get_sub_cats2($rec['id']); 
                } 
            echo '</ul>'; 
            }
     mysql_free_result($run);
        ?>

I'm wondering if I should use $run as the target because many queries on my page use$run as a variable, or would it be better to write the first lines of the query like this:

PHP Code:

mysql_select_db($database_civTekDB, $civTekDB);
$query_companyMenuRS = "SELECT * FROM endeavors WHERE eID = ".$parentid."";
$companyMenuRS = mysql_query($query_companyMenuRS) ..... 


and then write use mysql_free_result($companyRS) as the action line. IOW, should I use specific variable names for each query?

[Fou-Lu]

No the free result call would go after the loop. You have it after the function, so you've already lost the stack variable used within it (and best I can tell it would then wait for the script shutdown to release).
$run has no conflict here. That is within a function so $run within the scope of get_sub_cats2 has no relationship to $run declared in main or any other function.

[rgEffects]

Ok, so release would go here:

PHP Code:

<?php 
            $parentid = 0; // assuming that 0 is the main category. 
            get_sub_cats2($parentid); 
            function get_sub_cats2($parentid) { 
             
            $sql = "SELECT * FROM endeavors WHERE eID = ".$parentid."";  
            $run = mysql_query($sql); 
             
            echo '<ul class="aqtree3clickable">'; 
                while ($rec = mysql_fetch_assoc($run)) {  
                echo '<li />', 
                '<input name="cBox2[]" type="checkbox" id="cBox2[]" value="', $rec['id'],'"><a href=""></aa href=""></a>SN ', 
                 $rec['id'], ' - ',  $rec['eName'], ' / ', $rec['EBS'] ;  
                get_sub_cats2($rec['id']); 
                } 
                    mysql_free_result($run); // end of loop??
            echo '</ul>'; 
            }
        ?>

[Fou-Lu]

Yep that's correct.