Topic: Problem with MySQL query

The following query in viewtopic.php (That I have modified myself, and has been modified by a couple mods installed) seems to be using a lot of CPU, and taking over 2secs to execute, which seems very slow to me compared with normal MySQL queries.

I was wondering if anyone has any suggestions as to what part is causing the problems, or even a way to fix them. Thanks

$result = $db->query('SELECT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting,
u.num_posts, u.registered, u.admin_note, u.adult_sig, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS
count_rep_minus, u.reputation_enable, u.awards, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email,
p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_title, g.g_user_title, g.g_link_color,
g.g_hover_color, o.user_id AS is_online, b.id AS b_id, b.message AS b_message, b.expire AS b_expire, b.banned_by
AS b_by FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'users AS u ON u.id=p.poster_id INNER JOIN
'.$db->prefix.'groups AS g ON g.g_id=u.group_id LEFT JOIN '.$db->prefix.'online AS o ON (o.user_id=u.id AND o.user_id!=1
AND o.idle=0) LEFT JOIN '.$db->prefix.'bans AS b ON b.username=u.username LEFT JOIN '.$db->prefix.'reputation as r
ON (r.user_id=u.id) WHERE p.topic_id='.$id.' GROUP BY p.id ORDER BY p.id LIMIT '.$start_from.','.$pun_user['disp_posts'],
true) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());

PS. Not sure if this is the correct board since its not a clean PunBB install, please move if necessary.

Re: Problem with MySQL query

Run an explain on the select query, that'll help me see what the problem is (It looks like you added a lot of extra joins there)

3 (edited by Reines 2007-06-01 15:11)

Re: Problem with MySQL query

http://stealingyour.info/explain.html

Re: Problem with MySQL query

http://dev.mysql.com/doc/refman/5.0/en/explain.html wink

Re: Problem with MySQL query

Well now I feel stupid tongue Edited my post above with a link.

Re: Problem with MySQL query

Aha, yeah, the joins with bans and reputation are the issue.
Can you give me the code for your viewtopic.php so I can try rewriting it?

Re: Problem with MySQL query

Sure, though only bother if you happen to have spair time, it's not a huge issue.

<?php
/***********************************************************************

  Copyright (C) 2002-2005  Rickard Andersson (rickard@punbb.org)

  This file is part of PunBB.

  PunBB is free software; you can redistribute it and/or modify it
  under the terms of the GNU General Public License as published
  by the Free Software Foundation; either version 2 of the License,
  or (at your option) any later version.

  PunBB is distributed in the hope that it will be useful, but
  WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place, Suite 330, Boston,
  MA  02111-1307  USA

************************************************************************/

// Enable SPELLCHECKER on this page
// Disable it by adding // from the following line (on this page only)
define('PBB_SPELLCHECKER', 1);

define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';

if ($pun_user['g_read_board'] == '0')
    message($lang_common['No view']);

$action = isset($_GET['action']) ? $_GET['action'] : null;
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$pid = isset($_GET['pid']) ? intval($_GET['pid']) : 0;
if ($id < 1 && $pid < 1)
    message($lang_common['Bad request']);

// Load the viewtopic.php language file
require PUN_ROOT.'lang/'.$pun_user['language'].'/topic.php';
require PUN_ROOT.'lang/'.$pun_user['language'].'/reputation.php';
// Load the post.php language file
require PUN_ROOT.'lang/'.$pun_user['language'].'/post.php';

// If a post ID is specified we determine topic ID and page number so we can redirect to the correct message
if ($pid)
{
    $result = $db->query('SELECT topic_id FROM '.$db->prefix.'posts WHERE id='.$pid) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    if (!$db->num_rows($result))
        message($lang_common['Bad request']);

    $id = $db->result($result);

    // Determine on what page the post is located (depending on $pun_user['disp_posts'])
    $result = $db->query('SELECT id FROM '.$db->prefix.'posts WHERE topic_id='.$id.' ORDER BY posted') or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    $num_posts = $db->num_rows($result);

    for ($i = 0; $i < $num_posts; ++$i)
    {
        $cur_id = $db->result($result, $i);
        if ($cur_id == $pid)
            break;
    }
    ++$i;    // we started at 0

    $_GET['p'] = ceil($i / $pun_user['disp_posts']);
}

// If action=new, we redirect to the first new post (if any)
else if ($action == 'new' && !$pun_user['is_guest'])
{
    $result = $db->query('SELECT MIN(id) FROM '.$db->prefix.'posts WHERE topic_id='.$id.' AND posted>'.$pun_user['last_visit']) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    $first_new_post_id = $db->result($result);

    if ($first_new_post_id)
        header('Location: viewtopic.php?pid='.$first_new_post_id.'#p'.$first_new_post_id);
    else    // If there is no new post, we go to the last post
        header('Location: viewtopic.php?id='.$id.'&action=last');

    exit;
}

// If action=last, we redirect to the last post
else if ($action == 'last')
{
    $result = $db->query('SELECT MAX(id) FROM '.$db->prefix.'posts WHERE topic_id='.$id) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    $last_post_id = $db->result($result);

    if ($last_post_id)
    {
        header('Location: viewtopic.php?pid='.$last_post_id.'#p'.$last_post_id);
        exit;
    }
}


// Fetch some info about the topic
if (!$pun_user['is_guest'])
    $result = $db->query('SELECT t.subject, t.closed, t.num_replies, t.sticky, t.question, t.options, t.voters, t.votes, f.id AS forum_id, f.forum_name, f.moderators, f.min_posts, f.min_rep, fp.post_replies, s.user_id AS is_subscribed, c.hidden FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id INNER JOIN '.$db->prefix.'categories AS c ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'subscriptions AS s ON (t.id=s.topic_id AND s.user_id='.$pun_user['id'].') LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.id='.$id.' AND t.moved_to IS NULL') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());
else
    $result = $db->query('SELECT t.subject, t.closed, t.num_replies, t.sticky, t.question, t.options, t.voters, t.votes, f.id AS forum_id, f.forum_name, f.moderators, f.min_posts, f.min_rep, fp.post_replies, 0, c.hidden FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id INNER JOIN '.$db->prefix.'categories AS c ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.id='.$id.' AND t.moved_to IS NULL') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());

if (!$db->num_rows($result))
    message($lang_common['Bad request']);

$cur_topic = $db->fetch_assoc($result);

if ($cur_topic['hidden'] == '1' && $pun_config['a_alternate_ad'] != '')
    define('ALTERNATE_AD', 1);

$h_link = '';
if ($cur_topic['hidden'] == '1')
    $h_link = '<li> » <a href="forums.php?show=hidden">'.$lang_common['Hidden forums'].'</a></li>';

// Sort out who the moderators are and if we are currently a moderator (or an admin)
$mods_array = ($cur_topic['moderators'] != '') ? unserialize($cur_topic['moderators']) : array();
$is_admmod = ($pun_user['g_id'] < PUN_MOD || ($pun_user['g_id'] == PUN_MOD && array_key_exists($pun_user['username'], $mods_array))) ? true : false;

// Do we have enough posts to access this board?
if (($cur_topic['min_posts'] > $pun_user['num_posts'] || ($cur_topic['min_rep'] > $pun_user['count_rep'] && $pun_config['o_reputation_enabled'] == 1)) && !$is_admmod)
    message($lang_common['Bad request']);

// Can we or can we not post replies?
if ($cur_topic['closed'] == '0')
{
    if (($cur_topic['post_replies'] == '' && $pun_user['g_post_replies'] == '1') || $cur_topic['post_replies'] == '1' || $is_admmod)
        $post_link = '<a href="post.php?tid='.$id.'">'.$lang_topic['Post reply'].'</a>';
    else
        $post_link = ' ';
}
else
{
    $post_link = $lang_topic['Topic closed'];

    if ($is_admmod)
        $post_link .= ' / <a href="post.php?tid='.$id.'">'.$lang_topic['Post reply'].'</a>';
}


