AJAX Recent Topics

[SlammedDime]

The times will not update automatically.  That would be far too much javascript work.

As for server load: it's less server load than someone pushing F5 to refresh the page.

I already touched on that up here.  I will look at making that change though.

[digit]

I am not sure if it's just something to do with my server or?

It seems that it's taking longer and longer to load this ajax enabled recent topics page. 

Am I alone?

[SlammedDime]

Perhaps your site isn't as busy as you would like?  Link?

[digit]

My site's VERY busy - it updates every minute or so (works GREAT!)    I mean, it seems to take longer and longer just to access the url.  I have PM'd you the URL.

When testing it at....  http://www.websitepulse.com/ my forum's base URL responds in under a second. 

The ajax enabled recent topics URL takes over 7 seconds - but when I first installed it, it responded in just about 2 seconds.

I'm concerned it's straining the server somehow.

[SlammedDime]

That website is a little inaccurate, at least from where I'm at.  The ajax requests are only taking on average around 1.5 seconds, which isn't too bad, but it may be creating some server strain on mysql because of the size of your messages table, but this could possibly be alleviated...
Try running this query on the messages table in the database:

ALTER TABLE `[YOUR DB PREFIX]_messages` ADD INDEX `last_post_time` ( `posterTime` ) 

[digit]

That website is a little inaccurate, at least from where I'm at.  The ajax requests are only taking on average around 1.5 seconds, which isn't too bad, but it may be creating some server strain on mysql because of the size of your messages table, but this could possibly be alleviated...
Try running this query on the messages table in the database:

ALTER TABLE `[YOUR DB PREFIX]_messages` ADD INDEX `last_post_time` ( `posterTime` ) 

Thanks I'll try that.... I'll have to do that a bit later during off peak hours - need to do a backup first.

What will that do?

Thanks!

[SlammedDime]

All it does is add an index to the table to make lookups on certain fields faster.  Since this mod relys on the posterTime field in the messages table, without an index, the query starts from the beginning of the table and goes to the end.  This should significantly increase the query speed, allowing mysql to jump straight to the end of the table to start its query.  No need to really do a backup (always a good idea anyway), as this doesn't alter any data in the table itself.

