Can Most Active be changed to "Most Active [""] this week"?

Post by **corleoner** » 12 Feb 2014, 07:53

Reason being is that my forum is sports related and older topics lose interest once they get past a week old

I searched through this forum and also phpbb and didn't see anyting definitive on how it would be done. I'm pretty sure I could edit the language files to update the header titles for Most Active to include "This Week", but is there a way to change the code to compile the stats for 7 days?

Post by **Steve** » 12 Feb 2014, 10:26

Try this:
Open includes/functions_top_stats.php
Find:

Code: Select all

        WHERE ' . $db->sql_in_set('t.forum_id', $flist) . ' AND t.topic_moved_id = 0 AND t.topic_approved = 1

after add:

Code: Select all

        AND t.topic_last_post_time > ' . (time() - (int) (7 * 24 * 3600)) . '

Post by **corleoner** » 12 Feb 2014, 18:59

Thanks for the help Steve,

I tried adding those to most viewed topics and most replied topics and I get this error on my index

General Error

SQL ERROR [ mysqli ]

Unknown column 't.topic_last_post_time' in 'where clause' [1054]

SQL

SELECT topic_id, forum_id, topic_title, topic_views, topic_time, topic_first_poster_name, topic_first_poster_colour, topic_poster, topic_last_poster_id FROM bb_topics WHERE forum_id IN (3, 4, 5, 6, 7, 10, 11, 0) AND t.topic_last_post_time > 1391619563 ORDER BY topic_views DESC LIMIT 3

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/includes/db/dbal.php
LINE: 757
CALL: trigger_error()

FILE: [ROOT]/includes/db/mysqli.php
LINE: 189
CALL: dbal->sql_error()

FILE: [ROOT]/includes/db/mysqli.php
LINE: 231
CALL: dbal_mysqli->sql_query()

FILE: [ROOT]/includes/db/dbal.php
LINE: 170
CALL: dbal_mysqli->_sql_query_limit()

FILE: [ROOT]/includes/functions_top_stats.php
LINE: 94
CALL: dbal->sql_query_limit()

FILE: [ROOT]/index.php
LINE: 192
CALL: include('[ROOT]/includes/functions_top_stats.php')

Post by **Steve** » 12 Feb 2014, 19:08

place a copy of your includes/functions_top_stats.php between the

Code: Select all

 tags ,and what version of the mod you using?

Post by **corleoner** » 12 Feb 2014, 19:10

Looks like it's 1.0.9

Here ya go.

Code: Select all

<?php
/**
*
* @package phpBB3
* @version $Id: functions_top_stats.php v1.0.10 2012/20/06 Stoker $
* @copyright (c) 2012 PhpBB3 BBCodes
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/

/**
* @ignore
*/
if (!defined('IN_PHPBB'))
{
	exit;
}

/**
* Include only once.
*/
if (!defined('INCLUDES_FUNCTIONS_TOP_STATS_PHP'))
{
	define('INCLUDES_FUNCTIONS_TOP_STATS_PHP', true);
	
	$flist = $auth->acl_getf('f_read', true);
	$flist = array_unique(array_keys($flist));
	$flist = array_merge($flist, array(0));
	
// Recent Active Topics
$enable_active_topics = (!empty($config['tsrat_enable']) ? true : false);
if ($enable_active_topics && sizeof($flist))
{	
	$sql = 'SELECT t.forum_id, t.topic_id, t.topic_title, t.topic_time, t.topic_views, t.topic_poster, t.topic_replies, t.topic_first_poster_name, t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_last_post_time, t.topic_last_view_time, t.topic_last_poster_id, f.forum_name, f.forum_image
		FROM ' . TOPICS_TABLE . ' t
		LEFT JOIN ' . FORUMS_TABLE . ' f ON (t.forum_id = f.forum_id)
		WHERE ' . $db->sql_in_set('t.forum_id', $flist) . ' AND t.topic_moved_id = 0 AND t.topic_approved = 1
		ORDER BY t.topic_last_post_time DESC';
	$result = $db->sql_query_limit($sql, $config['tsrat_number']);

	while ($row = $db->sql_fetchrow($result))
	{
		$view_topic_url = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $row['forum_id'] . '&t=' . $row['topic_id']);
		$view_last_post_url = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $row['forum_id'] . '&p=' . $row['topic_last_post_id'] . '#p' . $row['topic_last_post_id']);
		$view_first_poster = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['topic_poster']);
		$view_last_poster = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['topic_last_poster_id']);
		$view_viewforum_url = append_sid("{$phpbb_root_path}viewforum.$phpEx", 'f=' . $row['forum_id']);
		
		$template->assign_block_vars('recent_active', array(
			'TOPIC_TITLE'					=> $row['topic_title'],
			'TOPIC_TIME'                    => $user->format_date($row['topic_time']), 
			'TOPIC_VIEWS'					=> $row['topic_views'],
			'TOPIC_REPLIES'					=> $row['topic_replies'],
			'TOPIC_FIRST_POSTER_NAME'		=> $row['topic_first_poster_name'],
			'TOPIC_FIRST_POSTER_COLOUR'		=> $row['topic_first_poster_colour'],
			'TOPIC_LAST_POSTER_NAME'		=> $row['topic_last_poster_name'],
			'TOPIC_LAST_POSTER_COLOUR'		=> $row['topic_last_poster_colour'],
			'TOPIC_LAST_POST_TIME'			=> $user->format_date($row['topic_last_post_time']),
			'TOPIC_LAST_VIEW_TIME'			=> $user->format_date($row['topic_last_view_time']),
			'U_FIRST_TOPIC'					=> $view_topic_url,
			'U_LAST_TOPIC'					=> $view_last_post_url,
			'USERNAME_FIRST'				=> $view_first_poster,
			'USERNAME_LAST'					=> $view_last_poster,
			'FORUM_NAME'					=> $row['forum_name'],
			'FORUM_IMAGE'					=> $row['forum_image'],
			'FORUM_URL'						=> $view_viewforum_url,
		));
	}
	$db->sql_freeresult($result);
	
	$template->assign_vars(array(
	//isset we can enter a value ?  : 'or leave empty' (value)
	//!empty yes : no ,true : false (bool)
        'JSSCROLL_SPEED'				=> (isset($config['tsjss_speed'])) ? $config['tsjss_speed'] : '',
		'JSSCROLL_INTERVAL'				=> (isset($config['tsjss_interval'])) ? $config['tsjss_interval'] : '',
		'TSRAT_NUMBER'					=> (isset($config['tsrat_number'])) ? $config['tsrat_number'] : '',
		'TS_JSSCROLL_DIRECTION'			=> (!empty($config['ts_jsscroll_direction'])) ? true : false,
		'TS_JSSCROLL_PAUSE'				=> (!empty($config['ts_jsscroll_pause'])) ? true : false,
		'TS_JSSCROLL_NAVIGATION'		=> (!empty($config['ts_jsscroll_navigation'])) ? true : false,
		'S_TSRAT_ENABLE'      			=> (!empty($config['tsrat_enable'])) ? true : false,
		'S_TS_JSSCROLL'      			=> (isset($config['ts_jsscroll'])) ? $config['ts_jsscroll'] : '',
		'S_TS_TICKER' 	     			=> (!empty($config['ts_ticker_enable'])) ? true : false,
    ));
}

	
// Most viewed topics
$enable_viewed_topics = (!empty($config['tsmvt_enable']) ? true : false);
if ($enable_viewed_topics && sizeof($flist))
{	
	$sql = 'SELECT topic_id, forum_id, topic_title, topic_views, topic_time, topic_first_poster_name, topic_first_poster_colour, topic_poster, topic_last_poster_id
		FROM ' . TOPICS_TABLE . ' WHERE ' . $db->sql_in_set('forum_id', $flist) . '
		ORDER BY topic_views DESC';
	$result = $db->sql_query_limit($sql, $config['tsmvt_number']);
	
	$most_viewed = array();
	while ($row = $db->sql_fetchrow($result))
	{
		$most_viewed[] = $row;
	}
  
	$db->sql_freeresult($result);
	
	foreach ($most_viewed as $row)
	{
		$view_topic_url = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $row['forum_id'] . '&t=' . $row['topic_id']);
		$view_first_poster = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['topic_poster']);
		$view_last_poster = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['topic_last_poster_id']);
		
		$template->assign_block_vars('most_viewed', array(
			'TOPIC_ID'						=> $row['topic_id'], 
			'FORUM_ID'	    				=> $row['forum_id'],
			'TOPIC_TITLE'					=> (utf8_strlen($row['topic_title']) > 41) ? truncate_string($row['topic_title'], 40) . "\xE2\x80\xA6" : $row['topic_title'],
			'TOPIC_VIEWS'					=> $row['topic_views'],
			'TOPIC_TIME'                    => $user->format_date($row['topic_time']), 
			'TOPIC_FIRST_POSTER_NAME'		=> $row['topic_first_poster_name'],
			'TOPIC_FIRST_POSTER_COLOUR'		=> $row['topic_first_poster_colour'],
			'U_FIRST_TOPIC'					=> $view_topic_url,
			'USERNAME_FIRST'				=> $view_first_poster,
			'USERNAME_LAST'					=> $view_last_poster,
		));
	}
	$db->sql_freeresult($result);
	
	$template->assign_vars(array(
		'S_TSMVT_ENABLE'      			=> $enable_viewed_topics,
		'TSMVT_NUMBER'					=> (isset($config['tsmvt_number'])) ? $config['tsmvt_number'] : '',
    ));
}
	
