• Registered: 2008-02-15
  • Posts: 200

Topic: Holy cow, 50Querys.. Help me GOD

Help me GOD.


0.00057      SELECT u.*, g.*, o.logged, o.idle FROM users AS u INNER JOIN groups AS g ON u.group_id=g.g_id LEFT JOIN online AS o ON o.user_id=u.id WHERE u.id=2
0.00021     UPDATE online SET logged=1247640127 WHERE user_id=2
0.00017     UPDATE online SET current_page='/forums/viewforum.php', current_ip='127.0.0.1', current_page_id='1' WHERE user_id='2'
0.00015     SELECT * FROM online WHERE logged<1247639827
0.00027     SELECT f.forum_name, pf.forum_name AS parent_forum, f.redirect_url, f.moderators, f.num_topics, f.sort_by, f.parent_forum_id, fp.post_topics FROM forums AS f LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=1) LEFT JOIN forums AS pf ON f.parent_forum_id=pf.id WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND f.id=1
0.00035     SELECT COUNT(id) FROM reports WHERE zapped IS NULL
0.01845     SELECT COUNT(id) FROM messages WHERE showed=0 AND owner=2
-0.01855     SELECT f.forum_desc, f.forum_name, f.id, f.last_post, f.last_post_id, f.last_poster, f.moderators, f.num_posts, f.num_topics, f.redirect_url, p.poster_id AS last_poster_id FROM forums AS f LEFT JOIN posts AS p ON (p.id=f.last_post_id) WHERE parent_forum_id=1 ORDER BY disp_position
0.01836     SELECT imgaward FROM users WHERE username="monkuar"
0.00073     SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id FROM topics AS t INNER JOIN posts AS p ON (p.id=t.last_post_id) WHERE forum_id=1 ORDER BY sticky DESC, last_post DESC LIMIT 0, 20
-0.00389     SELECT imgaward FROM users WHERE username="monkuar"
      SELECT imgaward,id FROM users WHERE username="monkuar"
0.01822     SELECT imgaward FROM users WHERE username="monkuar"
0.00019     SELECT imgaward,id FROM users WHERE username="monkuar"
-0.01910     SELECT imgaward FROM users WHERE username="monkuar"
0.01838     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00023     SELECT imgaward FROM users WHERE username="monkuar"
0.00016     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00014     SELECT imgaward FROM users WHERE username="monkuar"
0.00015     SELECT imgaward,id FROM users WHERE username="monkuar"
-0.02070     SELECT imgaward FROM users WHERE username="monkuar"
0.01833     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00025     SELECT imgaward FROM users WHERE username="monkuar"
0.01824     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00014     SELECT imgaward FROM users WHERE username="monkuar"
0.00016     SELECT imgaward,id FROM users WHERE username="monkuar"
-0.01942     SELECT imgaward FROM users WHERE username="monkuar"
0.01833     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00023     SELECT imgaward FROM users WHERE username="monkuar"
-0.01919     SELECT imgaward,id FROM users WHERE username="monkuar"
0.01833     SELECT imgaward FROM users WHERE username="monkuar"
0.01822     SELECT imgaward,id FROM users WHERE username="monkuar"
-0.01855     SELECT imgaward FROM users WHERE username="monkuar"
0.01830     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00020     SELECT imgaward FROM users WHERE username="monkuar"
0.00015     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00015     SELECT imgaward FROM users WHERE username="monkuar"
0.00015     SELECT imgaward,id FROM users WHERE username="monkuar"
-0.02048     SELECT imgaward FROM users WHERE username="monkuar"
0.01829     SELECT imgaward,id FROM users WHERE username="monkuar"
0.01822     SELECT imgaward FROM users WHERE username="monkuar"
0.00017     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00016     SELECT imgaward FROM users WHERE username="monkuar"
-0.01947     SELECT imgaward,id FROM users WHERE username="monkuar"
0.01836     SELECT imgaward FROM users WHERE username="monkuar"
0.00024     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00016     SELECT imgaward FROM users WHERE username="monkuar"
0.00015     SELECT imgaward,id FROM users WHERE username="monkuar"
0.00014     SELECT imgaward FROM users WHERE username="monkuar"
0.00016     SELECT imgaward,id FROM users WHERE username="monkuar"
Total query time: 0.08461 s

Im using imgawards mod here is a picture:


