Please help On Moderation Queue Permission Issue

Discussion forum for MOD Writers regarding MOD Development.
Locked
Seraphic
Registered User
Posts: 274
Joined: Wed Feb 15, 2006 8:20 am

Please help On Moderation Queue Permission Issue

Post by Seraphic »

Hi,

I have a forum on my forums that is linked to my website and it allows Registered Users and myself to post information and it will appear on the main website as news. I'm using the below script and it is php included into my website, it works without issue. However, a registered user just posted and it appeared on the website BEFORE I had a chance to approve/validate the post. This is quite bad for obvious reasons. The odd thing is, however, I made a test post with user permissions and it did not so up until it was validated.

Could anyone help edit the below script so only posts that have been approved with be displayed? (admin/mod posts don't count for this)

Thanks

Code: Select all

<?php
//-- Fetch the data from the specified fora
$bbcode = new bbcode();
$news_fora_id = array( '2', '3');
$output = '';
$topic_count = '2';
$query = "
    SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.bbcode_bitfield, p.bbcode_uid,
        u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
            (
                SELECT COUNT( post_id )
                FROM " . POSTS_TABLE . "
                    WHERE topic_id = p.topic_id
            ) AS aantal_posts
    FROM " . POSTS_TABLE . " AS p, " . USERS_TABLE . " AS u
        WHERE " . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . "
            AND u.user_id = p.poster_id
    GROUP BY topic_id
    ORDER BY topic_id DESC
    LIMIT 0, {$topic_count}
";
//die('<pre>' . $query );
$result = $db->sql_query( $query );
while( $row = $db->sql_fetchrow($result) )
{
    // Parse the message and subject
    $message = censor_text($row['post_text']);

    // Second parse bbcode here
    if ($row['bbcode_bitfield'])
    {
        $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
    }

    $message = bbcode_nl2br($message);
    $message = smiley_text($message);
    
    // Send data to output var
    $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
    $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
    $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
    $output .= "<br><p>\n\t";
    $output .= $message;
    $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
}

// print the output
print( $output );
?>

xhotshot23x
Registered User
Posts: 83
Joined: Wed Oct 10, 2007 4:31 am

Re: Please help On Moderation Queue Permission Issue

Post by xhotshot23x »

Hello,

I made several changes to your code, some for improvement and some to comply a little better with phpBB coding guidelines. I would like to point out some of the changes I made and hope you can learn from these,

1) The DB query it self was just about compeltly, I deleted the part about selecting the post_count because you can just select that filed from the posts table. Also to do with the query, I got rid of the double-quotes and replaced the with single to comply with the coding guide-lines.

2) At the end of the while loop you were missing a $db->sql_freeresult(), I added that and for the future you want to always make sure you remember to free a result when you are done using that data or have stored it in an array.



I have not tested this code however it should work fine, hope this helped.

A better version of the query, code could still be improved by using the generate_text_for_display() function inside of function_content which is include when you use the phpBB common file.

Code: Select all

<?php
    //-- Fetch the data from the specified fora
    $bbcode = new bbcode();
    $news_fora_id = array('2', '3');
    $output = '';
    $topic_count = '2';
    $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.topic_views, p.bbcode_bitfield, p.bbcode_uid, p.post_approved
                       u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
              FROM ' . POSTS_TABLE . ' AS p, ' . USERS_TABLE . ' AS u
              WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                AND u.user_id = p.poster_id
                    AND p.post_approved = 1
             GROUP BY p.topic_id
             ORDER BY p.topic_id DESC
                LIMIT 0, ' . $topic_count;
   $result = $db->sql_query($sql);
   
   while( $row = $db->sql_fetchrow($result) )
    {
        // Parse the message and subject
        $message = censor_text($row['post_text']);

        // Second parse bbcode here
        if ($row['bbcode_bitfield'])
        {
            $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
        }

        $message = bbcode_nl2br($message);
        $message = smiley_text($message);
       
        // Send data to output var
        $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
        $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
        $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
        $output .= "<br><p>\n\t";
        $output .= $message;
        $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
    }
    
    // Never forget to free a result!
    $db->sql_freeresult($result);
    // print the output
    print( $output );