// Most replied topics
$enable_replied_topics = (!empty($config['tsmrt_enable']) ? true : false);
if ($enable_replied_topics && sizeof($flist))
{
	$sql = 'SELECT topic_id, forum_id, topic_title, topic_replies, topic_time, topic_first_poster_name, topic_first_poster_colour, topic_poster, topic_last_poster_id 
		FROM ' . TOPICS_TABLE . ' WHERE ' . $db->sql_in_set('forum_id', $flist) . '
		ORDER BY topic_replies DESC';
	$result = $db->sql_query_limit($sql, $config['tsmrt_number']);
	
	$most_replied = array();
	while ($row = $db->sql_fetchrow($result))
	{
		$most_replied[] = $row;
	}
  
	$db->sql_freeresult($result);
	
	foreach ($most_replied as $row)
	{
		$view_topic_url = append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $row['forum_id'] . '&t=' . $row['topic_id']);
		$view_first_poster = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['topic_poster']);
		$view_last_poster = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['topic_last_poster_id']);
		
		$template->assign_block_vars('most_replied', array(
			'TOPIC_ID'						=> $row['topic_id'], 
			'FORUM_ID'	    				=> $row['forum_id'],
			'TOPIC_TITLE'					=> (utf8_strlen($row['topic_title']) > 41) ? truncate_string($row['topic_title'], 40) . "\xE2\x80\xA6" : $row['topic_title'],
			'TOPIC_REPLIES'					=> $row['topic_replies'],
			'TOPIC_TIME'                    => $user->format_date($row['topic_time']), 
			'TOPIC_FIRST_POSTER_NAME'		=> $row['topic_first_poster_name'],
			'TOPIC_FIRST_POSTER_COLOUR'		=> $row['topic_first_poster_colour'],
			'U_FIRST_TOPIC'					=> $view_topic_url,
			'USERNAME_FIRST'				=> $view_first_poster,
			'USERNAME_LAST'					=> $view_last_poster,
		));
	}
	$db->sql_freeresult($result);
	
	$template->assign_vars(array(
		'S_TSMRT_ENABLE'      			=> $enable_replied_topics,
		'TSMRT_NUMBER'					=> (isset($config['tsmrt_number'])) ? $config['tsmrt_number'] : '',
    ));
}

