Page 1 of 1

loops in loops

Posted: Mon Sep 27, 2010 5:08 pm
by doddsey_65
today i was told that some code I have is bad becuase i am using a loop within a loop. But i dont know how to do it any other way, and they werent helpful in the matter. So how would i do it? Heres the code in question:

Code: Select all

$parent_info_query = $db->query("SELECT
              p.parent_id, p.parent_name

          FROM ".DB_PREFIX."parents as p
          
          ORDER BY p.parent_id")
          
            or trigger_error("SQL", E_USER_ERROR);
                
while ($parent_info = mysql_fetch_object($parent_info_query)) { 
 

 
// Add parent_id into variable for later query
 
 $parent_id = $parent_info->parent_id;

echo '<table class="forum_table" onclick="expandCollapseTable(this)">
<tr id="tr1">
<th class="forum_left_corner"></th>
<th class="forum_parent_name">'.$parent_info->parent_name.'</th>
<th class="empty"></th>
<th class="empty"></th>
<th class="forum_last_post_header">'.LAST_POST.'</th>
</tr>';


$forum_info_query = $db->query("SELECT
              f.forum_id, 
              f.forum_name, 
              f.forum_description,
              f.forum_topics, 
              f.forum_posts,
              f.forum_last_poster, 
              f.forum_last_post_time, 
              f.forum_last_post,
              m.user_id, 
              m.user_username, 
              m.user_group,
              t.topic_id, 
              t.topic_name,
              po.post_id, 
              po.post_subject
              
          FROM ".DB_PREFIX."forums as f

          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
              
          LEFT JOIN ".DB_PREFIX."topics as t
              ON t.forum_id = f.forum_id
              
          LEFT JOIN ".DB_PREFIX."posts as po
              ON po.post_id = f.forum_last_post
              
          WHERE f.parent_id = '$parent_id'
              
          ORDER BY f.forum_id")
          
            or trigger_error("SQL", E_USER_ERROR);
                
while ($forum_info = mysql_fetch_object($forum_info_query)) { 

// Get Forum information from DB to show all forums
// including who the last post was posted by

echo '<tr class="gradient">';

$forum_url_name = $forum_info->forum_name;
$forum_url_name = str_replace(' ', '_', $forum_url_name);

echo '    <td class="forum_icon">
        <div class="thread_icon"></div>
        </td>
        <td class="forum_name">
        <p class="forum_name"><a href="index.php?forum=
        '. $forum_info->forum_id . '
        &name=
        '.$forum_url_name.'
        ">
        ' . $forum_info->forum_name .'
        </a></p>
        <p class="forum_description">
        ' . $forum_info->forum_description .'
        </p></td>
        
        <td class="forum_topics">
        '. $forum_info->forum_topics .'
        <span class="small_word"> '.TOPICS.'</span></td>
        
        <td class="forum_posts">
        '. $forum_info->forum_posts .'
        <span class="small_word"> '.POSTS.'</span></td>
        
        <td class="forum_last_post">';
            
        if (!$forum_info->forum_last_poster) {
        
        echo '<p class="noposts">'.NO_POSTS.'</p>
                <p class="be_the_first">'.BE_FIRST.'</p>';
        
        }
        elseif ($forum_info->forum_last_post == 0) {
        
        echo '<p class="noposts">'.NO_POSTS.'</p>
                <p class="be_the_first">'.BE_FIRST.'</p>';
        
        }

        else {
        
        
        $forum_last_post_clean = str_replace(' ', '_', $forum_info->user_username);
        
        echo '<p class="last_post_name"><a href="index.php?topic='.$forum_info->topic_id.'
        &name='.$forum_last_post_clean.'">'.$forum_info->post_subject.'</a></p>';
        
        echo '<p class="posted_by">Posted By - ';
        
        $today = date('F j, Y');
        $last_post = date("F j, Y", strtotime($forum_info->forum_last_post_time));
        
        if ($last_post == $today) { $last_post = 'Today at '.date("g:i a", strtotime($forum_info->forum_last_post_time)); }
        
        else { $last_post = date("F j, Y - g:i a", strtotime($forum_info->forum_last_post_time)); }
        if ($forum_info->user_group == 1) { echo 
        '<span class="admin">'; }
        
        elseif ($forum_info->user_group == 2) { echo 
        '<span class="mod">'; }
        
        elseif ($forum_info->user_group == 3) { echo 
        '<span class="user">'; }
        
        elseif ($forum_info->user_group == 0) { echo 
        '<span class="user">'; }
        
echo    $forum_info->user_username .'</span></p>

        
        <p class="last_post_date">'
        .$last_post;
        }
echo    '</p></td></tr>';

}
}
As you can see the first query ($parent_info_query) gets the parent name and parent id so i can use the parent id in the next query ($forum_info_query) so i can use WHERE parent_id = $parent_id. This is so it groups the results into the respective categories. Is there a way to do this without a loop in a loop? and why is this bad practice?

Thanks

Re: loops in loops

Posted: Mon Sep 27, 2010 5:33 pm
by DigitalMind
Fetch result of one query into array before executing the next one.
doddsey_65 wrote:and why is this bad practice?
I guess due to buffers you use.
I hope somebody will correct me if I'm wrong ;)

Re: loops in loops

Posted: Mon Sep 27, 2010 10:24 pm
by Jonah Bron
Because you have quite a few queries going on there. You raise performance by lowering queries to a minimum. Without really looking at the code, the tables are probably connected in a many-to-one relationship. My brain's slowing down, but I think you can use JOIN and loop through the inner table.

Re: loops in loops

Posted: Tue Sep 28, 2010 3:17 am
by DigitalMind
Jonah Bron wrote:You raise performance by lowering queries to a minimum
That's not always true. On heavy loaded systems it's better to split some queries to a few smaller queries. At least for MySQL.

Re: loops in loops

Posted: Tue Sep 28, 2010 11:32 am
by Jonah Bron
Can you give a reference for that information? It doesn't make sense to me, because it adds to the query overhead on the database.

Re: loops in loops

Posted: Tue Sep 28, 2010 1:07 pm
by McInfo
(Theory) Attempting to execute a single complex query can cause a spike in memory usage on the database server, whereas multiple simpler queries cause a lower but more sustained memory peak. On a database with thousands of rows, it can make the difference between running out of memory and executing the queries successfully.

Re: loops in loops

Posted: Tue Sep 28, 2010 3:10 pm
by DigitalMind
Jonah Bron wrote:Can you give a reference for that information? It doesn't make sense to me, because it adds to the query overhead on the database.
http://oreilly.com/catalog/9780596101718

Re: loops in loops

Posted: Tue Sep 28, 2010 3:15 pm
by Jonah Bron
@McInfo I guess that makes sense.

@DigitalMind Mind providing a quick excerpt? I am genuinely curious to know the reason for this.

Re: loops in loops

Posted: Tue Sep 28, 2010 3:29 pm
by DigitalMind
Jonah Bron wrote:@DigitalMind Mind providing a quick excerpt? I am genuinely curious to know the reason for this.
for example, you have a table with 1.000.000 rows.
delete * from some_table where some_condition might take too long. at the same time the query locks the table. that's not that good for other users/queries.
in this case you can use a trick. run same query(ies) with limit 10000 in loop. i hope you understood my poor english :)

Re: loops in loops

Posted: Tue Sep 28, 2010 3:39 pm
by Jonah Bron
That's interesting. I didn't really have it in my mind that queries locked tables, but I guess they have to.

Re: loops in loops

Posted: Tue Sep 28, 2010 3:58 pm
by DigitalMind
Jonah Bron wrote:That's interesting. I didn't really have it in my mind that queries locked tables, but I guess they have to.
http://forums.mysql.com/read.php?20,364169
http://forums.mysql.com/read.php?20,364 ... msg-364174

Re: loops in loops

Posted: Tue Sep 28, 2010 4:08 pm
by josh
Try and do a 50+ table join and get back at me about low query count & high performance ;-) 50 simple queries beats the 1 expensive one. It just depends.

Re: loops in loops

Posted: Tue Sep 28, 2010 4:17 pm
by DigitalMind
It mostly depends on MySQL design and one should know about disadvantages and use its advantages

Re: loops in loops

Posted: Tue Sep 28, 2010 4:21 pm
by Jonah Bron
josh wrote:Try and do a 50+ table join and get back at me about low query count & high performance ;-) 50 simple queries beats the 1 expensive one. It just depends.
That is why I put that reserve into my post.
Jonah Bron wrote:...My brain's slowing down...
:D