Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trying to display a certain post from phpbb

    I'm trying to display the first post of the newest topic in a phpbb forum (the forum is specific, only one forum: [82].

    The sql I'm looking at is here:

    PHP Code:
    $sql 'SELECT p.post_id, p.topic_id, p.post_subject, p.post_text, p.post_time, u.username
            FROM ' 
    POSTS_TABLE ' p
                INNER JOIN ' 
    USERS_TABLE ' u on (p.poster_id = u.user_id)
                    WHERE p.post_approved = 1
                        AND ' 
    $db->sql_in_set('p.forum_id'$authed_news_ary) . ' GROUP BY topic_id
                    ORDER BY 1 DESC'
    ;

        
    $result $db->sql_query_limit($sql$num_posts);
        
    $row $db->sql_fetchrowset($result); 
    The part that is probably screwy (is) is:
    PHP Code:
    AND ' . $db->sql_in_set('p.forum_id', $authed_news_ary) . ' GROUP BY topic_id
                    ORDER BY 1 DESC

    Can it even be done with normal statements or is more php required? The first post = the first post of any topic that is posted in the forum. Not the comments on the post thereafter

    Source:

    PHP Code:
    <!-- News -->
    <div class="subheader">
    <div class="title">Recent Clan News - 
    <?
    // The below is not my function, I only configured it, then modified it
    $forum_ids = array(82);        //Forum ID(s) to pull posts from
    $num_posts 1;        //Number of posts to display
    $num_chars 500;    //Number of characters to show in the post text

    //
    // Trim text to a certain length
    //
    function phpbb_trim_text(&$text, &$is_trimmed$number)
    {
        if (
    $number != and strlen($text) > $number)
        {
            
    $text       substr($text0$number);
            
    $is_trimmed true;
        }

        return 
    true;
    }

    //
    // Auth
    //
    $can_read_forum $auth->acl_getf('f_read');    //Get the forums the user can read from
    //Rework the array some
    $forum_id_ary = array();
    foreach(
    $can_read_forum as $key => $forum)
    {
        if(
    $forum['f_read'] != 0)
        {
            
    $forum_id_ary[] = $key;
        }
    }
    $authed_news_ary = array();

    //Of the desired forums, pull out the authed ones
    if(!sizeof($forum_ids))
    {
        
    $authed_news_ary $forum_id_ary;
    }
    else
    {
        
    $authed_news_ary array_intersect($forum_ids$forum_id_ary);
    }

    unset(
    $can_read_forum);

    //
    // Recent post
    //

    if(sizeof($authed_news_ary))
    {
        
    $sql 'SELECT p.post_id, p.topic_id, p.post_subject, p.post_text, p.post_time, u.username
            FROM ' 
    POSTS_TABLE ' p
                INNER JOIN ' 
    USERS_TABLE ' u on (p.poster_id = u.user_id)
                    WHERE p.post_approved = 1
                        AND ' 
    $db->sql_in_set('p.forum_id'$authed_news_ary) . ' GROUP BY topic_id
                    ORDER BY topic_id DESC'
    ;

        
    $result $db->sql_query_limit($sql$num_posts);
        
    $row $db->sql_fetchrowset($result);

        for(
    $i 0$i sizeof($row); $i++)
        {
            
    $post_url "http://forum.dgclan.com/redirect/?location=viewtopic.php?t=" $row[$i]['topic_id'] . "&amp;p=" $row[$i]['post_id'];


                    
    //Prepare text (strip bbcodes and trim it)
            
    $row[$i]['post_trimmed'] = false;
            
    $change $row[$i]['post_text'];
            
    strip_bbcode($change);
            
    $one str_replace("{b}","<br />",$change);
            
    $two str_replace("{strong}","<b>",$one);
            
    $three str_replace("{/strong}","</b>",$two);
      
            
    phpbb_trim_text($change$row[$i]['post_trimmed'], $num_chars);

            
    $post_text '';
            if (
    $row[$i]['post_trimmed'])
            {
                
    $post_text $three '...' '<br /><br />[ <a href="' $post_url '">Read Full</a> ]';
            }
            else
            {
                
    $post_text $three;
            }

            echo 
    '<a href="' $post_url '">' $row[$i]['post_subject'] . '</a></div><div class="date"></div></div><div class="newsarea"> <font color="SlateGray" style="padding-bottom:2px;">Posted by: ' $row[$i]['username'] . ' - ' $user->format_date($row[$i]['post_time']) . ' </font><br /><div style="background-color:#1A1A1A;border: 1px solid #3C3C3C;width:92%; padding:6px;"><font color="#D3D3D3">' $post_text '</font></div></td>
                    <br />'
    ;
        }

        
    $db->sql_freeresult($result);
    }
    else
    {
        echo 
    'No posts to display<br />';
    }
    ?>
    Last edited by yaMz; 10-12-2011 at 02:12 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    DEBUG DEBUG DEBUG.

    Start by doing this:
    Code:
    ...
    $sql = 'SELECT p.post_id, p.topic_id, p.post_subject, p.post_text, p.post_time, u.username
            FROM ' . POSTS_TABLE . ' p
                INNER JOIN ' . USERS_TABLE . ' u on (p.poster_id = u.user_id)
                    WHERE p.post_approved = 1
                        AND ' . $db->sql_in_set('p.forum_id', $authed_news_ary) . ' GROUP BY topic_id
                    ORDER BY 1 DESC';
    
    echo "<hr>DEBUG SQL: " . $sql . "<hr>\n";
    ...
    Look at that query as it is being present to MySQL. Is it legal? Does it make sense?

    But if you only want *ONE* post, then why are you asking for *ALL* of them? And then you do a loop on all of the found posts, so you will effectively be finding only the *last* one.

    At a minimum, I think you need a LIMIT 1 tacked onto the end of that query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And why do you have a GROUP BY clause in the SQL when you have no aggregate functions in the SELECT list?

    Finally, what is sql_in_set? I don't find that in the PHP docs.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ^ phpBB DBAL

    I appreciate the criticism and structure. I'll fixer up.

    Edit: Limit 1 is already tagged onto the query.
    $result = $db->sql_query_limit($sql, $num_posts);

    where $num_posts is 1
    Last edited by yaMz; 10-12-2011 at 12:54 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by yaMz View Post
    Limit 1 is already tagged onto the query.

    $result = $db->sql_query_limit($sql, $num_posts);

    where $num_posts is 1
    Might not be as efficient as putting the LIMIT 1 into the query. That simply prevents the PHP client from reading more than one result. Putting LIMIT 1 in the query allows MySQL to sometimes be much more efficient, esp. if the ORDER BY is based on an indexed column.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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