Topic: How to use query_see_board in second php block?

[Tarista]

I have been using the two codes below in a couple of custom php blocks and I can't seem to figure out how to get the second working like I want it to. The first uses "AND {query_see_board}"and I would prefer if the second could do that too since not all members can see all the boards and might be confused by the statistics, but I can not figure out how to get it to work. If I just add it to the statement members who can not see all boards get database-errors.

Both codes where found on this site so credits goes out to those that originally wrote them.

Code: [Select]

global $smcFunc, $scripturl, $txt;

$limit = 5;
$request = $smcFunc['db_query']('', '
   SELECT m.id_topic, m.subject, COUNT(m.id_msg) AS posts
   FROM {db_prefix}messages AS m
      INNER JOIN {db_prefix}boards AS b ON (b.id_board = m.id_board)
    WHERE poster_time <= {int:time}
       AND poster_time >= {int:time2}      AND {query_see_board}
   GROUP BY m.id_topic
   ORDER BY posts DESC
   LIMIT {int:limit}',
       array(
          'time' => time(),
          'time2' => (time()-604800),
      'limit' => $limit,
       )
);
while ($row = $smcFunc['db_fetch_assoc']($request))
{
   $row['subject'] = str_replace('Re: ', '', $row['subject']);

   $topics[] = array(
      'id' => $row['id_topic'],
      'subject' => $row['subject'],
      'href' => $scripturl . '?topic=' . $row['id_topic'] . '.0',
      'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.0" title="' . $row['subject'] . '">' . $row['subject'] . '</a>',
      'replies' => comma_format($row['posts']),
   );
}
$smcFunc['db_free_result']($request);

Code: [Select]

global $smcFunc, $scripturl, $context, $settings;

   // Top 10 Posters so far this week  (starts sunday)
   $starttime = mktime(0, 0, 0, date("n"), date("j"), date("Y")) - (date("N")*3600*24);
   // Offset based on forum time
   $starttime = forum_time(false, $starttime);
   
   $request = $smcFunc['db_query']('', '
      SELECT me.id_member, me.real_name, COUNT(*) as count_posts
      FROM {db_prefix}messages AS m
         LEFT JOIN {db_prefix}members AS me ON (me.id_member = m.id_member)
      WHERE m.poster_time > {int:start_time}
         AND m.id_member != 0
      GROUP BY me.id_member
      ORDER BY count_posts DESC
      LIMIT 5',
      array(
         'start_time' => $starttime,
      )
   );
   
   $context['top_posters_week'] = array();
   $max_num_posts = 1;
   while ($row_members = $smcFunc['db_fetch_assoc']($request))
   {
      $context['top_posters_week'][] = array(
         'name' => $row_members['real_name'],
         'id' => $row_members['id_member'],
         'num_posts' => $row_members['count_posts'],
         'href' => $scripturl . '?action=profile;u=' . $row_members['id_member'],
         'link' => '<a href="' . $scripturl . '?action=profile;u=' . $row_members['id_member'] . '">' . $row_members['real_name'] . '</a>'
      );

      if ($max_num_posts < $row_members['count_posts'])
         $max_num_posts = $row_members['count_posts'];
   }
   $smcFunc['db_free_result']($request);

Thank you

[[SiNaN]]

Code: (Find) [Select]

WHERE m.poster_time > {int:start_time}

Code: (Replace) [Select]

INNER JOIN {db_prefix}boards AS b ON (b.id_board = m.id_board)
WHERE m.poster_time > {int:start_time}
AND {query_see_board}

[Tarista]

Thank You so much! That worked great!

I went ahead and tried to add it to sp_TopPoster() in PortalBlocks.php for the This Month-statistics as well and it seems to work there too

Thank you once more for the quick reply.

Wishing you a great day!