?>
Image

Seraphic
Registered User
Posts: 274
Joined: Wed Feb 15, 2006 8:20 am

Re: Please help On Moderation Queue Permission Issue

Post by Seraphic »

xhotshot23x wrote:Hello,

I made several changes to your code, some for improvement and some to comply a little better with phpBB coding guidelines. I would like to point out some of the changes I made and hope you can learn from these,

1) The DB query it self was just about compeltly, I deleted the part about selecting the post_count because you can just select that filed from the posts table. Also to do with the query, I got rid of the double-quotes and replaced the with single to comply with the coding guide-lines.

2) At the end of the while loop you were missing a $db->sql_freeresult(), I added that and for the future you want to always make sure you remember to free a result when you are done using that data or have stored it in an array.



I have not tested this code however it should work fine, hope this helped.

A better version of the query, code could still be improved by using the generate_text_for_display() function inside of function_content which is include when you use the phpBB common file.

Code: Select all

<?php
    //-- Fetch the data from the specified fora
    $bbcode = new bbcode();
    $news_fora_id = array('2', '3');
    $output = '';
    $topic_count = '2';
    $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.topic_views, p.bbcode_bitfield, p.bbcode_uid, p.post_approved
                       u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
              FROM ' . POSTS_TABLE . ' AS p, ' . USERS_TABLE . ' AS u
              WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                AND u.user_id = p.poster_id
                    AND p.post_approved = 1
             GROUP BY p.topic_id
             ORDER BY p.topic_id DESC
                LIMIT 0, ' . $topic_count;
   $result = $db->sql_query($sql);
   
   while( $row = $db->sql_fetchrow($result) )
    {
        // Parse the message and subject
        $message = censor_text($row['post_text']);

        // Second parse bbcode here
        if ($row['bbcode_bitfield'])
        {
            $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
        }

        $message = bbcode_nl2br($message);
        $message = smiley_text($message);
       
        // Send data to output var
        $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
        $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
        $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
        $output .= "<br><p>\n\t";
        $output .= $message;
        $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
    }
    
    // Never forget to free a result!
    $db->sql_freeresult($result);
    // print the output
    print( $output );
?>
Hi,

Thanks for the help.

Moved your code into the file, but it does not display anything (no errors, just nothing) when I check the website that calls the file.

xhotshot23x
Registered User
Posts: 83
Joined: Wed Oct 10, 2007 4:31 am

Re: Please help On Moderation Queue Permission Issue

Post by xhotshot23x »

Sorry, try this, I set the query to run the string $sql when it should be $query.

Code: Select all

//-- Fetch the data from the specified fora
    $bbcode = new bbcode();
    $news_fora_id = array('2', '3');
    $output = '';
    $topic_count = '2';
    $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.topic_views, p.bbcode_bitfield, p.bbcode_uid, p.post_approved
                       u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
              FROM ' . POSTS_TABLE . ' AS p, ' . USERS_TABLE . ' AS u
              WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                AND u.user_id = p.poster_id
                    AND p.post_approved = 1
             GROUP BY p.topic_id
             ORDER BY p.topic_id DESC
                LIMIT 0, ' . $topic_count;
   $result = $db->sql_query($query);
   
   while( $row = $db->sql_fetchrow($result) )
    {
        // Parse the message and subject
        $message = censor_text($row['post_text']);

        // Second parse bbcode here
        if ($row['bbcode_bitfield'])
        {
            $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
        }

        $message = bbcode_nl2br($message);
        $message = smiley_text($message);
       
        // Send data to output var
        $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
        $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
        $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
        $output .= "<br><p>\n\t";
        $output .= $message;
        $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
    }
    
    // Never forget to free a result!
    $db->sql_freeresult($result);
    // print the output
    print( $output );

 
Image

Seraphic
Registered User
Posts: 274
Joined: Wed Feb 15, 2006 8:20 am

Re: Please help On Moderation Queue Permission Issue

