Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    When to free sql results?

    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.
    Last edited by rgEffects; 01-25-2013 at 02:14 AM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    rgEffects (01-24-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    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?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    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>'
                }
            
    ?>

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Yep that's correct.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    rgEffects (01-25-2013)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •