Related Topics

[L.G.S]

Where is this mod supposed to appear? It installed successfully but hasn't appeared anywhere???

[niko]

Where is this mod supposed to appear? It installed successfully but hasn't appeared anywhere???

Do you use default theme?

It should appear under messages

And it won't appear if message doesn't have related topics.

see Display.template.php and find related it should be at bottom.

For different themes just get code from messageindex and replace

foreach ($context['topics'] as $topic) with $context['related'] as $topic edit header and footer so it works put whole code in if (count($context['related']) > 0)
{
... code ...
}

[L.G.S]

I use a custom theme that uses the default template and english files, so it should be showing

I looked in the default theme as well, not there either.

[Mazeman]

And Sql server is old  and I can't change that to newer cause it isn't my server
3.23.58 is the version what PhpMyAdmin shows.

Here is the SQL query what is causing the prob.

Code Select Expand

$result = db_query("
SELECT
t.ID_TOPIC, t.numReplies, t.locked, t.numViews, t.isSticky, t.ID_POLL,
" . ($user_info['is_guest'] ? '0' : 'IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, -1)) + 1') . " AS new_from,
t.ID_LAST_MSG, ml.posterTime AS lastPosterTime, ml.ID_MSG_MODIFIED,
ml.subject AS lastSubject, ml.icon AS lastIcon, ml.posterName AS lastMemberName,
ml.ID_MEMBER AS lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime,
mf.subject AS firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS firstDisplayName,
LEFT(ml.body, 384) AS lastBody, LEFT(mf.body, 384) AS firstBody, ml.smileysEnabled AS lastSmileys,
mf.smileysEnabled AS firstSmileys, b.ID_BOARD, b.name
FROM {$db_prefix}messages AS ms2
JOIN {$db_prefix}boards AS b ON (b.ID_BOARD = ms2.ID_BOARD)
JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = ms2.ID_TOPIC)
JOIN {$db_prefix}messages AS ml ON (ml.ID_MSG = t.ID_LAST_MSG)
JOIN {$db_prefix}messages AS mf ON (mf.ID_MSG = t.ID_FIRST_MSG)
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC = t.ID_TOPIC AND lt.ID_MEMBER = $ID_MEMBER)
LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD = b.ID_BOARD AND lmr.ID_MEMBER = $ID_MEMBER)") . "
WHERE $user_info[query_see_board] AND MATCH(ms2.subject, ms2.body) AGAINST('$subject') AND NOT t.ID_TOPIC = $topic
GROUP BY ID_TOPIC
ORDER BY MATCH(ms2.subject, ms2.body) AGAINST('$subject')
LIMIT 0,5", __FILE__, __LINE__);

Edit: Wrong code was there
But now that's the right one.

[niko]

Can you try replacing it with this

Code Select Expand


$result = db_query("
SELECT
t.ID_TOPIC, t.numReplies, t.locked, t.numViews, t.isSticky, t.ID_POLL,
" . ($user_info['is_guest'] ? '0' : 'IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, -1)) + 1') . " AS new_from,
t.ID_LAST_MSG, ml.posterTime AS lastPosterTime, ml.ID_MSG_MODIFIED,
ml.subject AS lastSubject, ml.icon AS lastIcon, ml.posterName AS lastMemberName,
ml.ID_MEMBER AS lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime,
mf.subject AS firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS firstDisplayName,
LEFT(ml.body, 384) AS lastBody, LEFT(mf.body, 384) AS firstBody, ml.smileysEnabled AS lastSmileys,
mf.smileysEnabled AS firstSmileys, b.ID_BOARD, b.name
FROM ({$db_prefix}messages AS ms2, {$db_prefix}boards AS b, {$db_prefix}topics AS t, {$db_prefix}messages AS ml, {$db_prefix}messages  AS mf)
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC = t.ID_TOPIC AND lt.ID_MEMBER = $ID_MEMBER)
LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD = b.ID_BOARD AND lmr.ID_MEMBER = $ID_MEMBER)") . "
WHERE b.ID_BOARD = ms2.ID_BOARD AND t.ID_TOPIC = ms2.ID_TOPIC
AND ml.ID_MSG = t.ID_LAST_MSG AND mf.ID_MSG = t.ID_FIRST_MSG
AND $user_info[query_see_board] AND MATCH(ms2.subject, ms2.body) AGAINST('$subject') AND NOT t.ID_TOPIC = $topic
GROUP BY ID_TOPIC
ORDER BY MATCH(ms2.subject, ms2.body) AGAINST('$subject')
LIMIT 0,5", __FILE__, __LINE__);


of course you can ask your host to update mysql but I think I should make this mod work with MySQL 3.x

[Mazeman]

Now the error is gone. But I cant see any related topics and I even have topics with same name...

[niko]

Do you have code in Display.template.php?

if not add

Code Select Expand


if (count($context['related']) > 0)
{
echo '
<div class="tborder" ', $context['browser']['needs_size_fix'] && !$context['browser']['is_ie6'] ? 'style="width: 100%;"' : '', '>
<table border="0" width="100%" cellspacing="1" cellpadding="4" class="bordercolor">
<tr>
<td width="9%" colspan="2" class="catbg3"></td>
<td class="catbg3">', $txt[70],'</td>
<td class="catbg3">', $txt[109],'</td>
<td class="catbg3">', $txt[110],'</td>
<td class="catbg3">', $txt[301],'</td>
<td class="catbg3">', $txt[111],'</td>
</tr>';

foreach ($context['related'] as $topic)
{
// Do we want to seperate the sticky and lock status out?
if (!empty($settings['seperate_sticky_lock']) && strpos($topic['class'], 'sticky') !== false)
$topic['class'] = substr($topic['class'], 0, strrpos($topic['class'], '_sticky'));
if (!empty($settings['seperate_sticky_lock']) && strpos($topic['class'], 'locked') !== false)
$topic['class'] = substr($topic['class'], 0, strrpos($topic['class'], '_locked'));

echo '
<tr>
<td class="windowbg2" valign="middle" align="center" width="5%">
<img src="', $settings['images_url'], '/topic/', $topic['class'], '.gif" alt="" />
</td>
<td class="windowbg2" valign="middle" align="center" width="4%">
<img src="', $topic['first_post']['icon_url'], '" alt="" />
</td>
<td class="windowbg' , !empty($settings['seperate_sticky_lock']) && $topic['is_sticky'] ? '3' : '' , '" valign="middle" ', (!empty($topic['quick_mod']['remove']) ? 'id="topic_' . $topic['first_post']['id'] . '" onmouseout="mouse_on_div = 0;" onmouseover="mouse_on_div = 1;" ondblclick="modify_topic(\'' . $topic['id'] . '\', \'' . $topic['first_post']['id'] . '\', \'' . $context['session_id'] . '\');"' : ''), '>';

if (!empty($settings['seperate_sticky_lock']))
echo '
' , $topic['is_locked'] ? '<img src="' . $settings['images_url'] . '/icons/quick_lock.gif" align="right" alt="" id="lockicon' . $topic['first_post']['id'] . '" style="margin: 0;" />' : '' , '
' , $topic['is_sticky'] ? '<img src="' . $settings['images_url'] . '/icons/show_sticky.gif" align="right" alt="" id="stickyicon' . $topic['first_post']['id'] . '" style="margin: 0;" />' : '';

echo '
', $topic['is_sticky'] ? '<b>' : '' , '<span id="msg_' . $topic['first_post']['id'] . '">', $topic['first_post']['link'], '</span>', $topic['is_sticky'] ? '</b>' : '';

// Is this topic new? (assuming they are logged in!)
if ($topic['new'] && $context['user']['is_logged'])
echo '
<a href="', $topic['new_href'], '" id="newicon' . $topic['first_post']['id'] . '"><img src="', $settings['images_url'], '/', $context['user']['language'], '/new.gif" alt="', $txt[302], '" /></a>';

echo '
<small id="pages' . $topic['first_post']['id'] . '">', $topic['pages'], '</small>
</td>
<td class="windowbg2" valign="middle" width="14%">
', $topic['first_post']['member']['link'], '
</td>
<td class="windowbg' , $topic['is_sticky'] ? '3' : '' , '" valign="middle" width="4%" align="center">
', $topic['replies'], '
</td>
<td class="windowbg' , $topic['is_sticky'] ? '3' : '' , '" valign="middle" width="4%" align="center">
', $topic['views'], '
</td>
<td class="windowbg2" valign="middle" width="22%">
<a href="', $topic['last_post']['href'], '"><img src="', $settings['images_url'], '/icons/last_post.gif" alt="', $txt[111], '" title="', $txt[111], '" style="float: right;" /></a>
<span class="smalltext">
', $topic['last_post']['time'], '<br />
', $txt[525], ' ', $topic['last_post']['member']['link'], '
</span>
</td>';

echo '
</tr>';

}

echo '
</table>
</div>';
}