If this works for you (please let me know when you've done it so I can take a look at your site again), I'll include it in the next release for Beta 4.

[digit]

Hmm...  for some reason, it's hanging when I try to run that command....

I also noticed some warnings about that table...

UNIQUE and INDEX keys should not both be set for column `ID_TOPIC`
UNIQUE and INDEX keys should not both be set for column `ID_MEMBER`
More than one INDEX key was created for column `ID_MEMBER`

Is this normal?

EDIT: seems that SQL query eventually worked! Will report back.

[digit]

After testing it - base forum responds in less than a second, recent topics page is responding in 5-6 seconds - so it is a little bit faster! 

[SlammedDime]

Well the AJAX requests are definately much much faster... only 97 milliseconds, compared with 1.5-2 second averages from before, so the AJAX part is much better.  As for initial page load, I'll take a look at the query and see if there's a way I can speed it up some.  I can't promise much, but I'll see what I can do.


And just to clarify, that site you're using to test is only testing initial page loads.  So while it reports 6-7 second load times, thats only for the initial page load, that is not for the AJAX requests.  I'm only getting 4-5 second page loads from my end.

Edit: After looking at the indexes on the topics and messages table, I'm sure I can increase the performance of the query.  I'll use you as my test guinea pig if you don't mind....   Don't worry, I won't break your forum.

[digit]

I'll use you as my test guinea pig if you don't mind....  

Test away!   

I'll be waiting for that ajax who's online list too!    lol

[SlammedDime]

I'll use you as my test guinea pig if you don't mind....  

Test away!  

I'll be waiting for that ajax who's online list too!    lol

I'll mess around with the query when I get home from work tonight. 

[SlammedDime]

Okay digit, give this a shot

Open Sources/Recent.php, in the function RecentTopics()

Code (Find) Select Expand

$latest_post = !empty($_REQUEST['latest']) ? 'AND m.PosterTime > ' . (int) $_REQUEST['latest'] : '';

$context['topics'] = array();
// Find all the posts in distinct topics.  Newer ones will have higher IDs.
$request = db_query("
SELECT
ms.posterTime as firstTime, m.posterTime as lastTime, ms.subject, m.ID_TOPIC, t.numReplies,
ms.ID_MEMBER as ID_FIRST_POSTER, m.ID_MEMBER as ID_LAST_POSTER, m.ID_MSG, b.ID_BOARD, b.name AS bName,
IFNULL(mem2.realName, ms.posterName) AS firstPoster,
IFNULL(mem.realName, m.posterName) AS lastPoster
FROM ({$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b, {$db_prefix}messages AS ms)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER = t.ID_MEMBER_STARTED)
WHERE t.ID_LAST_MSG = m.ID_MSG
AND b.ID_BOARD = t.ID_BOARD
AND $query_this_board
AND ms.ID_MSG = t.ID_FIRST_MSG
$latest_post
ORDER BY t.ID_LAST_MSG DESC
LIMIT " . $settings['number_recent_topics'], __FILE__, __LINE__);


and

Code (Replace) Select Expand

$latest_post = !empty($_REQUEST['latest']) ? 'AND m.PosterTime > ' . (int) $_REQUEST['latest'] : '';

$min_message_id = $modSettings['maxMsgID'] - (int) (($modSettings['totalMessages'] / $modSettings['totalTopics']) * $settings['number_recent_topics']);

$context['topics'] = array();
// Find all the posts in distinct topics.  Newer ones will have higher IDs.
$request = db_query("
SELECT
ms.posterTime as firstTime, m.posterTime as lastTime, ms.subject, m.ID_TOPIC, t.numReplies,
ms.ID_MEMBER as ID_FIRST_POSTER, m.ID_MEMBER as ID_LAST_POSTER, m.ID_MSG, b.ID_BOARD, b.name AS bName,
IFNULL(mem2.realName, ms.posterName) AS firstPoster,
IFNULL(mem.realName, m.posterName) AS lastPoster
FROM {$db_prefix}topics AS t
INNER JOIN {$db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN {$db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {$db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER = t.ID_MEMBER_STARTED)
WHERE t.ID_LAST_MSG >= $min_message_id
AND $query_this_board
$latest_post
ORDER BY t.ID_LAST_MSG DESC
LIMIT " . $settings['number_recent_topics'], __FILE__, __LINE__);


See what that does for you.

[digit]

Okay digit, give this a shot

Open Sources/Recent.php, in the function RecentTopics()

[snip]

See what that does for you.

BAM!  Lightning fast!     Thanks!

[digit]

Slight problem.

It seems that by clicking on the last post icon, I am not actually getting the last post.

It links to a post like...

http://www.mysite.com/index.php/topic,168956.new/topicseen.html#new

When the last post is more like....

http://www.mysite.com/index.php/topic,168956.msg1230733.html#msg1230733

Thanks if you can fix that, and thanks again for fixing the speed issue.

[SlammedDime]

It should be taking you to the newest post.  SMF has a feature whereas if you put .new in there, like I have, Display.php does an extra query to figure out what the newest post is for the current user and directs them to it upon page load.  I'll look into this a bit further and get back to you.

[thomas86]

Hello,

This mod is geat, but i thnik how i make to display this Recent Post Instead of Default Recent Post on Home Page Board...

I Think thats I should edit board.index.template on Themes/default ... but when I edit this file The Recent Post was displayed on Home Page but it isn't Displayed nofing on table were should displaed post ...
I past this code on home page...

Code Select Expand

echo '
<div class="tborder" ', $context['browser']['needs_size_fix'] && !$context['browser']['is_ie6'] ? 'style="width: 100%;"' : '', '>
<table border="0" width="100%" cellspacing="0" cellpadding="4" class="bordercolor" id="topicTable">
<tr>';

// Are there actually any topics to show?
echo '
<td width="15%" class="catbg3"><strong>', $txt['smf82'], '</strong></td>
<td class="catbg3"><strong>', $txt[118], '</strong></td>
<td width="50" class="catbg3" align="center"><strong>', $txt[110], '</strong></td>
<td width="150" class="catbg3" align="center"><strong>', $txt[109], '</strong></td>
<td width="150" class="catbg3" align="center"><strong>', $txt[22], '</strong></td>
<td width="16" class="catbg3"></td>';

// No topics.... just say, "sorry bub".
if (empty($context['topics']))
echo '
</tr>
<tr id="no_topics">
<td class="windowbg2" width="100%" colspan="6"><strong>', $txt[151], '</strong></td>';

echo '
</tr>';

foreach ($context['topics'] as $topic)
{
echo '
<tr class="windowbg2" id="topic_', $topic['id'], '">
<td class="smalltext" style="padding-left: 10px; border-bottom: 1px solid rgb(204, 204, 204);">', $topic['board']['link'], '</td>
<td style="padding-left: 10px; border-bottom: 1px solid rgb(204, 204, 204);">', $topic['link'], '</td>
<td align="center" class="smalltext" style="border-bottom: 1px solid rgb(204, 204, 204);">', $topic['replies'], '</td>
<td align="center" class="smalltext" style="border-bottom: 1px solid rgb(204, 204, 204);">', $topic['firstPoster']['link'], '<br />', $topic['firstPoster']['time'], '</td>
<td align="center" class="smalltext" style="border-bottom: 1px solid rgb(204, 204, 204);">', $topic['lastPoster']['link'], '<br />', $topic['lastPoster']['time'], '</td>
<td align="center" style="border-bottom: 1px solid rgb(204, 204, 204);"><a href="', $topic['lastPost']['href'], '"><img src="', $settings['images_url'], '/icons/last_post.gif" alt="', $txt[111], '" title="', $txt[111], '" /></a></td>
</tr>';
}

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

// Now for all of the javascript stuff
echo '
<script language="Javascript" type="text/javascript"><!-- // -->
var last_post = ', (!empty($context['last_post_time']) ? $context['last_post_time'] : 0), ';
var time_interval = ', $settings['number_recent_topics_interval'] * 1000, ';
var max_topics = ', $settings['number_recent_topics'], ';

var interval_id = setInterval( "getTopics()", time_interval);

function getTopics()
{
if (window.XMLHttpRequest)
getXMLDocument("', $scripturl, '?action=recenttopics;latest=" + last_post + ";xml", gotTopics);
else
clearInterval(interval_id);
}

function gotTopics(XMLDoc)
{
var updated_time = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("lastTime")[0];
var topics = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("topic");
var topic, id_topic, board, subject, replies, firstPost, lastPost, link;
var myTable = document.getElementById("topicTable"), oldRow, myRow, myCell, myData, rowCount;

// If this exists, we have at least one updated/new topic
if (updated_time)
{
// Update the last post time
last_post = updated_time.childNodes[0].nodeValue;

// No Messages message?  Ditch it!
// Note, this should only happen if there are literally zero topics
// on the board when a user visits this page.
if (document.getElementById("no_topics") != null)
myTable.deleteRow(-1);

// If the topic is already in the list, remove it
for (var i = 0; i < topics.length; i++)
{
topic = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("topic")[i];
id_topic = topic.getElementsByTagName("id")[0].childNodes[0].nodeValue;
if ((oldRow = document.getElementById("topic_" + id_topic)) != null)
myTable.deleteRow(oldRow.rowIndex);
}

// Are we going to exceed the maximum topic count allowed?
while (((myTable.rows.length - 1 + topics.length) - max_topics) > 0)
myTable.deleteRow(-1);

// Now start the insertion
for (var i = 0; i < topics.length; i++)
{
// Lets get all of our data
topic = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("topic")[i];
id_topic = topic.getElementsByTagName("id")[0].childNodes[0].nodeValue;
board = topic.getElementsByTagName("board")[0].childNodes[0].nodeValue;
subject = topic.getElementsByTagName("subject")[0].childNodes[0].nodeValue;
replies = topic.getElementsByTagName("replies")[0].childNodes[0].nodeValue;
firstPost = topic.getElementsByTagName("first")[0].childNodes[0].nodeValue;
lastPost = topic.getElementsByTagName("last")[0].childNodes[0].nodeValue;
link = topic.getElementsByTagName("lastLink")[0].childNodes[0].nodeValue;

// Now to create the new row...
myRow = myTable.insertRow(1);
myRow.id = "topic_" + id_topic;
myRow.className = "windowbg";

// First the Board
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.style.paddingLeft = "10px";
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, board);

// Then subject
myCell = myRow.insertCell(-1);
myCell.style.paddingLeft = "10px";
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, subject);

// replies
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, replies);

// first post
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, firstPost);

// last post
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, lastPost);

// last post
myCell = myRow.insertCell(-1);
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, link);
}
}
}
// ]]></script>';
...
When I refresh page there are nothing on recent post ... and after few minuts the recent post is displayed ...

I think why the recent post isn't display "at now" after i refresh page...
And on end my post I would like say "sorry" for my poor english...


I'll Thankful for help,
Regards,
Thomas

[SlammedDime]

You would need to add code to BoardIndex.php in the Sources directory to perform the SQL query.  You'd have to copy the SQL part of the RecentTopics() function from Recent.php and you should be able to get something to work.

[digit]

It should be taking you to the newest post.  SMF has a feature whereas if you put .new in there, like I have, Display.php does an extra query to figure out what the newest post is for the current user and directs them to it upon page load.  I'll look into this a bit further and get back to you.

Well, this may be more of a browser issue - (Safari on a Mac) I seem to have the same problem here at smf.... so ignore that.

On another note:  How would I restrict access to the Ajax Recent results to my Charter Members only (group ID 9)... 'any easy way to do it?

Thanks!

[thomas86]

You would need to add code to BoardIndex.php in the Sources directory to perform the SQL query.  You'd have to copy the SQL part of the RecentTopics() function from Recent.php and you should be able to get something to work.

Hello,
hmmm.. I edit file BoardIndex.template.php and I paste

Code Select Expand

//recent post


echo '
<div class="tborder" ', $context['browser']['needs_size_fix'] && !$context['browser']['is_ie6'] ? 'style="width: 100%;"' : '', '>
<table border="0" width="100%" cellspacing="0" cellpadding="4" class="bordercolor" id="topicTable">
<tr>';

// Are there actually any topics to show?
echo '
<td width="15%" class="catbg3"><strong>', $txt['smf82'], '</strong></td>
<td class="catbg3"><strong>', $txt[118], '</strong></td>
<td width="50" class="catbg3" align="center"><strong>', $txt[110], '</strong></td>
<td width="150" class="catbg3" align="center"><strong>', $txt[109], '</strong></td>
<td width="150" class="catbg3" align="center"><strong>', $txt[22], '</strong></td>
<td width="16" class="catbg3"></td>';

// No topics.... just say, "sorry bub".
if (empty($context['topics']))
echo '
</tr>
<tr id="no_topics">
<td class="windowbg2" width="100%" colspan="6"><strong>', $txt[151], '</strong></td>';

echo '
</tr>';

foreach ($context['topics'] as $topic)
{
echo '
<tr class="windowbg2" id="topic_', $topic['id'], '">
<td class="smalltext" style="padding-left: 10px; border-bottom: 1px solid rgb(204, 204, 204);">', $topic['board']['link'], '</td>
<td style="padding-left: 10px; border-bottom: 1px solid rgb(204, 204, 204);">', $topic['link'], '</td>
<td align="center" class="smalltext" style="border-bottom: 1px solid rgb(204, 204, 204);">', $topic['replies'], '</td>
<td align="center" class="smalltext" style="border-bottom: 1px solid rgb(204, 204, 204);">', $topic['firstPoster']['link'], '<br />', $topic['firstPoster']['time'], '</td>
<td align="center" class="smalltext" style="border-bottom: 1px solid rgb(204, 204, 204);">', $topic['lastPoster']['link'], '<br />', $topic['lastPoster']['time'], '</td>
<td align="center" style="border-bottom: 1px solid rgb(204, 204, 204);"><a href="', $topic['lastPost']['href'], '"><img src="', $settings['images_url'], '/icons/last_post.gif" alt="', $txt[111], '" title="', $txt[111], '" /></a></td>
</tr>';
}

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

