[DEV] Precise Similar Topics

A place for MOD Authors to post and receive feedback on MODs still in development. No MODs within this forum should be used within a live environment!
Get Involved
Xabi
Registered User
Posts: 460
Joined: Wed May 23, 2007 9:04 am

[DEV] Precise Similar Topics

Post by Xabi »

This little but powerful modification is based on the Similar Topics Mod (http://www.phpbb-seo.de/downloads/mod-s ... opics.html), but the MySQL query is different, more precise (specially in big forums, with a lot of topics to search for). It's also a lighter query: I have it running successfully in a >1.600.000 posts, > 35.000 visits/day forum (anyway, it would be much better to do some caching work here).

Summing, this mod will show 5 related topics at the bottom of your threads (viewtopic), based on similar titles, sorted by relevance, from topics posted over the last year.

First of all, your topics table must be Fulltext indexed (MyISAM). In phpmyadmin:

Code: Select all

ALTER TABLE `phpbb_topics` ADD FULLTEXT (`topic_title`);
Then, in viewtopic.php, find:

Code: Select all

page_header($user->lang['VIEW_TOPIC'] . ' - ' . $topic_data['topic_title']);
Add after:

Code: Select all

// Begin Precise Similar Topics
$sql_array = array(
	'SELECT'	=> 'f.forum_id, f.forum_name, t.topic_id, t.topic_title, t.topic_time, MATCH (t.topic_title) AGAINST (\'' . $db->sql_escape($topic_data['topic_title']) . '\' ) as score',

	'FROM'		=> array(
		TOPICS_TABLE	=> 't',
	),

	'LEFT_JOIN'	=> array(
		array(
			'FROM'	=>	array(FORUMS_TABLE	=> 'f'),
			'ON'	=> 'f.forum_id = t.forum_id'
		)
	),

	'WHERE'		=> "MATCH (t.topic_title) AGAINST ('" . $db->sql_escape($topic_data['topic_title']) . "' ) >= 0.5
		AND t.topic_status <> " . ITEM_MOVED . '
		AND t.topic_time > (UNIX_TIMESTAMP() - 60*60*24*365)
		AND t.topic_id <> ' . (int) $topic_data['topic_id'],

	'GROUP_BY'	=> 't.topic_id',

	'ORDER_BY'	=> 'score desc',
);
$sql = $db->sql_build_query('SELECT', $sql_array);
if ($result = $db->sql_query_limit($sql, 5))
{
	while($similar = $db->sql_fetchrow($result))
	{
		if ($auth->acl_get('f_read', $similar['forum_id']))
		{
			$similar_forum_url	= append_sid("{$phpbb_root_path}viewforum.$phpEx", "f=" . $similar['forum_id']);
			$similar_topic_url	= append_sid("{$phpbb_root_path}viewtopic.$phpEx", "f=" . $similar['forum_id'] . '&t=' . $similar['topic_id']);

			$template->assign_block_vars('similar', array(
				'TOPIC_TITLE'			=> $similar['topic_title'],
				'U_TOPIC'				=> $similar_topic_url,
				'U_FORUM'				=> $similar_forum_url,
				'FORUM'					=> $similar['forum_name'])
			);
		}
	}
}
// End Precise Similar Topics
If you want to limit the search for similar topics to some forums, find inline:

Code: Select all

AND t.topic_time > (UNIX_TIMESTAMP() - 60*60*24*365)
And add after, in a new line:

Code: Select all

AND f.forum_id IN (X, X, X, X)
X, X, X... would be the forum IDs to take in account.

And finally, in styles/prosilver/template/viewtopic_body.html, search:

Code: Select all

<!-- INCLUDE jumpbox.html -->
And add after:

Code: Select all

<!-- IF .similar -->
<h3>Similar topics</h3>
<div class="post bg1">
	<div class="inner">
		<span class="corners-top"><span></span></span>
			<table width="100%">
			<tr>
				<!-- BEGIN similar -->
				<td width="20%" valign="top"><div style="margin: 5px;"><a href="{similar.U_TOPIC}" class="topictitle">{similar.TOPIC_TITLE}</a><br />
					{L_FORUM}: <a href="{similar.U_FORUM}">{similar.FORUM}</a></div>
				</td>
				<!-- END similar -->
			</tr>
			</table>
			<div class="back2top"><a href="#wrap" class="top" title="{L_BACK_TO_TOP}">{L_BACK_TO_TOP}</a></div>
		<span class="corners-bottom"><span></span></span>
	</div>
</div>
<br />
<!-- ENDIF -->
User avatar
hejowicz
Registered User
Posts: 442
Joined: Sun Dec 23, 2007 4:03 pm

Re: [DEV] Precise Similar Topics

Post by hejowicz »

Can you give instruction to SubSilver2 ? ;)
endeffects
Registered User
Posts: 27
Joined: Thu Sep 18, 2008 2:40 pm

