1 Topic by Vatii (edited by Vatii 2008-06-10 13:14)

  • Vatii
  • Junior Member
  • Offline
  • Registered: 2008-06-10
  • Posts: 2

Topic: Problem trying to run an independent query in viewtopic.php

Hello everyone, I was wondering if it's possible to run a query as a post is being processed in viewtopic.php. I'm using 1.2.17.

I'm try to run the following query just before the html markup to retrieve several variables to change the way a post appears when processed in the html:

        $pf_result = $db->query('SELECT top, bottom, body, tail FROM '.$db->prefix.'post_format WHERE user_id='.$cur_post['poster_id']) or error('Unable to read from post format', __FILE__, __LINE__, $db->error());
        if ($db->num_rows($pf_result)) {
            list($pf_top, $pf_bottom, $pf_body, $pf_tail) = $db->fetch_row($pf_result);
        } else {
            $pf_top = "default-top.png";
            $pf_bottom = "default-bottom.png";
            $pf_body = "default-body.png";
            $pf_tail = "default-tail.png";
        }

However when the code is processed, the first post would be displayed and replys after it will not be processed or be displayed at all. I was thinking that maybe the query needed to be included into that massive post retrieval query in order for this to work yet sadly my mysql knowledge is too limited to know how joins work.

// Retrieve the posts (and their respective poster/online status)
$result = $db->query('SELECT u.id, u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, 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_user_title, 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());

I was wondering if there is any way the method I tried will ever work? And if not can someone please explain to me how to use joins to add the query to the post retrieval one as well as showing an example. That way I'll be able to add more in if the need arises again in the future. Thanks!

2 Reply by Vatii

  • Vatii
  • Junior Member
  • Offline
  • Registered: 2008-06-10
  • Posts: 2

Re: Problem trying to run an independent query in viewtopic.php

Nevermind, I managed to figure it out and fix it myself. The query indeed had to be integrated with the post retrieval query as follows:

// Retrieve the posts (and their respective poster/online status)
$result = $db->query('SELECT pf.top, pf.bottom, pf.body, pf.tail, u.id, u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, 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_user_title, o.user_id AS is_online FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'post_format AS pf ON pf.user_id=p.poster_id 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());