// Now for all of the javascript stuff
echo '
<script language="Javascript" type="text/javascript"><!-- // -->
var last_post = ', (!empty($context['last_post_time']) ? $context['last_post_time'] : 0), ';
var time_interval = ', $settings['number_recent_topics_interval'] * 1000, ';
var max_topics = ', $settings['number_recent_topics'], ';

var interval_id = setInterval( "getTopics()", time_interval);

function getTopics()
{
if (window.XMLHttpRequest)
getXMLDocument("', $scripturl, '?action=recenttopics;latest=" + last_post + ";xml", gotTopics);
else
clearInterval(interval_id);
}

function gotTopics(XMLDoc)
{
var updated_time = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("lastTime")[0];
var topics = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("topic");
var topic, id_topic, board, subject, replies, firstPost, lastPost, link;
var myTable = document.getElementById("topicTable"), oldRow, myRow, myCell, myData, rowCount;

// If this exists, we have at least one updated/new topic
if (updated_time)
{
// Update the last post time
last_post = updated_time.childNodes[0].nodeValue;

// No Messages message?  Ditch it!
// Note, this should only happen if there are literally zero topics
// on the board when a user visits this page.
if (document.getElementById("no_topics") != null)
myTable.deleteRow(-1);

// If the topic is already in the list, remove it
for (var i = 0; i < topics.length; i++)
{
topic = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("topic")[i];
id_topic = topic.getElementsByTagName("id")[0].childNodes[0].nodeValue;
if ((oldRow = document.getElementById("topic_" + id_topic)) != null)
myTable.deleteRow(oldRow.rowIndex);
}

// Are we going to exceed the maximum topic count allowed?
while (((myTable.rows.length - 1 + topics.length) - max_topics) > 0)
myTable.deleteRow(-1);

// Now start the insertion
for (var i = 0; i < topics.length; i++)
{
// Lets get all of our data
topic = XMLDoc.getElementsByTagName("smf")[0].getElementsByTagName("topic")[i];
id_topic = topic.getElementsByTagName("id")[0].childNodes[0].nodeValue;
board = topic.getElementsByTagName("board")[0].childNodes[0].nodeValue;
subject = topic.getElementsByTagName("subject")[0].childNodes[0].nodeValue;
replies = topic.getElementsByTagName("replies")[0].childNodes[0].nodeValue;
firstPost = topic.getElementsByTagName("first")[0].childNodes[0].nodeValue;
lastPost = topic.getElementsByTagName("last")[0].childNodes[0].nodeValue;
link = topic.getElementsByTagName("lastLink")[0].childNodes[0].nodeValue;

// Now to create the new row...
myRow = myTable.insertRow(1);
myRow.id = "topic_" + id_topic;
myRow.className = "windowbg";

// First the Board
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.style.paddingLeft = "10px";
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, board);

// Then subject
myCell = myRow.insertCell(-1);
myCell.style.paddingLeft = "10px";
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, subject);

// replies
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, replies);

// first post
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, firstPost);

// last post
myCell = myRow.insertCell(-1);
myCell.className = "smalltext";
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, lastPost);

// last post
myCell = myRow.insertCell(-1);
myCell.align = "center"
myCell.style.borderBottom = "1px solid rgb(204, 204, 204)";
setInnerHTML(myCell, link);
}
}
}
// ]]></script>';
In Board Index the Recent Topics was displayed but after few minuts... Example in standard Recent Post on site the results of new post and topic was displayed on page load ...

The problem is why the Recent Topics isn't display on load page such as standard (default) Recent post's after The Center Info place... ?
BTW  I edit BoardIndex.php too with your sugestion but the Recent Topics