Re: [DEV] Precise Similar Topics

Post by endeffects »

thanks, this works much better then the original sql query.

i modified your code to use it with phpbb seo mod,
but i still have a problem, i wanna modify the db query
in a way that it don't lists other topics with the same
topic title as a previous match. so t.topic_title should be
unique in $sql_array.

i hope somebody can help me with it.

Code: Select all

// Begin Precise Similar Topics
$sql_array = array(
   'SELECT'   => 'f.forum_id, f.forum_name, t.topic_id, t.topic_title, t.topic_description, t.topic_replies, t.topic_views, u.user_id, u.username, u.user_colour, t.topic_time, MATCH (t.topic_title) AGAINST (\'' . $db->sql_escape($topic_data['topic_title']) . '\' ) as score',

   'FROM'      => array(
      TOPICS_TABLE   => 't',
   ),

   'LEFT_JOIN'   => array(
		array(
			'FROM'	=>	array(USERS_TABLE	=> 'u'),
			'ON'	=> 'u.user_id = t.topic_poster'
		),
		array(
        	'FROM'   =>   array(FORUMS_TABLE   => 'f'),
        	'ON'   => 'f.forum_id = t.forum_id'
      	)
	),

   'WHERE'      => "MATCH (t.topic_title) AGAINST ('" . $db->sql_escape($topic_data['topic_title']) . "' ) >= 0.5
      AND t.topic_status <> " . ITEM_MOVED . '
      AND t.topic_time > (UNIX_TIMESTAMP() - 60*60*24*365)
      AND t.topic_id <> ' . (int) $topic_data['topic_id'],

   'GROUP_BY'   => 't.topic_id',

   'ORDER_BY'   => 'score desc',
);
$sql = $db->sql_build_query('SELECT', $sql_array);
if ($result = $db->sql_query_limit($sql, 5))
{
	while($similar = $db->sql_fetchrow($result))
	{
    	if ($auth->acl_get('f_read', $similar['forum_id']))
	    {
    	    // http://www.phpBB-SEO.com SEO TOOLKIT BEGIN
        	if ( empty($phpbb_seo->seo_url['topic'][$similar['topic_id']]) ) {
	           if ($similar['topic_type'] == POST_GLOBAL) {
    	          $phpbb_seo->seo_opt['topic_type'][$similar['topic_id']] = POST_GLOBAL;
        	   }
	           $phpbb_seo->seo_url['topic'][$similar['topic_id']] = $phpbb_seo->format_url(censor_text($similar['topic_title']));
    	    }
        	if ( empty($phpbb_seo->seo_url['forum'][$similar['forum_id']]) ) {
	           $phpbb_seo->seo_url['forum'][$similar['forum_id']] = $phpbb_seo->set_url($similar['forum_name'], $similar['forum_id'], $phpbb_seo->seo_static['forum']);
    	    }
        	// http://www.phpBB-SEO.com SEO TOOLKIT END

	        $similar_forum_url   = append_sid("{$phpbb_root_path}viewforum.$phpEx", "f=" . $similar['forum_id']);
    	    $similar_topic_url   = append_sid("{$phpbb_root_path}viewtopic.$phpEx", "f=" . $similar['forum_id'] . '&t=' . $similar['topic_id']);
			$similar_user		= get_username_string('full', $similar['user_id'], $similar['username'], $similar['user_colour'], $similar['username']);

			$template->assign_block_vars('similar', array(
				'TOPIC_TITLE'			=> $similar['topic_title'],
				'TOPIC_DESCRIPTION'			=> $similar['topic_description'],
				'U_TOPIC'				=> $similar_topic_url,
				'REPLIES'				=> $similar['topic_replies'],
				'VIEWS'             => $similar['topic_views'],
				'USER'					=> $similar_user,
				'U_FORUM'				=> $similar_forum_url,
				'FORUM'					=> $similar['forum_name'])
			);
		}
	}
}
// End Precise Similar Topics
User avatar
hejowicz
Registered User
Posts: 442
Joined: Sun Dec 23, 2007 4:03 pm