http://i27.tinypic.com/k0lk7m.jpg

I mean that's what i want it look like but why does it loop through the query ..

here is my code:


<?php

// Fetch list of topics to display on this page
if ($pun_user['is_guest'] || $pun_config['o_show_dot'] == '0')
{
    // Without "the dot"
    $sql = 'SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'posts AS p ON (p.id=t.last_post_id) WHERE forum_id='.$id.' ORDER BY sticky DESC, '.(($cur_forum['sort_by'] == '1') ? 'posted' : 'last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];
}
   

$result = $db->query($sql) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

// If there are topics in this forum.
if ($db->num_rows($result))
{
    while ($cur_topic = $db->fetch_assoc($result))
    {
        $icon_text = $lang_common['Normal icon'];
        $item_status = '';
        $icon_type = 'e2';
$result4 = $db->query('SELECT imgaward FROM users WHERE username="'.$cur_topic['last_poster'].'" ') or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

    
    $post_user = $db->fetch_assoc($result4);

             // Image Award Mod Block Start
            //figure out the size of the award (Name of award should be in teh form:  Test_Award_100x20.png ... where png is format, 100x20 is dimensions and Test_Award is name of award (seen in admin interface)
            $lol2=$post_user['imgaward'];
            
            if ($post_user['imgaward'] == "") { 
            $lol12 = "";
            }
            else {
            $lol12 = "<img src='img/stars/$lol2'>";
            }
        if ($cur_topic['moved_to'] == null)
        
            $last_post = '<a href="viewtopic.php?pid='.$cur_topic['last_post_id'].'#p'.$cur_topic['last_post_id'].'">'.format_time($cur_topic['last_post']).'</a><br /><span class="byuser">'.$lang_common['by'].'&nbsp;<a href="profile.php?id='.$cur_topic['last_poster_id'].'">'.pun_htmlspecialchars($cur_topic['last_poster']).'</a>'.$lol12.'</span>';
else
            $last_post = '&nbsp;';

            
            
            $result4 = $db->query('SELECT imgaward,id FROM users WHERE username="'.($cur_topic['poster']).'" ') or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

    
    $post_user = $db->fetch_assoc($result4);

             // Image Award Mod Block Start
            //figure out the size of the award (Name of award should be in teh form:  Test_Award_100x20.png ... where png is format, 100x20 is dimensions and Test_Award is name of award (seen in admin interface)
            $lol23=$post_user['imgaward'];
            
            if ($post_user['imgaward'] == "") { 
            $lol123 = "";
            }
            else {
            $lol123 = "<img src='img/stars/$lol23'>";
            }
        if ($pun_config['o_censoring'] == '1')
            $cur_topic['subject'] = censor_words($cur_topic['subject']);

        if ($cur_topic['moved_to'] != 0)
        
            $subject = $lang_forum['Moved'].': <a href="viewtopic.php?id='.$cur_topic['moved_to'].'">'.pun_htmlspecialchars($cur_topic['subject']).'</a> <span class="byuser">'.$lang_common['by'].'&nbsp;'.pun_htmlspecialchars($cur_topic['poster']).''.$lol123.'</span>';
        else if ($cur_topic['closed'] == '0')
            $subject = '<a href="viewtopic.php?id='.$cur_topic['id'].'">'.pun_htmlspecialchars($cur_topic['subject']).'</a> <span class="byuser">'.'';
        //monk@
        else
        {
            $subject = '<font color=red>Closed: <a href="viewtopic.php?id='.$cur_topic['id'].'">'.$cur_topic['subject'].'</a></font>'.'';
            $icon_text = '<tr class="cl bc1"> 
    ';
        }

        if (!$pun_user['is_guest'] && $cur_topic['last_post'] > $pun_user['last_visit'] && $cur_topic['moved_to'] == null)
        {
            
            $item_status .= ' inew';
            $icon_type = 'e1';
            $subject = '<strong>'.$subject.'</strong>';
            $subject_new_posts = '[&nbsp;<a href="viewtopic.php?id='.$cur_topic['id'].'&amp;action=new" title="'.$lang_common['New posts info'].'">'.$lang_common['New posts'].'</a>&nbsp;]';
        }
        else
            $subject_new_posts = null;

        // Should we display the dot or not? :)
        if (!$pun_user['is_guest'] && $pun_config['o_show_dot'] == '1')
        {
            if ($cur_topic['has_posted'] == $pun_user['id'])
                $subject = '<strong>&middot;</strong>&nbsp;'.$subject;
            else
                $subject = '&nbsp;&nbsp;'.$subject;
        }

        if ($cur_topic['sticky'] == '1')
        {
            $subject = '<b>'.'Pinned'.':</b> '.$subject;
            
    
        }


        $num_pages_topic = ceil(($cur_topic['num_replies'] + 1) / $pun_user['disp_posts']);

        if ($num_pages_topic > 1)
            $subject_multipage = '[ '.paginate($num_pages_topic, -1, 'viewtopic.php?id='.$cur_topic['id']).' ]';
        else
            $subject_multipage = null;

        // Should we show the "New posts" and/or the multipage links?
        if (!empty($subject_new_posts) || !empty($subject_multipage))
        {
            $subject .= '&nbsp; '.(!empty($subject_new_posts) ? $subject_new_posts : '');
            $subject .= !empty($subject_multipage) ? ' '.$subject_multipage : '';
        }
        $rowbg = $rowbg == 'ce' ? 'ce bc1' : 'ce';

?>

<tr<?php if ($item_status != '') echo ' class="'.trim($item_status).'"'; ?>>
<tr class="<?php echo $rowbg ?>">
<?php echo trim($icon_text) ?>
    <td align="left"><div class="<?php echo $icon_type ?>"><div class="nosize"></div></td> 
    
    <td align="left"> <?php echo $subject ?><br><span class="desc">Objective</span></td> 
    <td align="left">
    
    <?php echo '<a href=profile.php?id='.$post_user['id'].'>'.($cur_topic['poster']).''.$lol123.'</a>' ?>
    
    
    <br><span class="desc"><?php echo format_time($cur_topic['posted']) ?></span></td> 
    <td align="left"><?php echo ($cur_topic['moved_to'] == null) ? $cur_topic['num_replies'] : '&nbsp;' ?></td> 
    <td align="left"><?php echo ($cur_topic['moved_to'] == null) ? $cur_topic['num_views'] : '&nbsp;' ?></td> 
    <td align="left"><?php echo $last_post ?></td> 
</tr>

See how it's in the while function? but i have to have in the while function because that way I can use my $cur_post variable for my MYSQL WHERE Clause, otherwise it wont bring up the right image,

Im talking about this query 

$result4 = $db->query('SELECT imgaward FROM users WHERE username="'.$cur_topic['last_poster'].'" ') or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

Anyone know any ideas on how to make it's only 1 Query or maybe im doing something totally wrong?? please help thx..

  • Slavok
  • PunBB Developer
  • Offline
  • From: Russia
  • Registered: 2008-06-11
  • Posts: 1,238

Re: Holy cow, 50Querys.. Help me GOD

Edit the first query in your code like posted below:

$sql = 'SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id, u.imgaward INNER JOIN '.$db->prefix.'users AS u ON u.username = t.last_poster FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'posts AS p ON (p.id=t.last_post_id) WHERE forum_id='.$id.' ORDER BY sticky DESC, '.(($cur_forum['sort_by'] == '1') ? 't.posted' : 't.last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];

In the processing of each topic, remove the SQL query you used for getting user's award. The award is in $cur_topic['imgaward'] now.

Slavok's Website

  • Registered: 2008-02-15
  • Posts: 200

Re: Holy cow, 50Querys.. Help me GOD

Slavok wrote:

$sql = 'SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id, u.imgaward INNER JOIN '.$db->prefix.'users AS u ON u.username = t.last_poster FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'posts AS p ON (p.id=t.last_post_id) WHERE forum_id='.$id.' ORDER BY sticky DESC, '.(($cur_forum['sort_by'] == '1') ? 't.posted' : 't.last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];