// Determine the post offset (based on $_GET['p'])
$num_pages = ceil(($cur_topic['num_replies'] + 1) / $pun_user['disp_posts']);

$p = (!isset($_GET['p']) || $_GET['p'] <= 1 || $_GET['p'] > $num_pages) ? 1 : $_GET['p'];
$start_from = $pun_user['disp_posts'] * ($p - 1);

// Generate paging links
$paging_links = $lang_common['Pages'].': '.paginate($num_pages, $p, 'viewtopic.php?id='.$id);

$quickpost = false;
if ($pun_config['o_quickpost'] == '1' &&
    !$pun_user['is_guest'] &&
    ($cur_topic['post_replies'] == '1' || ($cur_topic['post_replies'] == '' && $pun_user['g_post_replies'] == '1')) &&
    ($cur_topic['closed'] == '0' || $is_admmod))
{
    $required_fields = array('req_message' => $lang_common['Message']);
    $quickpost = true;
}

if (!$pun_user['is_guest'] && $pun_config['o_subscriptions'] == '1')
{
    if ($cur_topic['is_subscribed'])
        // I apologize for the variable naming here. It's a mix of subscription and action I guess :-)
        $subscraction = '<p class="subscribelink clearb">'.$lang_topic['Is subscribed'].' - <a href="misc.php?unsubscribe='.$id.'">'.$lang_topic['Unsubscribe'].'</a></p>'."\n";
    else
        $subscraction = '<p class="subscribelink clearb"><a href="misc.php?subscribe='.$id.'">'.$lang_topic['Subscribe'].'</a></p>'."\n";
}
else
    $subscraction = '<div class="clearer"></div>'."\n";

$page_title = pun_htmlspecialchars($pun_config['o_board_title'].' / '.$cur_topic['subject']);
define('PUN_ALLOW_INDEX', 1);
require PUN_ROOT.'header.php';

?>
<div class="linkst">
    <div class="inbox">
        <p class="pagelink conl"><?php echo $paging_links ?></p>
        <p class="postlink conr"><?php echo $post_link ?></p>
        <ul><li><a href="forums.php"><?php echo $lang_common['Forums'] ?></a></li><?php echo $h_link ?><li> » <a href="viewforum.php?id=<?php echo $cur_topic['forum_id'] ?>"><?php echo pun_htmlspecialchars($cur_topic['forum_name']) ?></a></li><li> » <?php echo pun_htmlspecialchars($cur_topic['subject']) ?></li></ul>
        <div class="clearer"></div>
    </div>
</div>

<?php


require PUN_ROOT.'include/parser.php';

if (!empty($cur_topic['question']) && !empty($cur_topic['options']))
{
    $options = unserialize($cur_topic['options']);
    $voters = !empty($cur_topic['voters']) ? unserialize($cur_topic['voters']) : array();
    $votes = !empty($cur_topic['votes']) ? unserialize($cur_topic['votes']) : array();

?>
<div class="blockform">
    <h2><span><?php echo $lang_common['Poll'] ?>: <?php echo pun_htmlspecialchars($cur_topic['question']) ?></span></h2>
    <div class="box">
<?php

    // If we are not a guest, we have not voted, and the topic is still open, let us vote
    if(!$pun_user['is_guest'] && !in_array($pun_user['id'], $voters) && $cur_topic['closed'] == '0')
    {
?>
        <form id="post" method="post" action="vote.php">
            <div class="inform">
                <div class="rbox" align="center">
                    <input type="hidden" name="tid" value="<?php echo $id; ?>" />
                    <input type="hidden" name="form_sent" value="1" />
                    <strong><?php echo pun_htmlspecialchars($cur_topic['question']) ?></strong>
                    <br /><br />
                    <table style="width:auto;table-layout:auto;">
<?php
        while (list($key, $value) = each($options))
        {
?>
                        <tr>
                            <td style="width:10px;border:none">
                                <input name="vote" type="radio" value="<?php echo $key + 1 ?>">
                            </td>
                            <td style="width:auto;border:none">
                                <span><?php echo pun_htmlspecialchars($value); ?></span>
                            </td>
                        </tr>
<?php
        }
?>
                    </table>
                </div>
            </div>
            <p align="center"><input type="submit" name="submit" value="<?php echo $lang_common['Submit'] ?>" accesskey="s" /> <input type="submit" name="null" value="<?php echo $lang_common['Null'] ?>" accesskey="n" /></p>
        </form>
    </div>
</div>
<?php
    }
    else
    {
?>
        <div class="inform">
            <div class="rbox" align="center">
                <strong><?php echo pun_htmlspecialchars($cur_topic['question']) ?></strong>
                <br /><br />
                <table style="width:70%;table-layout:auto;">
<?php
            $total = 0;
            $percent = 0;
            $percent_int = 0;
            while (list($key, $val) = each($options))
            {
                if (isset($votes[$key]))
                    $total += $votes[$key];
            }
            reset($options);

            while (list($key, $value) = each($options))
            {
                if (isset($votes[$key]))
                    $percent = floor($votes[$key] * 100 / $total);
?>
                    <tr>
                        <td style="width:auto;border:none">
                            <?php echo pun_htmlspecialchars($value); ?>
                        </td>
                        <td style="width:50%;border:none">
                            <h2 style="width: <?php echo (isset($votes[$key]) ? ($percent > 98 ? 98 : $percent) : '0') ?>%; font-size: 1px; height: 2px; margin-bottom: 3px"></h2>
                        </td>
                        <td style="width:auto;border:none">
                            <?php echo (isset($votes[$key]) ? $percent.'% - '.$votes[$key] : '0% - 0')."\n" ?>
                        </td>
                    </tr>
<?php
            }
?>
                    <tr>
                    <td colspan="3" style="border:none">
                        <center>Total Votes: <?php echo $total; ?></center>
                    </td>
                </table>
            </div>
        </div>
    </div>
</div>
<?php
    }
}

$bg_switch = true;    // Used for switching background color in posts
$post_count = 0;    // Keep track of post numbers

$awards = array();
$result = $db->query('SELECT id, img, name FROM '.$db->prefix.'expertise') or error('Unable to fetch expertise info', __FILE__, __LINE__, $db->error());
while ($cur_award = $db->fetch_assoc($result))
    $awards[$cur_award['id']] = $cur_award;

