Le Blog de Sébastien Colas

<?php

/*
* Doc: https://www.phpbb.com/customise/db/mod/view_or_mark_unread_posts/support/unread_posts_counter_small_optimize-t_99119
*/

/* Headers JSON */
header('Last-Modified: ' . gmdate( 'D, d M Y H:i:s' ) . 'GMT' );
header('Cache-Control: no-cache, must-revalidate');
header('Pragma: no-cache');
header('Content-type: application/json; charset=iso-8859-1');

define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : '../forum/';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include ($phpbb_root_path . 'common.' . $phpEx);

$user->session_begin();

global $db, $user;

if ($user->data['user_id'] == ANONYMOUS)
{
echo '{"unread_posts":"0"}';
return;
}

// Select unread topics
$unread_topics = array ();
$sql = 'SELECT t.topic_id
FROM ' . TOPICS_TABLE . ' t
LEFT JOIN ' . TOPICS_TRACK_TABLE . ' tt ON (tt.user_id = ' . $user->data['user_id'] . ' AND t.topic_id = tt.topic_id)
LEFT JOIN ' . FORUMS_TRACK_TABLE . ' ft ON (ft.user_id = ' . $user->data['user_id'] . ' AND t.forum_id = ft.forum_id)
WHERE t.topic_last_post_time > ' . $user->data['user_lastmark'] . ' AND
(
(tt.mark_time IS NOT NULL AND t.topic_last_post_time > tt.mark_time) OR
(tt.mark_time IS NULL AND ft.mark_time IS NOT NULL AND t.topic_last_post_time > ft.mark_time) OR
(tt.mark_time IS NULL AND ft.mark_time IS NULL)
)
LIMIT 1001';

$result = $db->;sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$unread_topics[] = $row['topic_id'];
}

if (empty ($unread_topics))
{
echo '{"unread_posts":"0"}';
return;
}

// now count the posts with post time after each of the relevant times
$sql = 'SELECT COUNT(p.post_id) as count
FROM ' . POSTS_TABLE . ' p
LEFT JOIN ' . FORUMS_TRACK_TABLE . ' ft ON (p.forum_id = ft.forum_id AND ft.user_id = ' . $user->data['user_id'] . ')
LEFT JOIN ' . TOPICS_TRACK_TABLE . ' tt ON (p.topic_id = tt.topic_id AND tt.user_id = ' . $user->data['user_id'] . ')
WHERE ' . $db->sql_in_set('p.topic_id', $unread_topics) . '
AND
(
p.post_time > tt.mark_time
OR (tt.mark_time IS NULL AND p.post_time > ft.mark_time)
OR (ft.mark_time IS NULL AND p.post_time > ' . $user->data['user_lastmark'] . ')
)';

$result = $db->sql_query($sql);
$unread_post_count = $db->sql_fetchfield('count', false, $result);
$db->sql_freeresult($result);

echo '{"unread_posts":"' . $unread_post_count . '"}';

?>