// Most active users
$enable_active_users = (!empty($config['tsmau_enable']) ? true : false);
if ($enable_active_users)
{	
	if (($active_users = $cache->get('_ts_most_active_users')) === false)
	{
		$sql = 'SELECT user_id, username, user_posts, user_colour, user_regdate
			FROM ' . USERS_TABLE . ' WHERE user_inactive_time = 0
			AND group_id != 6
			AND user_id != 1
			ORDER BY user_posts DESC';
		$result = $db->sql_query_limit($sql, $config['tsmau_number']);
		
		$active_users = array();
		while ($row = $db->sql_fetchrow($result))
		{
			$active_users[] = $row;
		}
	  
		$db->sql_freeresult($result);

		// Cache for 1 hour
		$cache->put('_ts_most_active_users', $active_users, 3600);
	}
	
	foreach ($active_users as $row)
	{
		$most_active_user = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['user_id']);
		$most_active_user_search = append_sid("{$phpbb_root_path}search.$phpEx", 'author_id=' . $row['user_id'] . '&sr=posts');
		$most_active_user_percent = ($row['user_posts'] * 100) / $total_posts;
		
		$template->assign_block_vars('most_active_users', array(
			'USER_ID'						=> $most_active_user,
			'USER_POST_SEARCH'				=> $most_active_user_search,
			'USER_POST_PERCENT'				=> number_format($most_active_user_percent, 2),
			'USERNAME'						=> $row['username'],
			'USER_REG'						=> $user->format_date($row['user_regdate']),
			'USER_POSTS'					=> $row['user_posts'],
			'USER_COLOUR'					=> $row['user_colour'],
		));
	}
	$db->sql_freeresult($result);
	
	$template->assign_vars(array(
		'S_TSMAU_ENABLE'      			=> $enable_active_users,
		'TSMAU_NUMBER'					=> (isset($config['tsmau_number'])) ? $config['tsmau_number'] : '',
    ));
}

// Most active forums
$enable_active_forums = (!empty($config['tsmaf_enable']) ? true : false);
if ($enable_active_forums && sizeof($flist))
{
	$sql = 'SELECT forum_id, forum_name, forum_posts
		FROM ' . FORUMS_TABLE . ' WHERE ' . $db->sql_in_set('forum_id', $flist) . ' AND forum_type = ' . FORUM_POST . '
		ORDER BY forum_posts DESC';
	$result = $db->sql_query_limit($sql, $config['tsmaf_number']);

	$active_forums = array();
	while ($row = $db->sql_fetchrow($result))
	{
		$active_forums[] = $row;
	}
  
	$db->sql_freeresult($result);

	foreach ($active_forums as $row)
	{
		$most_active_forums = append_sid("{$phpbb_root_path}viewforum.$phpEx", 'f=' . $row['forum_id']);
		$most_active_forums_percent = ($row['forum_posts'] * 100) / $total_posts;
		
		$template->assign_block_vars('most_active_forums', array(
			'FORUM_URL'						=> $most_active_forums,
			'FORUM_POSTS_PERCENT'			=> number_format($most_active_forums_percent, 2),
			'FORUM_ID'						=> $row['forum_id'],
			'FORUM_NAME'					=> $row['forum_name'],
			'FORUM_POSTS'					=> $row['forum_posts'],
		));
	}
	$db->sql_freeresult($result);
	
	$template->assign_vars(array(
		'S_TSMAF_ENABLE'      			=> $enable_active_forums,
		'TSMAF_NUMBER'					=> (isset($config['tsmaf_number'])) ? $config['tsmaf_number'] : '',
    ));
}

// Last visited Bots
if (!empty($config['tslvb_enable']))
{	
	if ( ( $last_bots = $cache->get('_ts_last_visited_bots') ) === false )
	{	
		$sql = 'SELECT user_id, username, user_lastvisit, user_colour
			FROM ' . USERS_TABLE . ' 
			WHERE user_type = ' . USER_IGNORE . '
			ORDER BY user_lastvisit DESC';
		$result = $db->sql_query_limit($sql, $config['tslvb_number']);
	
		$last_bots = array();
		while ($row = $db->sql_fetchrow($result))
		{
			$last_bots[] = $row;
		}
	  
		$db->sql_freeresult($result);

		// Cache for 5 minutes
		$cache->put('_ts_last_visited_bots', $last_bots, 300);
	}
	
	foreach ( $last_bots as $row )
	{	
		$template->assign_block_vars('last_visited_bots', array(
			'USERNAME'						=> $row['username'],
			'USER_COLOUR'					=> $row['user_colour'],
			'USER_LAST_VISIT'               => $user->format_date($row['user_lastvisit']),
		));
	}
	$db->sql_freeresult($result);
	
	$template->assign_vars(array(
		'S_TSLVB_ENABLE'      			=> $config['tslvb_enable'],
		'TSLVB_NUMBER'					=> $config['tslvb_number'],
    ));
}