before

if ($context['show_spellchecking']) at almost end of file.

[Mazeman]

yup, the code is there.
But nothing...

[niko]

Then it's not recognized by MySQL as related, it will search by subject from subject and body.

[Mazeman]

Then it's not recognized by MySQL as related, it will search by subject from subject and body.

So what I should do?
Delete the mod and let it be?
Or write my own mod, that does the trick 

[niko]

Eventually there will be topics that are related. I don't exactly know how it is calculated but from original message only topic is used to find related topics using MySQL's own function MATCH AGAINST.

Words that appear in post very often are not taken into count. Words under 4 characters are also excluded from index unless mysql config is configured to smaller limit

[L.G.S]

so what's the point in making this mod if you don't even know when it will work? None of my threads have shown related topics yet, and I've checked most of them.

Doubt the IPB and VB ones make you guess when they will start working

[niko]

so what's the point in making this mod if you don't even know when it will work? None of my threads have shown related topics yet, and I've checked most of them.

Doubt the IPB and VB ones make you guess when they will start working

Why would you have guess? It will work when theres topics that are related. Words most not be usually used.
if 1/2 posts have word "mysql" it won't be used. php will be never used on default mysql settings.

Actually this is very similar to how vbulletin does this.

if you want exactly how vbulletin does

replace

Code Select Expand


AND $user_info[query_see_board] AND MATCH(ms2.subject, ms2.body) AGAINST('$subject') AND NOT t.ID_TOPIC = $topic
GROUP BY ID_TOPIC
ORDER BY MATCH(ms2.subject, ms2.body) AGAINST('$subject')


with

Code Select Expand


AND $user_info[query_see_board] AND MATCH(ms2.subject) AGAINST('$subject') AND NOT t.ID_TOPIC = $topic
GROUP BY ID_TOPIC
ORDER BY MATCH(ms2.subject) AGAINST('$subject')


And make fulltext index for subject

[L.G.S]

Yes and what will that do?

I've been through at least 100 topics which seem to be the most original, and none of them have given me related topics. What's the point in having it if it's only EXACT related topics, which will probably never be found

[niko]

it's not does not need to be exact to be found as related.

I have no problems finding topics with related topics at forum with 177 topics.

Subject must have words that are in appear less than < 1/2 posts and are not stopwords

See: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html for more information.

Did you try making it only use subject like I told you in previous post?

[L.G.S]

Sorry I did not mean to be rude towards you.

How do I make the modification?

[Daniel15]

Hmmm... . I tried this on a development version of the forum on daniel15.com, but the posts it finds seem to be completely unrelated (they just have very common words in them, like 'SMFShop').
Any way to make it return... "better" results?

P.S Yeah, I do understand that it's probably a limitation of the MySQL FULLTEXT engine.

[lupus]

Sorry must be having a real thick day...

Where does this show up and how do i get it to work, i've installed it no problem but unlike most mods i can't see where its supposed to be visable...

[Daniel15]

Where does this show up and how do i get it to work, i've installed it no problem but unlike most mods i can't see where its supposed to be visable...

If there's any posts related to the current one, they will appear underneath the last post in the topic (or under the Quick Reply, if you have it enabled). Check your default theme.
If you're using a different theme, you'll need to edit the Display.template.php file for that theme.

[lupus]

Where does this show up and how do i get it to work, i've installed it no problem but unlike most mods i can't see where its supposed to be visable...

If there's any posts related to the current one, they will appear underneath the last post in the topic (or under the Quick Reply, if you have it enabled). Check your default theme.
If you're using a different theme, you'll need to edit the Display.template.php file for that theme.

Ah right okay...Thought i was having a Blonde moment or something...

Any idea what i'm looking for as i'm kinda new to all this (Using the Helios_Multi Theme)...Thanks for any help...