// Retrieve the posts (and their respective poster/online status)
$result = $db->query('SELECT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, u.adult_sig, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, u.awards, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_title, g.g_user_title, g.g_link_color, g.g_hover_color, o.user_id AS is_online, b.id AS b_id, b.message AS b_message, b.expire AS b_expire, b.banned_by AS b_by FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'users AS u ON u.id=p.poster_id INNER JOIN '.$db->prefix.'groups AS g ON g.g_id=u.group_id LEFT JOIN '.$db->prefix.'online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN '.$db->prefix.'bans AS b ON b.username=u.username LEFT JOIN '.$db->prefix.'reputation as r ON r.user_id=u.id WHERE p.topic_id='.$id.' GROUP BY p.id ORDER BY p.id LIMIT '.$start_from.','.$pun_user['disp_posts'], true) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
while ($cur_post = $db->fetch_assoc($result))
{
    $post_count++;
    $user_avatar = '';
    $user_info = array();
    $user_contacts = array();
    $post_actions = array();
    $is_online = '';
    $signature = '';
    $user_awards = array();
    $award_str = '';

    // If the poster is a registered user.
    if ($cur_post['poster_id'] > 1)
    {
        $rank_pips = make_pips($cur_post['num_posts']);

        if(!empty($cur_post['awards']))
        {
            $user_awards = split(',', $cur_post['awards']);
            shuffle($user_awards);
            $uawards = array();
            for($x = 0;count($uawards) < 3 && $x < count($user_awards);$x++)
            {
                if(!array_key_exists($user_awards[$x], $awards))
                    continue;
                $uawards[] = '<img src="'.PUN_ROOT.'img/special/'.$awards[$user_awards[$x]]['img'].'" alt="'.pun_htmlspecialchars($awards[$user_awards[$x]]['name']).'" title=" '.pun_htmlspecialchars($awards[$user_awards[$x]]['name']).' " />';
            }
            $award_str = implode('  ', $uawards);
        }

        $username = '<a href="profile.php?id='.$cur_post['poster_id'].'" class="username">'.pun_htmlspecialchars($cur_post['username']).'</a>';
        $user_title = get_title($cur_post);

        // Format the online indicator
        $is_online = ($cur_post['is_online'] == $cur_post['poster_id']) ? '<strong>'.$lang_topic['Online'].'</strong>' : $lang_topic['Offline'];

        if ($pun_config['o_avatars'] == '1' && $cur_post['use_avatar'] == '1' && $pun_user['show_avatars'] != '0' && ($cur_post['adult_sig'] != '1' || $pun_user['show_adult'] == '1'))
        {
            if ($img_size = @getimagesize($pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.gif'))
                $user_avatar = '<img src="'.$pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.gif" '.$img_size[3].' alt="" />';
            else if ($img_size = @getimagesize($pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.jpg'))
                $user_avatar = '<img src="'.$pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.jpg" '.$img_size[3].' alt="" />';
            else if ($img_size = @getimagesize($pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.png'))
                $user_avatar = '<img src="'.$pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.png" '.$img_size[3].' alt="" />';
        }
        else
            $user_avatar = '';

        // We only show location, register date, post count and the contact links if "Show user info" is enabled
        if ($pun_config['o_show_user_info'] == '1')
        {
            if ($cur_post['location'] != '')
                $user_info[] = '<dd>'.$lang_topic['From'].': '.pun_htmlspecialchars($cur_post['location']);

            $user_info[] = '<dd>'.$lang_common['Registered'].': '.date($pun_config['o_date_format'], $cur_post['registered']);

            if ($pun_config['o_show_post_count'] == '1' || $pun_user['g_id'] < PUN_GUEST || $pun_user['g_view_count'] == '1')
                $user_info[] = '<dd>'.$lang_common['Posts'].': '.$cur_post['num_posts'];

            // Now let's deal with the contact links (E-mail and URL)
            if (($cur_post['email_setting'] == '0' && !$pun_user['is_guest']) || $pun_user['g_id'] < PUN_GUEST)
                $user_contacts[] = '<a href="mailto:'.$cur_post['email'].'">'.$lang_common['E-mail'].'</a>';
            else if ($cur_post['email_setting'] == '1' && !$pun_user['is_guest'])
                $user_contacts[] = '<a href="misc.php?email='.$cur_post['poster_id'].'">'.$lang_common['E-mail'].'</a>';

            if($pun_config['o_pms_enabled'] && !$pun_user['is_guest'] && $pun_user['g_pm'] == 1)
            {
                $pid = isset($cur_post['poster_id']) ? $cur_post['poster_id'] : $cur_post['id'];
                $user_contacts[] = '<a href="message_send.php?id='.$pid.'&tid='.$id.'">'.$lang_common['PM'].'</a>';
            }

            if ($cur_post['url'] != '')
                $user_contacts[] = '<a href="'.pun_htmlspecialchars($cur_post['url']).'">'.$lang_topic['Website'].'</a>';
        }

        $user_info[] = '<dd>'.$lang_common['Group'].': '.(isset($cur_post['b_id']) ? $lang_common['Banned'] : $cur_post['g_title']);

        $country_code = IPtoCountry($cur_post['poster_ip']);

        if ($pun_user['g_id'] < PUN_GUEST)
        {
            $user_info[] = '<dd>IP: <a href="moderate.php?get_host='.$cur_post['id'].'">'.$cur_post['poster_ip'].'</a> ('.$country_code.')';

            if ($cur_post['admin_note'] != '')
                $user_info[] = '<dd>'.$lang_topic['Note'].': <strong>'.pun_htmlspecialchars($cur_post['admin_note']).'</strong>';
        }
    }
    // If the poster is a guest (or a user that has been deleted)
    else
    {
        $username = pun_htmlspecialchars($cur_post['username']);
        $user_title = get_title($cur_post);
        $country_code = IPtoCountry($cur_post['poster_ip']);

        if ($pun_user['g_id'] < PUN_GUEST)
            $user_info[] = '<dd>IP: <a href="moderate.php?get_host='.$cur_post['id'].'">'.$cur_post['poster_ip'].'</a> ('.$country_code.')';

        if ($pun_config['o_show_user_info'] == '1' && $cur_post['poster_email'] != '' && !$pun_user['is_guest'])
            $user_contacts[] = '<a href="mailto:'.$cur_post['poster_email'].'">'.$lang_common['E-mail'].'</a>';
    }

    // Generation post action array (quote, edit, delete etc.)
    if (!$is_admmod)
    {
        if (!$pun_user['is_guest'])
            $post_actions[] = '<li class="postreport"><a href="misc.php?report='.$cur_post['id'].'">'.$lang_topic['Report'].'</a>';

        if ($cur_topic['closed'] == '0')
        {
            if ($cur_post['poster_id'] == $pun_user['id'])
            {
                if ((($start_from + $post_count) == 1 && $pun_user['g_delete_topics'] == '1') || (($start_from + $post_count) > 1 && $pun_user['g_delete_posts'] == '1'))
                    $post_actions[] = '<li class="postdelete"><a href="delete.php?id='.$cur_post['id'].'">'.$lang_topic['Delete'].'</a>';
                if ($pun_user['g_edit_posts'] == '1')
                    $post_actions[] = '<li class="postedit"><a href="edit.php?id='.$cur_post['id'].'">'.$lang_topic['Edit'].'</a>';
            }

            if (($cur_topic['post_replies'] == '' && $pun_user['g_post_replies'] == '1') || $cur_topic['post_replies'] == '1')
                $post_actions[] = '<li class="postquote"><a href="post.php?tid='.$id.'&qid='.$cur_post['id'].'">'.$lang_topic['Quote'].'</a>';
        }
    }
    else
        $post_actions[] = '<li class="postreport"><a href="misc.php?report='.$cur_post['id'].'">'.$lang_topic['Report'].'</a>'.$lang_topic['Link separator'].'</li><li class="postdelete"><a href="delete.php?id='.$cur_post['id'].'">'.$lang_topic['Delete'].'</a>'.$lang_topic['Link separator'].'</li><li class="postedit"><a href="edit.php?id='.$cur_post['id'].'">'.$lang_topic['Edit'].'</a>'.$lang_topic['Link separator'].'</li><li class="postquote"><a href="post.php?tid='.$id.'&qid='.$cur_post['id'].'">'.$lang_topic['Quote'].'</a>';


    // Switch the background color for every message.
    $bg_switch = ($bg_switch) ? $bg_switch = false : $bg_switch = true;
    $vtbg = ($bg_switch) ? ' roweven' : ' rowodd';


    // Perform the main parsing of the message (BBCode, smilies etc)
    $cur_post['message'] = parse_message($cur_post['message'], $cur_post['hide_smilies']);

    // Do signature parsing/caching
    if ($cur_post['signature'] != '' && $pun_user['show_sig'] != '0' && ($cur_post['adult_sig'] != '1' || $pun_user['show_adult'] == '1'))
    {
        if (isset($signature_cache[$cur_post['poster_id']]))
            $signature .= $signature_cache[$cur_post['poster_id']];
        else
        {

            if(isset($cur_post['b_id'])) {
                if(isset($cur_post['b_message'])) {
                    $signature .= '<b>Banned:</b> '.pun_htmlspecialchars($cur_post['b_message']).'<br />';
                }
                if(isset($cur_post['b_expire'])) {
                    $signature .= '<b>Expires:</b> '.format_time($cur_post['b_expire'], true).'<br />';
                }
                if(isset($cur_post['b_by'])) {
                    $signature .= '<b>Banned By:</b> '.pun_htmlspecialchars($cur_post['b_by']).'<br />';
                }
                $signature .= '<hr /><br />';
            }

            $signature .= parse_signature($cur_post['signature']);
            $signature_cache[$cur_post['poster_id']] = $signature;
        }
    }

?>
<div id="p<?php echo $cur_post['id'] ?>" class="blockpost<?php echo $vtbg ?><?php if (($post_count + $start_from) == 1) echo ' firstpost'; ?>">
    <h2><span><span class="conr">#<?php echo ($start_from + $post_count) ?> </span><a href="viewtopic.php?pid=<?php echo $cur_post['id'].'#p'.$cur_post['id'] ?>"><?php echo format_time($cur_post['posted']) ?></a></span></h2>
    <div class="box">
        <div class="inbox">
            <div class="postleft">
                <dl>
                    <dt<?php echo(isset($cur_post['g_link_color']) && isset($cur_post['g_hover_color']) ? ' class="'.strtolower(str_replace(' ', '', $cur_post['g_title'])).'"' : '') ?>><strong><?php echo $username ?></strong></dt>
                    <dd class="usertitle"><strong><?php echo $user_title ?></strong></dd>
                    <dd><?php echo $rank_pips ?></dd>
                    <dd class="postavatar"><?php echo $user_avatar ?></dd>
<?php
if (count($user_info)) echo "\t\t\t\t\t".implode('</dd>'."\n\t\t\t\t\t", $user_info).'</dd>'."\n";

if ($cur_post['poster_id'] != 1 && $pun_user['g_rep_enable'] == 1 && $pun_config['o_reputation_enabled'] == 1 && $cur_post['reputation_enable'] == 1 && $pun_user['reputation_enable_adm'] == 1 && $pun_user['reputation_enable'] == 1)  {
?>
<dd>
<?php echo '<a href="reputation.php?uid='.$cur_post['poster_id'].'">'.$lang_reputation['Reputation'].'</a>'; ?>:
<?php
    //If viewer are guest or user who post this message,then we do not show control buttons
    if($pun_user['is_guest'] != true && $pun_user['username'] != $cur_post['username']) {
        $repdata = '&pid='.$cur_post['id'].'&uid='.$cur_post['poster_id'].'&method=';
        $repdataplus = $repdata.'1';
        $repdataminus = $repdata.'2';
?>
<a href="reputation.php?<?php echo $repdataplus; ?>"><img src="img/warn_add.gif" alt="+" border="0"></a>  <strong><?php echo $cur_post['count_rep_plus'] - $cur_post['count_rep_minus']; ?>  </strong><a href="reputation.php?<?php echo $repdataminus; ?>"><img src="img/warn_minus.gif" alt="-" border="0"></a>
<?php
    }
    else {
        echo '  <strong>'.($cur_post['count_rep_plus'] - $cur_post['count_rep_minus']).' </strong>'."\n";
    }
?>
</dd>
<?php
}

if (count($user_contacts)) echo "\t\t\t\t\t".'<dd class="usercontacts">'.implode('  ', $user_contacts).'</dd>'."\n"; ?>
<?php if (!empty($award_str)) echo "\t\t\t\t\t".'<dd class="usercontacts">'.$award_str.'</dd>'."\n"; ?>
                </dl>
            </div>
            <div class="postright">
                <h3><?php if (($post_count + $start_from) > 1) echo ' Re: '; ?><?php echo pun_htmlspecialchars($cur_topic['subject']) ?></h3>
                <div class="postmsg">
                    <?php echo $cur_post['message']."\n" ?>
<?php if ($cur_post['edited'] != '') echo "\t\t\t\t\t".'<p class="postedit"><em>'.$lang_topic['Last edit'].' '.pun_htmlspecialchars($cur_post['edited_by']).' ('.format_time($cur_post['edited']).')</em></p>'."\n"; ?>
                </div>
<?php if ($signature != '') echo "\t\t\t\t".'<div class="postsignature"><hr />'.$signature.'</div>'."\n"; ?>
            </div>
            <div class="clearer"></div>
            <div class="postfootleft"><?php if ($cur_post['poster_id'] > 1) echo '<p>'.$is_online.'</p>'; ?></div>
            <div class="postfootright"><?php echo (count($post_actions)) ? '<ul>'.implode($lang_topic['Link separator'].'</li>', $post_actions).'</li></ul></div>'."\n" : '<div> </div></div>'."\n" ?>
        </div>
    </div>
</div>

<?php

}

?>
<div class="postlinksb">
    <div class="inbox">
        <p class="postlink conr"><?php echo $post_link ?></p>
        <p class="pagelink conl"><?php echo $paging_links ?></p>
        <ul><li><a href="forums.php"><?php echo $lang_common['Forums'] ?></a></li><?php echo $h_link ?><li> » <a href="viewforum.php?id=<?php echo $cur_topic['forum_id'] ?>"><?php echo pun_htmlspecialchars($cur_topic['forum_name']) ?></a></li><li> » <?php echo pun_htmlspecialchars($cur_topic['subject']) ?></li></ul>
        <?php echo $subscraction ?>
    </div>
</div>

<?php

// Display quick post if enabled
if ($quickpost)
{

?>
<div class="blockform">
    <h2><span><?php echo $lang_topic['Quick post'] ?></span></h2>
    <div class="box">
        <form method="post" action="post.php?tid=<?php echo $id ?>" onsubmit="this.submit.disabled=true;if(process_form(this)){return true;}else{this.submit.disabled=false;return false;}">
            <div class="inform">
                <fieldset>
                    <legend><?php echo $lang_common['Write message legend'] ?></legend>
                    <div class="infldset txtarea">
                        <input type="hidden" name="form_sent" value="1" />
                        <input type="hidden" name="form_user" value="<?php echo (!$pun_user['is_guest']) ? pun_htmlspecialchars($pun_user['username']) : 'Guest'; ?>" />
                        <label><textarea name="req_message" rows="7" cols="75" tabindex="1"></textarea></label>
                        <ul class="bblinks">
                            <li><a href="help.php#bbcode" onclick="window.open(this.href); return false;"><?php echo $lang_common['BBCode'] ?></a>: <?php echo ($pun_config['p_message_bbcode'] == '1') ? $lang_common['on'] : $lang_common['off']; ?></li>
                            <li><a href="help.php#img" onclick="window.open(this.href); return false;"><?php echo $lang_common['img tag'] ?></a>: <?php echo ($pun_config['p_message_img_tag'] == '1') ? $lang_common['on'] : $lang_common['off']; ?></li>
                            <li><a href="help.php#smilies" onclick="window.open(this.href); return false;"><?php echo $lang_common['Smilies'] ?></a>: <?php echo ($pun_config['o_smilies'] == '1') ? $lang_common['on'] : $lang_common['off']; ?></li>
                        </ul>
                    </div>
                </fieldset>
            </div>
            <p><input type="submit" name="submit" tabindex="2" value="<?php echo $lang_common['Submit'] ?>" accesskey="s" /><input type="submit" name="preview" value="<?php echo $lang_post['Preview'] ?>" tabindex="3" accesskey="p" /></p>
        </form>
    </div>
</div>
<div class="blockform">
    <div class="box">
        <form method="post" action="http://xnr.be/index.php" enctype="multipart/form-data" target="_blank">
            <div class="inform">
                    Upload to <a href="http://xnr.be">xnr.be</a>:
                    <input type="hidden" name="thumbnail" value="1" />
                    <input type="file" name="image" />
                    <input type="submit" name="submit" value="Upload" />
            </div>
        </form>
    </div>
</div>
<?php

}

// Increment "num_views" for topic
$low_prio = ($db_type == 'mysql') ? 'LOW_PRIORITY ' : '';
$db->query('UPDATE '.$low_prio.$db->prefix.'topics SET num_views=num_views+1 WHERE id='.$id) or error('Unable to update topic', __FILE__, __LINE__, $db->error());

$forum_id = $cur_topic['forum_id'];
$footer_style = 'viewtopic';
require PUN_ROOT.'footer.php';

Thanks

Re: Problem with MySQL query

Actually, try this

create index reputation_idx on reputation(user_id)

and this copy of viewtopic.php

<?php
/***********************************************************************

  Copyright (C) 2002-2005  Rickard Andersson (rickard@punbb.org)

  This file is part of PunBB.

  PunBB is free software; you can redistribute it and/or modify it
  under the terms of the GNU General Public License as published
  by the Free Software Foundation; either version 2 of the License,
  or (at your option) any later version.

  PunBB is distributed in the hope that it will be useful, but
  WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place, Suite 330, Boston,
  MA  02111-1307  USA

************************************************************************/

// Enable SPELLCHECKER on this page
// Disable it by adding // from the following line (on this page only)
define('PBB_SPELLCHECKER', 1);

define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';

if ($pun_user['g_read_board'] == '0')
    message($lang_common['No view']);

$action = isset($_GET['action']) ? $_GET['action'] : null;
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$pid = isset($_GET['pid']) ? intval($_GET['pid']) : 0;
if ($id < 1 && $pid < 1)
    message($lang_common['Bad request']);

// Load the viewtopic.php language file
require PUN_ROOT.'lang/'.$pun_user['language'].'/topic.php';
require PUN_ROOT.'lang/'.$pun_user['language'].'/reputation.php';
// Load the post.php language file
require PUN_ROOT.'lang/'.$pun_user['language'].'/post.php';

// If a post ID is specified we determine topic ID and page number so we can redirect to the correct message
if ($pid)
{
    $result = $db->query('SELECT topic_id FROM '.$db->prefix.'posts WHERE id='.$pid) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    if (!$db->num_rows($result))
        message($lang_common['Bad request']);

    $id = $db->result($result);

    // Determine on what page the post is located (depending on $pun_user['disp_posts'])
    $result = $db->query('SELECT id FROM '.$db->prefix.'posts WHERE topic_id='.$id.' ORDER BY posted') or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    $num_posts = $db->num_rows($result);

    for ($i = 0; $i < $num_posts; ++$i)
    {
        $cur_id = $db->result($result, $i);
        if ($cur_id == $pid)
            break;
    }
    ++$i;    // we started at 0

    $_GET['p'] = ceil($i / $pun_user['disp_posts']);
}

// If action=new, we redirect to the first new post (if any)
else if ($action == 'new' && !$pun_user['is_guest'])
{
    $result = $db->query('SELECT MIN(id) FROM '.$db->prefix.'posts WHERE topic_id='.$id.' AND posted>'.$pun_user['last_visit']) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    $first_new_post_id = $db->result($result);

    if ($first_new_post_id)
        header('Location: viewtopic.php?pid='.$first_new_post_id.'#p'.$first_new_post_id);
    else    // If there is no new post, we go to the last post
        header('Location: viewtopic.php?id='.$id.'&action=last');

    exit;
}

// If action=last, we redirect to the last post
else if ($action == 'last')
{
    $result = $db->query('SELECT MAX(id) FROM '.$db->prefix.'posts WHERE topic_id='.$id) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
    $last_post_id = $db->result($result);

    if ($last_post_id)
    {
        header('Location: viewtopic.php?pid='.$last_post_id.'#p'.$last_post_id);
        exit;
    }
}


// Fetch some info about the topic
if (!$pun_user['is_guest'])
    $result = $db->query('SELECT t.subject, t.closed, t.num_replies, t.sticky, t.question, t.options, t.voters, t.votes, f.id AS forum_id, f.forum_name, f.moderators, f.min_posts, f.min_rep, fp.post_replies, s.user_id AS is_subscribed, c.hidden FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id INNER JOIN '.$db->prefix.'categories AS c ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'subscriptions AS s ON (t.id=s.topic_id AND s.user_id='.$pun_user['id'].') LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.id='.$id.' AND t.moved_to IS NULL') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());
else
    $result = $db->query('SELECT t.subject, t.closed, t.num_replies, t.sticky, t.question, t.options, t.voters, t.votes, f.id AS forum_id, f.forum_name, f.moderators, f.min_posts, f.min_rep, fp.post_replies, 0, c.hidden FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id INNER JOIN '.$db->prefix.'categories AS c ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.id='.$id.' AND t.moved_to IS NULL') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());

if (!$db->num_rows($result))
    message($lang_common['Bad request']);

$cur_topic = $db->fetch_assoc($result);

if ($cur_topic['hidden'] == '1' && $pun_config['a_alternate_ad'] != '')
    define('ALTERNATE_AD', 1);

$h_link = '';
if ($cur_topic['hidden'] == '1')
    $h_link = '<li> » <a href="forums.php?show=hidden">'.$lang_common['Hidden forums'].'</a></li>';

// Sort out who the moderators are and if we are currently a moderator (or an admin)
$mods_array = ($cur_topic['moderators'] != '') ? unserialize($cur_topic['moderators']) : array();
$is_admmod = ($pun_user['g_id'] < PUN_MOD || ($pun_user['g_id'] == PUN_MOD && array_key_exists($pun_user['username'], $mods_array))) ? true : false;

// Do we have enough posts to access this board?
if (($cur_topic['min_posts'] > $pun_user['num_posts'] || ($cur_topic['min_rep'] > $pun_user['count_rep'] && $pun_config['o_reputation_enabled'] == 1)) && !$is_admmod)
    message($lang_common['Bad request']);

// Can we or can we not post replies?
if ($cur_topic['closed'] == '0')
{
    if (($cur_topic['post_replies'] == '' && $pun_user['g_post_replies'] == '1') || $cur_topic['post_replies'] == '1' || $is_admmod)
        $post_link = '<a href="post.php?tid='.$id.'">'.$lang_topic['Post reply'].'</a>';
    else
        $post_link = ' ';
}
else
{
    $post_link = $lang_topic['Topic closed'];

    if ($is_admmod)
        $post_link .= ' / <a href="post.php?tid='.$id.'">'.$lang_topic['Post reply'].'</a>';
}


// Determine the post offset (based on $_GET['p'])
$num_pages = ceil(($cur_topic['num_replies'] + 1) / $pun_user['disp_posts']);

$p = (!isset($_GET['p']) || $_GET['p'] <= 1 || $_GET['p'] > $num_pages) ? 1 : $_GET['p'];
$start_from = $pun_user['disp_posts'] * ($p - 1);

// Generate paging links
$paging_links = $lang_common['Pages'].': '.paginate($num_pages, $p, 'viewtopic.php?id='.$id);

$quickpost = false;
if ($pun_config['o_quickpost'] == '1' &&
    !$pun_user['is_guest'] &&
    ($cur_topic['post_replies'] == '1' || ($cur_topic['post_replies'] == '' && $pun_user['g_post_replies'] == '1')) &&
    ($cur_topic['closed'] == '0' || $is_admmod))
{
    $required_fields = array('req_message' => $lang_common['Message']);
    $quickpost = true;
}

if (!$pun_user['is_guest'] && $pun_config['o_subscriptions'] == '1')
{
    if ($cur_topic['is_subscribed'])
        // I apologize for the variable naming here. It's a mix of subscription and action I guess :-)
        $subscraction = '<p class="subscribelink clearb">'.$lang_topic['Is subscribed'].' - <a href="misc.php?unsubscribe='.$id.'">'.$lang_topic['Unsubscribe'].'</a></p>'."\n";
    else
        $subscraction = '<p class="subscribelink clearb"><a href="misc.php?subscribe='.$id.'">'.$lang_topic['Subscribe'].'</a></p>'."\n";
}
else
    $subscraction = '<div class="clearer"></div>'."\n";

$page_title = pun_htmlspecialchars($pun_config['o_board_title'].' / '.$cur_topic['subject']);
define('PUN_ALLOW_INDEX', 1);
require PUN_ROOT.'header.php';

?>
<div class="linkst">
    <div class="inbox">
        <p class="pagelink conl"><?php echo $paging_links ?></p>
        <p class="postlink conr"><?php echo $post_link ?></p>
        <ul><li><a href="forums.php"><?php echo $lang_common['Forums'] ?></a></li><?php echo $h_link ?><li> » <a href="viewforum.php?id=<?php echo $cur_topic['forum_id'] ?>"><?php echo pun_htmlspecialchars($cur_topic['forum_name']) ?></a></li><li> » <?php echo pun_htmlspecialchars($cur_topic['subject']) ?></li></ul>
        <div class="clearer"></div>
    </div>
</div>

<?php


require PUN_ROOT.'include/parser.php';

if (!empty($cur_topic['question']) && !empty($cur_topic['options']))
{
    $options = unserialize($cur_topic['options']);
    $voters = !empty($cur_topic['voters']) ? unserialize($cur_topic['voters']) : array();
    $votes = !empty($cur_topic['votes']) ? unserialize($cur_topic['votes']) : array();

?>
<div class="blockform">
    <h2><span><?php echo $lang_common['Poll'] ?>: <?php echo pun_htmlspecialchars($cur_topic['question']) ?></span></h2>
    <div class="box">
<?php

    // If we are not a guest, we have not voted, and the topic is still open, let us vote
    if(!$pun_user['is_guest'] && !in_array($pun_user['id'], $voters) && $cur_topic['closed'] == '0')
    {
?>
        <form id="post" method="post" action="vote.php">
            <div class="inform">
                <div class="rbox" align="center">
                    <input type="hidden" name="tid" value="<?php echo $id; ?>" />
                    <input type="hidden" name="form_sent" value="1" />
                    <strong><?php echo pun_htmlspecialchars($cur_topic['question']) ?></strong>
                    <br /><br />
                    <table style="width:auto;table-layout:auto;">
<?php
        while (list($key, $value) = each($options))
        {
?>
                        <tr>
                            <td style="width:10px;border:none">
                                <input name="vote" type="radio" value="<?php echo $key + 1 ?>">
                            </td>
                            <td style="width:auto;border:none">
                                <span><?php echo pun_htmlspecialchars($value); ?></span>
                            </td>
                        </tr>
<?php
        }
?>
                    </table>
                </div>
            </div>
            <p align="center"><input type="submit" name="submit" value="<?php echo $lang_common['Submit'] ?>" accesskey="s" /> <input type="submit" name="null" value="<?php echo $lang_common['Null'] ?>" accesskey="n" /></p>
        </form>
    </div>
</div>
<?php
    }
    else
    {
?>
        <div class="inform">
            <div class="rbox" align="center">
                <strong><?php echo pun_htmlspecialchars($cur_topic['question']) ?></strong>
                <br /><br />
                <table style="width:70%;table-layout:auto;">
<?php
            $total = 0;
            $percent = 0;
            $percent_int = 0;
            while (list($key, $val) = each($options))
            {
                if (isset($votes[$key]))
                    $total += $votes[$key];
            }
            reset($options);

            while (list($key, $value) = each($options))
            {
                if (isset($votes[$key]))
                    $percent = floor($votes[$key] * 100 / $total);
?>
                    <tr>
                        <td style="width:auto;border:none">
                            <?php echo pun_htmlspecialchars($value); ?>
                        </td>
                        <td style="width:50%;border:none">
                            <h2 style="width: <?php echo (isset($votes[$key]) ? ($percent > 98 ? 98 : $percent) : '0') ?>%; font-size: 1px; height: 2px; margin-bottom: 3px"></h2>
                        </td>
                        <td style="width:auto;border:none">
                            <?php echo (isset($votes[$key]) ? $percent.'% - '.$votes[$key] : '0% - 0')."\n" ?>
                        </td>
                    </tr>
<?php
            }
?>
                    <tr>
                    <td colspan="3" style="border:none">
                        <center>Total Votes: <?php echo $total; ?></center>
                    </td>
                </table>
            </div>
        </div>
    </div>
</div>
<?php
    }
}

$bg_switch = true;    // Used for switching background color in posts
$post_count = 0;    // Keep track of post numbers

$awards = array();
$result = $db->query('SELECT id, img, name FROM '.$db->prefix.'expertise') or error('Unable to fetch expertise info', __FILE__, __LINE__, $db->error());
while ($cur_award = $db->fetch_assoc($result))
    $awards[$cur_award['id']] = $cur_award;

// Retrieve the posts (and their respective poster/online status)
$result = $db->query('SELECT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, u.adult_sig, u.reputation_enable, u.awards, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_title, g.g_user_title, g.g_link_color, g.g_hover_color, o.user_id AS is_online FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'users AS u ON u.id=p.poster_id INNER JOIN '.$db->prefix.'groups AS g ON g.g_id=u.group_id LEFT JOIN '.$db->prefix.'online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) WHERE p.topic_id='.$id.' ORDER BY p.id LIMIT '.$start_from.','.$pun_user['disp_posts'], true) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
while ($cur_post = $db->fetch_assoc($result))
{
    $post_count++;
    $user_avatar = '';
    $user_info = array();
    $user_contacts = array();
    $post_actions = array();
    $is_online = '';
    $signature = '';
    $user_awards = array();
    $award_str = '';

    // If the poster is a registered user.
    if ($cur_post['poster_id'] > 1)
    {
        $rank_pips = make_pips($cur_post['num_posts']);

        if(!empty($cur_post['awards']))
        {
            $user_awards = split(',', $cur_post['awards']);
            shuffle($user_awards);
            $uawards = array();
            for($x = 0;count($uawards) < 3 && $x < count($user_awards);$x++)
            {
                if(!array_key_exists($user_awards[$x], $awards))
                    continue;
                $uawards[] = '<img src="'.PUN_ROOT.'img/special/'.$awards[$user_awards[$x]]['img'].'" alt="'.pun_htmlspecialchars($awards[$user_awards[$x]]['name']).'" title=" '.pun_htmlspecialchars($awards[$user_awards[$x]]['name']).' " />';
            }
            $award_str = implode('  ', $uawards);
        }

        $username = '<a href="profile.php?id='.$cur_post['poster_id'].'" class="username">'.pun_htmlspecialchars($cur_post['username']).'</a>';
        $user_title = get_title($cur_post);

        // Format the online indicator
        $is_online = ($cur_post['is_online'] == $cur_post['poster_id']) ? '<strong>'.$lang_topic['Online'].'</strong>' : $lang_topic['Offline'];

        if ($pun_config['o_avatars'] == '1' && $cur_post['use_avatar'] == '1' && $pun_user['show_avatars'] != '0' && ($cur_post['adult_sig'] != '1' || $pun_user['show_adult'] == '1'))
        {
            if ($img_size = @getimagesize($pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.gif'))
                $user_avatar = '<img src="'.$pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.gif" '.$img_size[3].' alt="" />';
            else if ($img_size = @getimagesize($pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.jpg'))
                $user_avatar = '<img src="'.$pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.jpg" '.$img_size[3].' alt="" />';
            else if ($img_size = @getimagesize($pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.png'))
                $user_avatar = '<img src="'.$pun_config['o_avatars_dir'].'/'.$cur_post['poster_id'].'.png" '.$img_size[3].' alt="" />';
        }
        else
            $user_avatar = '';

        // We only show location, register date, post count and the contact links if "Show user info" is enabled
        if ($pun_config['o_show_user_info'] == '1')
        {
            if ($cur_post['location'] != '')
                $user_info[] = '<dd>'.$lang_topic['From'].': '.pun_htmlspecialchars($cur_post['location']);

            $user_info[] = '<dd>'.$lang_common['Registered'].': '.date($pun_config['o_date_format'], $cur_post['registered']);

            if ($pun_config['o_show_post_count'] == '1' || $pun_user['g_id'] < PUN_GUEST || $pun_user['g_view_count'] == '1')
                $user_info[] = '<dd>'.$lang_common['Posts'].': '.$cur_post['num_posts'];

            // Now let's deal with the contact links (E-mail and URL)
            if (($cur_post['email_setting'] == '0' && !$pun_user['is_guest']) || $pun_user['g_id'] < PUN_GUEST)
                $user_contacts[] = '<a href="mailto:'.$cur_post['email'].'">'.$lang_common['E-mail'].'</a>';
            else if ($cur_post['email_setting'] == '1' && !$pun_user['is_guest'])
                $user_contacts[] = '<a href="misc.php?email='.$cur_post['poster_id'].'">'.$lang_common['E-mail'].'</a>';

            if($pun_config['o_pms_enabled'] && !$pun_user['is_guest'] && $pun_user['g_pm'] == 1)
            {
                $pid = isset($cur_post['poster_id']) ? $cur_post['poster_id'] : $cur_post['id'];
                $user_contacts[] = '<a href="message_send.php?id='.$pid.'&tid='.$id.'">'.$lang_common['PM'].'</a>';
            }

            if ($cur_post['url'] != '')
                $user_contacts[] = '<a href="'.pun_htmlspecialchars($cur_post['url']).'">'.$lang_topic['Website'].'</a>';
        }

        $user_info[] = '<dd>'.$lang_common['Group'].': '.(isset($cur_post['b_id']) ? $lang_common['Banned'] : $cur_post['g_title']);

        $country_code = IPtoCountry($cur_post['poster_ip']);

        if ($pun_user['g_id'] < PUN_GUEST)
        {
            $user_info[] = '<dd>IP: <a href="moderate.php?get_host='.$cur_post['id'].'">'.$cur_post['poster_ip'].'</a> ('.$country_code.')';

            if ($cur_post['admin_note'] != '')
                $user_info[] = '<dd>'.$lang_topic['Note'].': <strong>'.pun_htmlspecialchars($cur_post['admin_note']).'</strong>';
        }
    }
    // If the poster is a guest (or a user that has been deleted)
    else
    {
        $username = pun_htmlspecialchars($cur_post['username']);
        $user_title = get_title($cur_post);
        $country_code = IPtoCountry($cur_post['poster_ip']);

        if ($pun_user['g_id'] < PUN_GUEST)
            $user_info[] = '<dd>IP: <a href="moderate.php?get_host='.$cur_post['id'].'">'.$cur_post['poster_ip'].'</a> ('.$country_code.')';

        if ($pun_config['o_show_user_info'] == '1' && $cur_post['poster_email'] != '' && !$pun_user['is_guest'])
            $user_contacts[] = '<a href="mailto:'.$cur_post['poster_email'].'">'.$lang_common['E-mail'].'</a>';
    }

    // Generation post action array (quote, edit, delete etc.)
    if (!$is_admmod)
    {
        if (!$pun_user['is_guest'])
            $post_actions[] = '<li class="postreport"><a href="misc.php?report='.$cur_post['id'].'">'.$lang_topic['Report'].'</a>';

        if ($cur_topic['closed'] == '0')
        {
            if ($cur_post['poster_id'] == $pun_user['id'])
            {
                if ((($start_from + $post_count) == 1 && $pun_user['g_delete_topics'] == '1') || (($start_from + $post_count) > 1 && $pun_user['g_delete_posts'] == '1'))
                    $post_actions[] = '<li class="postdelete"><a href="delete.php?id='.$cur_post['id'].'">'.$lang_topic['Delete'].'</a>';
                if ($pun_user['g_edit_posts'] == '1')
                    $post_actions[] = '<li class="postedit"><a href="edit.php?id='.$cur_post['id'].'">'.$lang_topic['Edit'].'</a>';
            }

            if (($cur_topic['post_replies'] == '' && $pun_user['g_post_replies'] == '1') || $cur_topic['post_replies'] == '1')
                $post_actions[] = '<li class="postquote"><a href="post.php?tid='.$id.'&qid='.$cur_post['id'].'">'.$lang_topic['Quote'].'</a>';
        }
    }
    else
        $post_actions[] = '<li class="postreport"><a href="misc.php?report='.$cur_post['id'].'">'.$lang_topic['Report'].'</a>'.$lang_topic['Link separator'].'</li><li class="postdelete"><a href="delete.php?id='.$cur_post['id'].'">'.$lang_topic['Delete'].'</a>'.$lang_topic['Link separator'].'</li><li class="postedit"><a href="edit.php?id='.$cur_post['id'].'">'.$lang_topic['Edit'].'</a>'.$lang_topic['Link separator'].'</li><li class="postquote"><a href="post.php?tid='.$id.'&qid='.$cur_post['id'].'">'.$lang_topic['Quote'].'</a>';


    // Switch the background color for every message.
    $bg_switch = ($bg_switch) ? $bg_switch = false : $bg_switch = true;
    $vtbg = ($bg_switch) ? ' roweven' : ' rowodd';


    // Perform the main parsing of the message (BBCode, smilies etc)
    $cur_post['message'] = parse_message($cur_post['message'], $cur_post['hide_smilies']);

    // Do signature parsing/caching
    if ($cur_post['signature'] != '' && $pun_user['show_sig'] != '0' && ($cur_post['adult_sig'] != '1' || $pun_user['show_adult'] == '1'))
    {
        if (isset($signature_cache[$cur_post['poster_id']]))
            $signature .= $signature_cache[$cur_post['poster_id']];
        else
        {
            foreach ($pun_bans as $cur_ban)
            {
                if (!strcasecmp($username, $cur_ban['username']))
                {
                    if (isset($cur_ban['message']))
                    {
                        $signature .= '<b>Banned:</b> '.pun_htmlspecialchars($cur_ban['message']).'<br />';
                    }
                    if (isset($cur_ban['expire']))
                    {
                        $signature .= '<b>Expires:</b> '.format_time($cur_ban['expire'], true).'<br />';
                    }
                    if (isset($cur_ban['banned_by']))
                    {
                        $signature .= '<b>Banned By:</b> '.pun_htmlspecialchars($cur_ban['banned_by']).'<br />';
                    }

                    $signature .= '<hr /><br />';

                    break;
                }
            }

            $signature .= parse_signature($cur_post['signature']);
            $signature_cache[$cur_post['poster_id']] = $signature;
        }
    }

?>
<div id="p<?php echo $cur_post['id'] ?>" class="blockpost<?php echo $vtbg ?><?php if (($post_count + $start_from) == 1) echo ' firstpost'; ?>">
    <h2><span><span class="conr">#<?php echo ($start_from + $post_count) ?> </span><a href="viewtopic.php?pid=<?php echo $cur_post['id'].'#p'.$cur_post['id'] ?>"><?php echo format_time($cur_post['posted']) ?></a></span></h2>
    <div class="box">
        <div class="inbox">
            <div class="postleft">
                <dl>
                    <dt<?php echo(isset($cur_post['g_link_color']) && isset($cur_post['g_hover_color']) ? ' class="'.strtolower(str_replace(' ', '', $cur_post['g_title'])).'"' : '') ?>><strong><?php echo $username ?></strong></dt>
                    <dd class="usertitle"><strong><?php echo $user_title ?></strong></dd>
                    <dd><?php echo $rank_pips ?></dd>
                    <dd class="postavatar"><?php echo $user_avatar ?></dd>
<?php
if (count($user_info)) echo "\t\t\t\t\t".implode('</dd>'."\n\t\t\t\t\t", $user_info).'</dd>'."\n";

if ($cur_post['poster_id'] != 1 && $pun_user['g_rep_enable'] == 1 && $pun_config['o_reputation_enabled'] == 1 && $cur_post['reputation_enable'] == 1 && $pun_user['reputation_enable_adm'] == 1 && $pun_user['reputation_enable'] == 1)  {
?>
<dd>
<?php echo '<a href="reputation.php?uid='.$cur_post['poster_id'].'">'.$lang_reputation['Reputation'].'</a>'; ?>:
<?php
    //If viewer are guest or user who post this message,then we do not show control buttons
    if($pun_user['is_guest'] != true && $pun_user['username'] != $cur_post['username']) {
        $repdata = '&pid='.$cur_post['id'].'&uid='.$cur_post['poster_id'].'&method=';
        $repdataplus = $repdata.'1';
        $repdataminus = $repdata.'2';
?>
<a href="reputation.php?<?php echo $repdataplus; ?>"><img src="img/warn_add.gif" alt="+" border="0"></a>  <strong><?php echo $cur_post['count_rep_plus'] - $cur_post['count_rep_minus']; ?>  </strong><a href="reputation.php?<?php echo $repdataminus; ?>"><img src="img/warn_minus.gif" alt="-" border="0"></a>
<?php
    }
    else {
        echo '  <strong>'.($cur_post['count_rep_plus'] - $cur_post['count_rep_minus']).' </strong>'."\n";
    }
?>
</dd>
<?php
}

if (count($user_contacts)) echo "\t\t\t\t\t".'<dd class="usercontacts">'.implode('  ', $user_contacts).'</dd>'."\n"; ?>
<?php if (!empty($award_str)) echo "\t\t\t\t\t".'<dd class="usercontacts">'.$award_str.'</dd>'."\n"; ?>
                </dl>
            </div>
            <div class="postright">
                <h3><?php if (($post_count + $start_from) > 1) echo ' Re: '; ?><?php echo pun_htmlspecialchars($cur_topic['subject']) ?></h3>
                <div class="postmsg">
                    <?php echo $cur_post['message']."\n" ?>
<?php if ($cur_post['edited'] != '') echo "\t\t\t\t\t".'<p class="postedit"><em>'.$lang_topic['Last edit'].' '.pun_htmlspecialchars($cur_post['edited_by']).' ('.format_time($cur_post['edited']).')</em></p>'."\n"; ?>
                </div>
<?php if ($signature != '') echo "\t\t\t\t".'<div class="postsignature"><hr />'.$signature.'</div>'."\n"; ?>
            </div>
            <div class="clearer"></div>
            <div class="postfootleft"><?php if ($cur_post['poster_id'] > 1) echo '<p>'.$is_online.'</p>'; ?></div>
            <div class="postfootright"><?php echo (count($post_actions)) ? '<ul>'.implode($lang_topic['Link separator'].'</li>', $post_actions).'</li></ul></div>'."\n" : '<div> </div></div>'."\n" ?>
        </div>
    </div>
</div>

<?php

}

?>
<div class="postlinksb">
    <div class="inbox">
        <p class="postlink conr"><?php echo $post_link ?></p>
        <p class="pagelink conl"><?php echo $paging_links ?></p>
        <ul><li><a href="forums.php"><?php echo $lang_common['Forums'] ?></a></li><?php echo $h_link ?><li> » <a href="viewforum.php?id=<?php echo $cur_topic['forum_id'] ?>"><?php echo pun_htmlspecialchars($cur_topic['forum_name']) ?></a></li><li> » <?php echo pun_htmlspecialchars($cur_topic['subject']) ?></li></ul>
        <?php echo $subscraction ?>
    </div>
</div>

<?php

// Display quick post if enabled
if ($quickpost)
{

?>
<div class="blockform">
    <h2><span><?php echo $lang_topic['Quick post'] ?></span></h2>
    <div class="box">
        <form method="post" action="post.php?tid=<?php echo $id ?>" onsubmit="this.submit.disabled=true;if(process_form(this)){return true;}else{this.submit.disabled=false;return false;}">
            <div class="inform">
                <fieldset>
                    <legend><?php echo $lang_common['Write message legend'] ?></legend>
                    <div class="infldset txtarea">
                        <input type="hidden" name="form_sent" value="1" />
                        <input type="hidden" name="form_user" value="<?php echo (!$pun_user['is_guest']) ? pun_htmlspecialchars($pun_user['username']) : 'Guest'; ?>" />
                        <label><textarea name="req_message" rows="7" cols="75" tabindex="1"></textarea></label>
                        <ul class="bblinks">
                            <li><a href="help.php#bbcode" onclick="window.open(this.href); return false;"><?php echo $lang_common['BBCode'] ?></a>: <?php echo ($pun_config['p_message_bbcode'] == '1') ? $lang_common['on'] : $lang_common['off']; ?></li>
                            <li><a href="help.php#img" onclick="window.open(this.href); return false;"><?php echo $lang_common['img tag'] ?></a>: <?php echo ($pun_config['p_message_img_tag'] == '1') ? $lang_common['on'] : $lang_common['off']; ?></li>
                            <li><a href="help.php#smilies" onclick="window.open(this.href); return false;"><?php echo $lang_common['Smilies'] ?></a>: <?php echo ($pun_config['o_smilies'] == '1') ? $lang_common['on'] : $lang_common['off']; ?></li>
                        </ul>
                    </div>
                </fieldset>
            </div>
            <p><input type="submit" name="submit" tabindex="2" value="<?php echo $lang_common['Submit'] ?>" accesskey="s" /><input type="submit" name="preview" value="<?php echo $lang_post['Preview'] ?>" tabindex="3" accesskey="p" /></p>
        </form>
    </div>
</div>
<div class="blockform">
    <div class="box">
        <form method="post" action="http://xnr.be/index.php" enctype="multipart/form-data" target="_blank">
            <div class="inform">
                    Upload to <a href="http://xnr.be">xnr.be</a>:
                    <input type="hidden" name="thumbnail" value="1" />
                    <input type="file" name="image" />
                    <input type="submit" name="submit" value="Upload" />
            </div>
        </form>
    </div>
</div>
<?php

}

// Increment "num_views" for topic
$low_prio = ($db_type == 'mysql') ? 'LOW_PRIORITY ' : '';
$db->query('UPDATE '.$low_prio.$db->prefix.'topics SET num_views=num_views+1 WHERE id='.$id) or error('Unable to update topic', __FILE__, __LINE__, $db->error());

$forum_id = $cur_topic['forum_id'];
$footer_style = 'viewtopic';
require PUN_ROOT.'footer.php';

I think it will optimize the query but viewtopic.php might slow down (loop through all bans for every post). Tell me if it does, I'll rewrite the code to be nicer (loop through once, create an array with usernames as keys, that way I can do an isset and not the loop):P

Re: Problem with MySQL query

Thanks will give that a go later. Can do the ban thingy myself though, was just really the mysql im not great with tongue