Re: [DEV] Precise Similar Topics

Post by hejowicz »

Is this compatible with advanced seo url from phpbb-seo.com ?
endeffects
Registered User
Posts: 27
Joined: Thu Sep 18, 2008 2:40 pm

Re: [DEV] Precise Similar Topics

Post by endeffects »

yes my version is, just remove the t.description values.
but i still need help to prevent duplicated topic titles on output
User avatar
Ahri89
Registered User
Posts: 524
Joined: Sat Apr 07, 2007 2:53 pm
Location: Spain
Contact:

Re: [DEV] Precise Similar Topics

Post by Ahri89 »

great mod!
MODs translated to Spanish: ACP Add User MOD, ACP Announcement Centre, Advertisement Management, Custom Profile Fields, Update Reminder, Flood time setting per forum, MOD Version Check, Posting template, Prune Log, Simple Trash Bin, TinyPic Plugin, User Reminder, View Profile, View your topics, and more...

Sorry for my bad english xD
gavo
Registered User
Posts: 91
Joined: Sat Jun 23, 2007 10:44 am

Re: [DEV] Precise Similar Topics

Post by gavo »

Thanks excellant mod, it is running well & the search results seem very good.

rather than limit the search to certain forums how would you exclude 2 forums from the results, this would be a lot easier than a big array.

Thanks
ptex
Registered User
Posts: 21
Joined: Wed Feb 06, 2008 3:55 pm

Re: [DEV] Precise Similar Topics

Post by ptex »

I'm wondering if there's a way to make this work for InnoDB storage engine.
Having just "phpbb_topics" in MyIsam while everything else is in InnoDB seems rather inconsistent and I prefer to avoid it.

Any help would be immensely welcome since this is one of the most useful (and simple) mods I know :)
IntellectualThinker
Registered User
Posts: 30
Joined: Tue Dec 23, 2008 5:15 pm

Re: [DEV] Precise Similar Topics

Post by IntellectualThinker »

I installed this one on my forums and it has just a couple of errors. Here is a screen shot.

Image

Any ideas?
A New Era In Political Discussion
http://discusspolitics.org
stokerpiller
Registered User
Posts: 1934
Joined: Wed Feb 28, 2007 8:06 pm

Re: [DEV] Precise Similar Topics

Post by stokerpiller »

IntellectualThinker wrote:I installed this one on my forums and it has just a couple of errors. Here is a screen shot.

Image

Any ideas?
Yes, you can use the html provided in first post. ;)
I am done with phpBB
Akbaio
Registered User
Posts: 49
Joined: Fri Dec 05, 2008 1:33 am

Re: [DEV] Precise Similar Topics

Post by Akbaio »

If I modify this:

Code: Select all

AND t.topic_time > (UNIX_TIMESTAMP() - 60*60*24*365)
to:

Code: Select all

AND t.topic_time > (UNIX_TIMESTAMP() - 60*60*24*1460)
the mod will show related topic over the last 4 year?
youngjediknight
Registered User
Posts: 385
Joined: Wed Apr 22, 2009 2:58 pm

Re: [DEV] Precise Similar Topics

Post by youngjediknight »

Cool mod and very streamlined.

Would this observe forum permissions so it shows only similar topics in forums for which the user has access to?
Akbaio
Registered User
Posts: 49
Joined: Fri Dec 05, 2008 1:33 am

Re: [DEV] Precise Similar Topics

Post by Akbaio »

yes, it observe the permissions
youngjediknight
Registered User
Posts: 385
Joined: Wed Apr 22, 2009 2:58 pm

Re: [DEV] Precise Similar Topics

Post by youngjediknight »

Thanks for the response, Akbaio.

I'm going to try and reformat the results so it looks more like the standard topic list template.

Would it be too cumbersome to have the topics populate as the user enters in a topic when starting a new topic? So that the user might see there is already another topic exactly like the one he/she was about to post.
Locked

Return to “[3.0.x] MODs in Development”