Post by Seraphic »

xhotshot23x wrote:Sorry, try this, I set the query to run the string $sql when it should be $query.

Code: Select all

//-- Fetch the data from the specified fora
    $bbcode = new bbcode();
    $news_fora_id = array('2', '3');
    $output = '';
    $topic_count = '2';
    $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.topic_views, p.bbcode_bitfield, p.bbcode_uid, p.post_approved
                       u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
              FROM ' . POSTS_TABLE . ' AS p, ' . USERS_TABLE . ' AS u
              WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                AND u.user_id = p.poster_id
                    AND p.post_approved = 1
             GROUP BY p.topic_id
             ORDER BY p.topic_id DESC
                LIMIT 0, ' . $topic_count;
   $result = $db->sql_query($query);
   
   while( $row = $db->sql_fetchrow($result) )
    {
        // Parse the message and subject
        $message = censor_text($row['post_text']);

        // Second parse bbcode here
        if ($row['bbcode_bitfield'])
        {
            $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
        }

        $message = bbcode_nl2br($message);
        $message = smiley_text($message);
       
        // Send data to output var
        $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
        $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
        $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
        $output .= "<br><p>\n\t";
        $output .= $message;
        $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
    }
    
    // Never forget to free a result!
    $db->sql_freeresult($result);
    // print the output
    print( $output );

 
Hi,

Came back with this:

Code: Select all

SQL ERROR [ mysql4 ]

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 '.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.'

xhotshot23x
Registered User
Posts: 83
Joined: Wed Oct 10, 2007 4:31 am

Re: Please help On Moderation Queue Permission Issue

Post by xhotshot23x »

I love it when I forget one letter, sorry about that and try this:

Code: Select all

<?php
      //-- Fetch the data from the specified fora
        $bbcode = new bbcode();
        $news_fora_id = array('2', '3');
        $output = '';
        $topic_count = '2';
        $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.topic_views, p.bbcode_bitfield, p.bbcode_uid, p.post_approved,
                           u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
                  FROM ' . POSTS_TABLE . ' AS p, ' . USERS_TABLE . ' AS u
                  WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                    AND u.user_id = p.poster_id
                        AND p.post_approved = 1
                 GROUP BY p.topic_id
                 ORDER BY p.topic_id DESC
                    LIMIT 0, ' . $topic_count;
       $result = $db->sql_query($query);
       
       while( $row = $db->sql_fetchrow($result) )
        {
            // Parse the message and subject
            $message = censor_text($row['post_text']);

            // Second parse bbcode here
            if ($row['bbcode_bitfield'])
            {
                $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
            }

            $message = bbcode_nl2br($message);
            $message = smiley_text($message);
           
            // Send data to output var
            $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
            $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
            $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
            $output .= "<br><p>\n\t";
            $output .= $message;
            $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
        }
        
        // Never forget to free a result!
        $db->sql_freeresult($result);
        // print the output
        print( $output );

     ?>
Image

ckwalsh
Former Team Member
Posts: 1837
Joined: Wed Mar 15, 2006 1:50 am
Location: Seattle, USA
Name: Cullen Walsh
Contact:

Re: Please help On Moderation Queue Permission Issue

Post by ckwalsh »

Moving this to MOD Writers Discussion
Where to post what | Forum Rules | The Dos and Don'ts of General Discussion
In Seattle and want to meet, chat, or have a coffee? Drop me a PM.

Seraphic
Registered User
Posts: 274
Joined: Wed Feb 15, 2006 8:20 am

Re: Please help On Moderation Queue Permission Issue

Post by Seraphic »

xhotshot23x wrote:I love it when I forget one letter, sorry about that and try this:

Code: Select all