Hi sir.. thanks so much for replying and trying to help but i get this error:

File: C:\wamp\www\forums\viewforum.php
Line: 261

PunBB reported: Unable to fetch topic list

Database reported: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN users AS u ON u.username = t.last_poster FROM topics AS t INNER JOIN ' at line 1 (Errno: 1064)

Failed query: SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id, u.imgaward INNER JOIN users AS u ON u.username = t.last_poster FROM topics AS t INNER JOIN posts AS p ON (p.id=t.last_post_id) WHERE forum_id=1 ORDER BY sticky DESC, t.last_post DESC LIMIT 0, 20

sad

  • Registered: 2008-02-15
  • Posts: 200

Re: Holy cow, 50Querys.. Help me GOD

yo, im using this 1 

$query = 'SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'posts AS p ON (p.id=t.last_post_id) WHERE forum_id='.$id.' ORDER BY sticky DESC, '.(($cur_forum['sort_by'] == '1') ? 'posted' : 'last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];
}

is that good can u make it betteR?

  • Slavok
  • PunBB Developer
  • Offline
  • From: Russia
  • Registered: 2008-06-11
  • Posts: 1,238

Re: Holy cow, 50Querys.. Help me GOD

Here is the corrected query:

$query = 'SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to, p.poster_id AS last_poster_id FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'users AS u ON u.username = t.last_poster INNER JOIN '.$db->prefix.'posts AS p ON (p.id=t.last_post_id) WHERE forum_id='.$id.' ORDER BY sticky DESC, '.(($cur_forum['sort_by'] == '1') ? 'posted' : 'last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];

