SQL Help

[stitch626]

I am trying to write a SQL for any topics in the Moderation queue but I am just learning and taking stabs. Been stabbing now for a few hours now I am turning yo you guys for some assistance please.

Code: Select all

$sql = 'SELECT unapproved_topics
      FROM ' . MODULES_TABLE . "
      WHERE unapproved_topics = 1";
      $result = $db->sql_query($sql);
      $row = $db->sql_fetchrow($result);
      $db->sql_freeresult($result);

And then I have:

Code: Select all

'S_UNAPPROVED_TOPICS'      => (($row['unapproved_topics']) && $auth->acl_get('m_approve')) ? true : false,

This just keeps on producing errors.

[Highway of Life]

Modules table?? ... unapproved_topics?

If you are trying to get a list of topics that are unapproved, you would want something like this...

Code: Select all

$sql = 'SELECT topic_id, topic_title
    FROM ' . TOPICS_TABLE . 
    WHERE topic_approved = 0;
$result = $db->sql_query($sql);
... 

If you are trying to get a count, the number of unapproved topics, it would be:

Code: Select all

$sql = 'SELECT COUNT(topic_id) AS unapproved_topics
    FROM ' . TOPICS_TABLE . '
    WHERE topic_approved = 0;
$result = $db->sql_query($sql);
$unapproved_topics = $db->sql_fetchfield('unapproved_topics);
$db->sql_freeresult($result); 

Before running the DB query, a check should be run to see if that moderator has permission, therefore, you would go...

Code: Select all

if ($auth->acl_get('m_approve'))
{
    // ... SQL goes here
} 

If you get errors, it always helps to know what those are so we can see what is causing the problem.

[stitch626]

Thanks David,

I placed the last block of code in Functions.php before:

Code: Select all

// The following assigns all _common_ variables that may be used at any point in a template.
   $template->assign_vars(array(

and I get this:

Parse error: parse error, unexpected T_STRING in /home/content/*/*/*/*****/html/testboard/includes/functions.php on line 3371

Here is that block:

Code: Select all

if ($auth->acl_get('m_approve'))
{
    $sql = 'SELECT unapproved_topics
    FROM ' . TOPICS_TABLE . '
    WHERE topic_approved = 1;
$result = $db->sql_query($sql);
$unapproved_topics = $db->sql_fetchfield('unapproved_topics);
$db->sql_freeresult($result);
}

<<This is line 3371

Code: Select all

$unapproved_topics = $db->sql_fetchfield('unapproved_topics);

Well how I came up with MODULES TABLE is because I did a search in phpmyadmin and this is the table it came up in.

Hey this is my first time trying to write a SQL so as you can see I have no idea nor do I quite understand what all that means yet.

[CoC Chris]

This is how I had it, if it's any help to you Mike.

Code: Select all

$total_unapproved = '';
   $sql = 'SELECT COUNT(topic_approved) AS total_unapproved
      FROM ' . TOPICS_TABLE . "
      WHERE topic_approved = 0";
         $result = $db->sql_query($sql);
         $total_unapproved = (int) $db->sql_fetchfield('total_unapproved');
         $db->sql_freeresult($result);

   if ($total_unapproved >= 0)
   {
      $total_unapproved = $total_unapproved;
   }

Code: Select all

      'U_MCP_UNAPPROVED_TOPIC'         => append_sid("{$phpbb_root_path}mcp.$phpEx", 'i=reports&&mode=unapproved_topics', true, $user->session_id),
      'S_UNAPPROVED_TOPIC'      => (($total_unapproved) && $auth->acl_get('m_approve')) ? true : false,

[stitch626]

@CoC

Oh. You have already done this mod?

[CoC Chris]

No -

I was doing it for my board, and then noticed the unapproved posts are different and need to be selected from the post_table and I couldn't be bothered to mess about with it so I just scrapped the idea.

But I keep a note of all things I do just in case one day I decide to go back to it, or I need a bit of help for something -

[Highway of Life]

and I get this:

Parse error: parse error, unexpected T_STRING in /home/content/*/*/*/*****/html/testboard/includes/functions.php on line 3371

Here is that block:

That's because you're missing a comma right before the ; on this line:

Code: Select all

WHERE topic_approved = 1';

Make sure you have an editor that does syntax highlighting. You?ll notice these problems right away.

Edit: And yes, it was my fault,... typing fast as I was in a hurry

[stitch626]

I knew it something dumb like that. Things like this don't stand out for me becuase I am still learning so I am not yet familair with all the comma's and quotes yet where they are supposed to be placed.

Thanks again. And thak you CoC.

[Obsidian]

Let me guess, this is for that Topic Reported MOD, right?

You're extending it to do the unapproved posts, too?

Hurrah! Go stitch!



*hugs*

[stitch626]

Let me guess, this is for that Topic Reported MOD, right?

You're extending it to do the unapproved posts, too?

Hurrah! Go stitch!



*hugs*

Um well I was working on it but CoC made it a bit too easy on me and threw me the entire code for it. J/K CoC.

Anyway, I haven't had a chance to try it out yet. I am sure it works just fine knowing CoC's fine work.
Here is what he sent me today. I haven't put it into Mod format though.

This is both mods combined into one.
In Function.php

Spoiler:

Code: Select all

// Start Unapproved Notification Mod
// Unapproved Posts
$total_unapproved_posts = '';
    $sql = 'SELECT COUNT(post_approved) AS total_unapproved_posts
        FROM ' . POSTS_TABLE . "
        WHERE post_approved = 0";
            $result = $db->sql_query($sql);
            $total_unapproved_posts = (int) $db->sql_fetchfield('total_unapproved_posts');
            $db->sql_freeresult($result);

    if ($total_unapproved_posts >= 0)
    {
        $total_unapproved_posts = $total_unapproved_posts;
    }
// Unapproved Posts

// Unapproved Topics
$total_unapproved = '';
    $sql = 'SELECT COUNT(topic_approved) AS total_unapproved
        FROM ' . TOPICS_TABLE . "
        WHERE topic_approved = 0";
            $result = $db->sql_query($sql);
            $total_unapproved = (int) $db->sql_fetchfield('total_unapproved');
            $db->sql_freeresult($result);

    if ($total_unapproved >= 0)
    {
        $total_unapproved = $total_unapproved;
    }
// Unaproved Topics

if ($total_unapproved)
{
        $total_unapproved_posts = !$total_unapproved_posts;
}
// End Unapproved Notification Mod

Spoiler:

Code: Select all

'U_MCP_APPROVED_TOPIC'            => append_sid("{$phpbb_root_path}mcp.$phpEx", 'i=queue&mode=unapproved_topics', true, $user->session_id),
        'S_APPROVED_TOPIC'        => (($total_unapproved) && $auth->acl_get('m_approve')) ? true : false,

        'U_MCP_APPROVED_POSTS'            => append_sid("{$phpbb_root_path}mcp.$phpEx", 'i=queue&mode=unapproved_posts', true, $user->session_id),
        'S_APPROVED_POSTS'        => (($total_unapproved_posts) && $auth->acl_get('m_approve')) ? true : false,

language/en/common.php

Spoiler:

Code: Select all

'APPROVED_TOPIC_ATT'        => 'A topic has been posted and needs approving!',
'APPROVED_POST_ATT'        => 'A post has been made and need approving!',

overall_header.html

Spoiler:

Code: Select all

<!-- IF S_APPROVED_TOPIC or S_APPROVED_POSTS -->
    <div id="message" class="rules">
        <div class="inner"><span class="corners-top"><span></span></span>
            <div style="text-align: center">
<!-- IF S_APPROVED_TOPIC -->
                <a href="{U_MCP_APPROVED_TOPIC}"><img src="{T_IMAGESET_PATH}/icon_reported.gif" alt="*" />{L_APPROVED_TOPIC_ATT}<img src="{T_IMAGESET_PATH}/icon_reported.gif" alt="*" /></a>
<!-- ENDIF -->
<!-- IF S_APPROVED_POSTS -->
                <a href="{U_MCP_APPROVED_POSTS}"><img src="{T_IMAGESET_PATH}/icon_reported.gif" alt="*" />{L_APPROVED_POST_ATT}<img src="{T_IMAGESET_PATH}/icon_reported.gif" alt="*" /></a>
<!-- ENDIF -->
            </div>
        <span class="corners-bottom"><span></span></span></div>
    </div>
<!-- ENDIF -->

Now go back to the mod topic fhttp://startrekguide.com/community/vie ... 1&start=10 and copy those animated images to your imageset folder