// Last registered Users
$enable_last_users = (!empty($config['tslru_enable']) ? true : false);
if ($enable_last_users)
{   
	if (( $last_users = $cache->get('_ts_last_registered_users')) === false)
	{
		$sql = 'SELECT user_id, username, user_colour, user_regdate
			FROM ' . USERS_TABLE . ' WHERE user_inactive_time = 0
			AND group_id != 6
			AND user_id != 1
			ORDER BY user_regdate DESC';
		$result = $db->sql_query_limit($sql, $config['tslru_number']);
   
		$last_users = array();
		while ($row = $db->sql_fetchrow($result))
		{
			$last_users[] = $row;
		}
	  
		$db->sql_freeresult($result);

		// Cache for 5 minutes
		$cache->put('_ts_last_registered_users', $last_users, 300);
	}

	foreach ($last_users as $row)
	{
		$last_registered_user = append_sid("{$phpbb_root_path}memberlist.$phpEx", 'mode=viewprofile' . '&u=' . $row['user_id']);
	  
		$template->assign_block_vars('last_registered_user', array(
			'USER_URL'                  => $last_registered_user,
			'USERNAME'                  => $row['username'],
			'USER_COLOUR'               => $row['user_colour'],
			'USER_REGISTERED'           => $user->format_date($row['user_regdate']),
		));
	}
	
	$template->assign_vars(array(
		'S_TSLRU_ENABLE'				=> $enable_last_users,
		'TSLRU_NUMBER'					=> (isset($config['tslru_number'])) ? $config['tslru_number'] : '',
	));
}

}

?>

Post by **Steve** » 12 Feb 2014, 19:14

Adjusted Find

Steve© wrote:Try this:
Open includes/functions_top_stats.php
Find:

Code: Select all

   $sql = 'SELECT t.forum_id, t.topic_id, t.topic_title, t.topic_time, t.topic_views, t.topic_poster, t.topic_replies, t.topic_first_poster_name, t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_last_post_time, t.topic_last_view_time, t.topic_last_poster_id, f.forum_name, f.forum_image
      FROM ' . TOPICS_TABLE . ' t
      LEFT JOIN ' . FORUMS_TABLE . ' f ON (t.forum_id = f.forum_id)
      WHERE ' . $db->sql_in_set('t.forum_id', $flist) . ' AND t.topic_moved_id = 0 AND t.topic_approved = 1

after add:

Code: Select all

        AND t.topic_last_post_time > ' . (time() - (int) (7 * 24 * 3600)) . '

Your placing it in the wrong sql query

Post by **corleoner** » 12 Feb 2014, 19:18

I'm not tyring to use it for the 10 active topics, as our board moves fast, it's not necessary for just 10 topics,

But I would like to use it for

Most Viewed topics, most replied topics, most active users, most active forums

Would I still insert that code at the same spot in those areas?

Post by **Steve** » 12 Feb 2014, 19:20

yeah but the code needs adjusted i will post back when i have time, the mod file by stoker is a mess I'm sure i had fixed it at some point...

Post by **corleoner** » 12 Feb 2014, 19:24

I appreciate it Steve, that's probably why it wasn't working for me, I tried that same code in the different sections.

Thanks a ton!

Post by **Steve** » 12 Feb 2014, 20:06

you can use

Code: Select all

		AND topic_time > ' . (time() - (int) (7 * 24 * 3600)) . '

for // Most viewed topics && // Most replied topics ,but the rest is a good hrs work...