But this query is slow for big amounts of rows in DB. It will be better if you collect all last_poster to a variable and then fetch their's awards.

Slavok's Website

  • Registered: 2008-02-15
  • Posts: 200

Re: Holy cow, 50Querys.. Help me GOD

so u think it will be better just the way i had it? [on the database? faster] ?

  • User33
  • PunBB senior member
  • Offline
  • Registered: 2008-02-03
  • Posts: 875

Re: Holy cow, 50Querys.. Help me GOD

You could store the imgaward info in a variable for each user:

if(!isset($imgaward[$cur_topic['last_poster']])
{
$result4 = $db->query('SELECT imgaward FROM users WHERE username="'.$cur_topic['last_poster'].'" ') or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

$post_user = $db->fetch_assoc($result4);
$imgaward[$cur_topic['last_poster']] = $post_user;
}
else
$post_user = $imgaward[$cur_topic['last_poster']];
  • Registered: 2008-02-15
  • Posts: 200

Re: Holy cow, 50Querys.. Help me GOD

Garciat wrote:

You could store the imgaward info in a variable for each user:

if(!isset($imgaward[$cur_topic['last_poster']])
{
$result4 = $db->query('SELECT imgaward FROM users WHERE username="'.$cur_topic['last_poster'].'" ') or error('Unable to fetch category/forum list', __FILE__, __LINE__, $db->error());

$post_user = $db->fetch_assoc($result4);
$imgaward[$cur_topic['last_poster']] = $post_user;
}
else
$post_user = $imgaward[$cur_topic['last_poster']];

would that be the most efficient waY?

  • Slavok
  • PunBB Developer
  • Offline
  • From: Russia
  • Registered: 2008-06-11
  • Posts: 1,238

Re: Holy cow, 50Querys.. Help me GOD

The count of DB queries in the method suggested by Garciat depends on the count of unique values of last_poster. I suggest that you fetch awards of all last posted users with only one query. I think it will be more efficient.

Slavok's Website

  • Registered: 2008-02-15
  • Posts: 200

Re: Holy cow, 50Querys.. Help me GOD

Sorry  dont know that kinda of talk, so the query i posted is what you mean right ? {sorry im french}

<?php echo "Thanks slovak"; ?>

11 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Holy cow, 50Querys.. Help me GOD

NiCk Newman wrote:

{sorry im french}

The word you're after is either 'thick' or 'annoying'. smile


p.s: You sure as hell ain't French Nick.

MattF's Website

12 Reply by Slavok

  • Slavok
  • PunBB Developer
  • Offline
  • From: Russia
  • Registered: 2008-06-11
  • Posts: 1,238

Re: Holy cow, 50Querys.. Help me GOD

NiCk Newman wrote:

Sorry  dont know that kinda of talk, so the query i posted is what you mean right ? {sorry im french}

No. As I posted above, this query will be slow for big amounts of rows in a DB. It will be more efficient if you:

  • Process all fetched topics and collect all last_poster values.

  • Fetch award of last_posters with an SQL-query.

  • Create an associative array where key is a username and values is award.

After this you can use this array to get user's award.

NiCk Newman wrote:
<?php echo "Thanks slovak"; ?>

I'm Slavok smile

Slavok's Website