<?php
      //-- Fetch the data from the specified fora
        $bbcode = new bbcode();
        $news_fora_id = array('2', '3');
        $output = '';
        $topic_count = '2';
        $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.topic_views, p.bbcode_bitfield, p.bbcode_uid, p.post_approved,
                           u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
                  FROM ' . POSTS_TABLE . ' AS p, ' . USERS_TABLE . ' AS u
                  WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                    AND u.user_id = p.poster_id
                        AND p.post_approved = 1
                 GROUP BY p.topic_id
                 ORDER BY p.topic_id DESC
                    LIMIT 0, ' . $topic_count;
       $result = $db->sql_query($query);
       
       while( $row = $db->sql_fetchrow($result) )
        {
            // Parse the message and subject
            $message = censor_text($row['post_text']);

            // Second parse bbcode here
            if ($row['bbcode_bitfield'])
            {
                $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
            }

            $message = bbcode_nl2br($message);
            $message = smiley_text($message);
           
            // Send data to output var
            $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
            $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
            $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
            $output .= "<br><p>\n\t";
            $output .= $message;
            $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
        }
        
        // Never forget to free a result!
        $db->sql_freeresult($result);
        // print the output
        print( $output );

     ?>
Hi,

No problem. You new code comes back with:

On a side note, found a fix for this. Thanks for your help though.

Code: Select all

SQL ERROR [ mysql4 ]

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 'FROM phpbb_posts AS p, phpbb_users AS u WHERE p.forum_id I' at line 3 [1064]

An SQL error occurred while fetching this page. Please contact the Board Administrator if this problem persists.

xhotshot23x
Registered User
Posts: 83
Joined: Wed Oct 10, 2007 4:31 am

Re: Please help On Moderation Queue Permission Issue

Post by xhotshot23x »

Sorry about all the confusing, I ended up testing this and found some more bugs so I fixed those. I was mistaken when I said you don't need to select the count of post_ids, you do, so I added that part back. Everything should be working now.

Code: Select all

//-- Fetch the data from the specified fora
        //$bbcode = new bbcode();
        $news_fora_id = array('2', '3');
        $topic_count = '2';
        $query = 'SELECT p.topic_id, p.forum_id, p.post_time, p.post_subject, p.post_text, p.bbcode_bitfield, p.bbcode_uid, p.post_approved,
                           u.user_id, u.user_email, u.username, u.user_posts, u.user_rank, u.user_colour, u.user_allow_viewonline, u.user_allow_viewemail,
                      (
                        SELECT COUNT( post_id ) AS aantal_posts
                        FROM ' . POSTS_TABLE . '
                        WHERE topic_id = p.topic_id
                    )    
                  FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
                  WHERE ' . $db->sql_in_set( 'p.forum_id', $news_fora_id ) . '
                    AND u.user_id = p.poster_id
                        AND p.post_approved = 1
                 GROUP BY p.topic_id
                 ORDER BY p.topic_id DESC
                    LIMIT 0, ' . $topic_count;
       $result = $db->sql_query($query);
       $output = '';
       while( $row = $db->sql_fetchrow($result) )
        {
            // Parse the message and subject
            $message = censor_text($row['post_text']);

            // Second parse bbcode here
            if ($row['bbcode_bitfield'])
            {
                $bbcode->bbcode_second_pass($message, $row['bbcode_uid'], $row['bbcode_bitfield']);
            }

            $message = bbcode_nl2br($message);
            $message = smiley_text($message);
           
            // Send data to output var
            $output .= "<h1><a href=\"" . $phpbb_root_path . "viewtopic.php?f={$row['forum_id']}&t={$row['topic_id']}\" title=\""  . censor_text($row['post_subject']) . "\">".censor_text($row['post_subject'])."</a></h1>\n";
            $output .= "\n\t<i>Posted by <a href=\"index.php?id=1\">" . $row['username'] . "</a>\n";
            $output .= "\n\ton " . date( $user->data['user_dateformat'], $row['post_time'] ) . "</i>\n";
            $output .= "<br><p>\n\t";
            $output .= $message;
            $output .= "</p><p><span class=\"newsspacer\">&nbsp;</span></p>\n\n";
        }
        
        // Never forget to free a result!
        $db->sql_freeresult($result);
        // print the output
        print( $output ); 
Image

Locked

Return to “[3.0.x] MOD Writers Discussion”