RC5: Correct the following query to be 2.0 compliant & something else...

[MoreBloodWine]

The below query was originally written for 1.1.x but I recently upgraded my site and am now getting an error on it. I was wondering if someone could please fix it to be 2.0 compliant. There's also two other thing wrong with this query that will require andd SMF query guru.

1. It does it's job in showing unread posts since last visit just like the default SMF option available to all users on the header nead their avatar.but it doesnt show really old posts. Really old I guess could be a user defind thing because I don't know just how far back it looks. Anyway, if I mark an "old" post as new it doesnt show via the code but it does if I use the built in SMF Show unread posts since last visit. which is what all of this code is suppose to mimmic to the T.

2. This is just a minor annoyance but would love a patch for it, if a new user signs up the are this blockcode.php goes in is flooded with ALOT of posts where as with SMF's built in Show unread posts since last visit. it's empty as it should be since the user is new and is on their first visit.

Query:

Code Select Expand

$request = db_query("
SELECT
m.posterTime, ms.subject, m.ID_TOPIC, m.ID_MEMBER,
IFNULL(mem.realName, m.posterName) AS posterName, " . ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, -1)) + 1 AS new_from') . "
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)" . (!$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 t.ID_LAST_MSG = m.ID_MSG
AND b.ID_BOARD = t.ID_BOARD" . (empty($exclude_boards) ? '' : "
AND b.ID_BOARD NOT IN (" . implode(', ', $exclude_boards) . ")") . "
AND $user_info[query_see_board]
AND ms.ID_MSG = t.ID_FIRST_MSG
AND (lmr.ID_MSG IS NULL OR lmr.ID_MSG < m.ID_MSG_MODIFIED)
AND (lt.ID_MSG IS NULL OR lt.ID_MSG < m.ID_MSG_MODIFIED)
ORDER BY t.ID_LAST_MSG DESC
LIMIT $look_ahead", __FILE__, __LINE__);

Error:

Code Select Expand

Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LEFT JOIN `REMOVED`.smf_log_mark_read AS lmr ON lmr.ID_BOARD = b' at line 7

All of this code is from a custom recent posts block, one thats meant to mimic how show unread topics since last visit works. Anyway, the below pic is a snapshot of what the block looks like when there's no posts to show, however, there's these two annoying (for me anyway) line breaks at the bottom of the message and for the life of me I can't see where they're coming from. Chances are it's starin me right in the face and I'm just not seeing it. Or they're part of exising code to properly format something when posts are to be shown in which case they would need to be commented out when there's no posts to show. Anyway, if someone could also help me figure this one out that will be all I need form this post, Ty.



Anyway, Here's all current code from the entire setup.

blockcode.php

Code Select Expand

<?php
if($context['user']['is_logged']){
// Block Code to display unread topics from XML file.
// Needs unreadTopicsXml.php to function.

// Uses the prototype javascript library.  If you are already using this library simply
// disable the reference to the google api below.
$prototype = 0; // 1 = Enabled, 0 = Disabled

// Configuration

// Set the number of unread topics to show at any one time.
$num_topics = 10;

// Exclude some boards ? Then fill the array eg : array(1,13,20), if not then leave empty like this : array().
$exclude_boards= array();

// End of Config.


// The code....

if (!empty($exclude_boards))
$exclude_boards_string = implode("-" , $exclude_boards);
else
$exclude_boards_string = 'null';

global $boardurl, $settings, $context, $scripturl;

if($prototype == '1'){
echo '
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/prototype/1.6.1.0/prototype.js"></script>
';
}

echo '
<div id="notifier" class="smalltext"></div>
<div id="unreadtopicscontainer" style="width: 100%; overflow: auto; max-height: 78ex;">
</div>
<div id="markallread" class="smalltext" style="visibility: hidden">
<center>
<hr width="75%" />
<span style="cursor: pointer" onClick="markAllTopicsRead();">Mark all posts read.</span>
</center>
</div>';

// Pass the variables to the javascript and load the custom js.
echo '
<script type="text/javascript">
var xmlrequesturl = "' , $boardurl , '/tp-files/block-unreadtopics/unreadTopicsXml.php?numtopics=' , $num_topics , '&exclude=' , $exclude_boards_string , '";
var newimage = "' , $settings['images_url'] , '/' . $context['user']['language'] . '/new.gif";
var dismissbutton = "' , $boardurl , '/tp-files/block-unreadtopics/removeButton.jpg";
var scripturl = "' , $scripturl , '";
var sessionid = "' , $context['session_id'] , '";
var moretopics = "0";
</script>
<script type="text/javascript" src="' , $boardurl , '/tp-files/block-unreadtopics/showUnread.js"></script>
';
}else{
echo '<center>Please <a href="'.$scripturl.'?action=login">login</a> or <a href="'.$scripturl.'?action=register">register</a> to use this block.</center>';
}
?>

unreadTopicsXml.php

Code Select Expand

<?php
// This file generates an XML file of unread topics.

header("Content-type: text/xml");

require_once('../../SSI.php');

// Get any passed variables.
if (isset($_GET['numtopics']))
$num_topics = $_GET['numtopics'];
else
$num_topics = 10;

if (isset($_GET['exclude']) && $_GET['exclude'] != 'null')
{
$exclude_boards = explode("-", $_GET['exclude']);
if (!is_array($exclude_boards) || empty($exclude_boards))
$exclude_boards = null;
}
else
{
$exclude_boards = null;
}

global $context, $settings, $scripturl, $db_prefix, $ID_MEMBER;
global $user_info, $modSettings;

// Database query etc taken from SSI.php and adapted...
if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
$exclude_boards = array($modSettings['recycle_board']);
else
$exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;

// Lets just see if there are more messages than asked for...
$look_ahead = $num_topics + 1;

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
$request = db_query("
SELECT
m.posterTime, ms.subject, m.ID_TOPIC, m.ID_MEMBER,
IFNULL(mem.realName, m.posterName) AS posterName, " . ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, -1)) + 1 AS new_from') . "
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)" . (!$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 t.ID_LAST_MSG = m.ID_MSG
AND b.ID_BOARD = t.ID_BOARD" . (empty($exclude_boards) ? '' : "
AND b.ID_BOARD NOT IN (" . implode(', ', $exclude_boards) . ")") . "
AND $user_info[query_see_board]
AND ms.ID_MSG = t.ID_FIRST_MSG
AND (lmr.ID_MSG IS NULL OR lmr.ID_MSG < m.ID_MSG_MODIFIED)
AND (lt.ID_MSG IS NULL OR lt.ID_MSG < m.ID_MSG_MODIFIED)
ORDER BY t.ID_LAST_MSG DESC
LIMIT $look_ahead", __FILE__, __LINE__);

$posts = array();

while ($row = mysql_fetch_assoc($request))
{
// Censor the subject.
censorText($row['subject']);

// Build the array.
$posts[] = array(
'id_topic' => $row['ID_TOPIC'],
'poster_link' => empty($row['ID_MEMBER']) ? $row['posterName'] : htmlspecialchars('<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>'),
'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['new_from'] . ';topicseen#new',
'short_subject' => shorten_subject($row['subject'], 25),
'time' => htmlspecialchars(timeformat($row['posterTime']))
);
}

mysql_free_result($request);

$post_count = count($posts);

echo '<unreadtopics items="' , $post_count , '">';

$counter = 0;

if($post_count > 0)
{
foreach($posts as $post)
{
if ($counter < $num_topics)
echo '
<topic id="' , $post['id_topic'] , '" href="' , $post['href'] , '" poster="' , $post['poster_link'] ,'" time="' , $post['time'] , '">
' , $post['short_subject'] , '
</topic>';

$counter++;
}
}

echo '
</unreadtopics>';
?>

showUnread.js

Code Select Expand

var numberOfUnreadTopics;

function getUnreadTopics()
{
new Ajax.Request( xmlrequesturl, {
onSuccess: function( transport )
{
var unreadTopics = transport.responseXML.getElementsByTagName( "topic" );

numberOfUnreadTopics = unreadTopics.length

if (numberOfUnreadTopics == 0)
{
notifyNoTopics();
}
else
{
var lookahead = transport.responseXML.getElementsByTagName( "unreadtopics" );

moretopics = lookahead[0].getAttribute("items").strip();

notifySomeTopics(numberOfUnreadTopics, moretopics);

for ( var i = 0; i < unreadTopics.length; i++ )
{
var subject = unreadTopics[i].firstChild.nodeValue.strip();
var id = unreadTopics[i].getAttribute("id").strip();
var href = unreadTopics[i].getAttribute("href").strip();
var poster = unreadTopics[i].getAttribute("poster").strip();
var time = unreadTopics[i].getAttribute("time").strip();

var divTag = document.createElement("div");
divTag.className = "smalltext";
divTag.setAttribute("id", "topic" + id);
divTag.setAttribute("style", "overflow:hidden");
divTag.innerHTML = '<img border="0" src="' + dismissbutton + '" alt="Dismiss" title="Dismiss" onClick="markTopicRead(\'' + id + '\');" />';
divTag.innerHTML += ' <a href="' + href + '">' + subject + '</a>';
divTag.innerHTML += '<br />By: <strong>' + poster + '</strong>';
divTag.innerHTML += '<br /><a href="' + href + '"><img border="0" src="' + newimage + '" alt="new" /></a>';
divTag.innerHTML += ' [' + time + ']';

if (i < unreadTopics.length - 1)
divTag.innerHTML = divTag.innerHTML + "<hr />";

document.getElementById("unreadtopicscontainer").appendChild(divTag);
}
}
}
});
}

getUnreadTopics();

function notifyNoTopics()
{
document.getElementById("notifier").innerHTML = '<center>There are no recent<br />unread posts to display.</center>';
document.getElementById("markallread").style.visibility = "hidden";
}

function notifySomeTopics(count, moreitems)
{
if (moreitems > count)
{
document.getElementById("notifier").innerHTML = '<center>Showing ' + count + '<br />of<br />' + moreitems + '+ Unread Posts.<br /><hr width="75%" /></center>';
}
else
{
document.getElementById("notifier").innerHTML = '<center>Number of Unread Posts: ' + count + '<br /><hr width="75%" /></center>';
}
document.getElementById("markallread").style.visibility = "visible";
}

function markTopicRead(idTopic)
{
new Ajax.Request( scripturl + "?topic=" + idTopic , {
onSuccess: function( transport )
{
myCollapse("topic" + idTopic);
numberOfUnreadTopics -= 1;
if(numberOfUnreadTopics < 1)
getUnreadTopics();
else
{
moretopics -= 1;
notifySomeTopics(numberOfUnreadTopics, moretopics);
}
}
});
}

function markAllTopicsRead()
{
new Ajax.Request( scripturl + "?action=markasread;sa=all;sesc=" + sessionid , {
onSuccess: function( transport )
{
document.getElementById("unreadtopicscontainer").innerHTML = '';
notifyNoTopics();
}
});
}

function myCollapse(targetDiv)
{
var temp = document.getElementById(targetDiv).offsetHeight;
var height = parseInt(temp);
var max = height;
var min = 1;

if (height > min)
{
height -= 1;
document.getElementById(targetDiv).style.height = height + "px";
setTimeout("myCollapse(\'" + targetDiv + "\')", 1);
}
else
{
document.getElementById(targetDiv).style.visibility = "hidden";
}
}

[Illori]

since this is a coding issue, i am going to move it to the coding board.

[MoreBloodWine]

since this is a coding issue, i am going to move it to the coding board.

Thx, I wasnt really sure about where to put it but figured the 2.0 board because of the query / use of it all.

[SlammedDime]

Have you looked at SSI.php?  That query looks eerily familiar...

Edit:

Code (SSI.php) Select Expand

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
$request = $smcFunc['db_query']('substring', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member, m.id_msg, b.id_board, b.name AS board_name, t.num_replies, t.num_views,
IFNULL(mem.real_name, m.poster_name) AS poster_name, ' . ($user_info['is_guest'] ? '1 AS is_read, 0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) >= m.id_msg_modified AS is_read,
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') . ', SUBSTRING(m.body, 1, 384) AS body, m.smileys_enabled, m.icon
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)' . (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:current_member})' : '') . '
WHERE t.id_last_msg >= {int:min_message_id}
' . (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude_boards})') . '
' . (empty($include_boards) ? '' : '
AND b.id_board IN ({array_int:include_boards})') . '
AND {query_wanna_see_board}' . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
ORDER BY t.id_last_msg DESC
LIMIT ' . $num_recent,
array(
'current_member' => $user_info['id'],
'include_boards' => empty($include_boards) ? '' : $include_boards,
'exclude_boards' => empty($exclude_boards) ? '' : $exclude_boards,
'min_message_id' => $modSettings['maxMsgID'] - 35 * min($num_recent, 5),
'is_approved' => 1,
)
);
$posts = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{
$row['body'] = strip_tags(strtr(parse_bbc($row['body'], $row['smileys_enabled'], $row['id_msg']), array('<br />' => '&#10;')));
if ($smcFunc['strlen']($row['body']) > 128)
$row['body'] = $smcFunc['substr']($row['body'], 0, 128) . '...';

// Censor the subject.
censorText($row['subject']);
censorText($row['body']);

if (empty($modSettings['messageIconChecks_disable']) && !isset($icon_sources[$row['icon']]))
$icon_sources[$row['icon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['icon'] . '.gif') ? 'images_url' : 'default_images_url';

// Build the array.
$posts[] = array(
'board' => array(
'id' => $row['id_board'],
'name' => $row['board_name'],
'href' => $scripturl . '?board=' . $row['id_board'] . '.0',
'link' => '<a href="' . $scripturl . '?board=' . $row['id_board'] . '.0">' . $row['board_name'] . '</a>'
),
'topic' => $row['id_topic'],
'poster' => array(
'id' => $row['id_member'],
'name' => $row['poster_name'],
'href' => empty($row['id_member']) ? '' : $scripturl . '?action=profile;u=' . $row['id_member'],
'link' => empty($row['id_member']) ? $row['poster_name'] : '<a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '">' . $row['poster_name'] . '</a>'
),
'subject' => $row['subject'],
'replies' => $row['num_replies'],
'views' => $row['num_views'],
'short_subject' => shorten_subject($row['subject'], 25),
'preview' => $row['body'],
'time' => timeformat($row['poster_time']),
'timestamp' => forum_time(true, $row['poster_time']),
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['id_msg'] . ';topicseen#new',
'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['id_msg'] . '#new" rel="nofollow">' . $row['subject'] . '</a>',
// Retained for compatibility - is technically incorrect!
'new' => !empty($row['is_read']),
'is_new' => empty($row['is_read']),
'new_from' => $row['new_from'],
'icon' => '<img src="' . $settings[$icon_sources[$row['icon']]] . '/post/' . $row['icon'] . '.gif" align="middle" alt="' . $row['icon'] . '" />',
);
}
$smcFunc['db_free_result']($request);

[MoreBloodWine]

The person who wrote the code for reasons I cannot recall said using the built in SMF query wasnt possible for whatever reasons. I might be able to find that reason if I go dig up the post on TP where the code was originally written since the blockcode.php is to be used in a block.

Using that query provides no results via the block, I'm guessing thats only one of the reason of why the original coder who said I would need a SQL guru to really get in to this.

Right now on my site using http://www.eojmarket.com/index.php?action=unread;all;start=0 I see 5 posts.

Using the right query I should see the same 5 posts in the TP block.

In either case, all the code required for the system to work is provided. It currently works on 1.1.x should someone want to test it or whatever.

Have you looked at SSI.php?  That query looks eerily familiar...

Edit:

Code (SSI.php) Select Expand

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
$request = $smcFunc['db_query']('substring', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member, m.id_msg, b.id_board, b.name AS board_name, t.num_replies, t.num_views,
IFNULL(mem.real_name, m.poster_name) AS poster_name, ' . ($user_info['is_guest'] ? '1 AS is_read, 0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) >= m.id_msg_modified AS is_read,
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') . ', SUBSTRING(m.body, 1, 384) AS body, m.smileys_enabled, m.icon
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)' . (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:current_member})' : '') . '
WHERE t.id_last_msg >= {int:min_message_id}
' . (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude_boards})') . '
' . (empty($include_boards) ? '' : '
AND b.id_board IN ({array_int:include_boards})') . '
AND {query_wanna_see_board}' . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
ORDER BY t.id_last_msg DESC
LIMIT ' . $num_recent,
array(
'current_member' => $user_info['id'],
'include_boards' => empty($include_boards) ? '' : $include_boards,
'exclude_boards' => empty($exclude_boards) ? '' : $exclude_boards,
'min_message_id' => $modSettings['maxMsgID'] - 35 * min($num_recent, 5),
'is_approved' => 1,
)
);
$posts = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{
$row['body'] = strip_tags(strtr(parse_bbc($row['body'], $row['smileys_enabled'], $row['id_msg']), array('<br />' => '&#10;')));
if ($smcFunc['strlen']($row['body']) > 128)
$row['body'] = $smcFunc['substr']($row['body'], 0, 128) . '...';

// Censor the subject.
censorText($row['subject']);
censorText($row['body']);

if (empty($modSettings['messageIconChecks_disable']) && !isset($icon_sources[$row['icon']]))
$icon_sources[$row['icon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['icon'] . '.gif') ? 'images_url' : 'default_images_url';

// Build the array.
$posts[] = array(
'board' => array(
'id' => $row['id_board'],
'name' => $row['board_name'],
'href' => $scripturl . '?board=' . $row['id_board'] . '.0',
'link' => '<a href="' . $scripturl . '?board=' . $row['id_board'] . '.0">' . $row['board_name'] . '</a>'
),
'topic' => $row['id_topic'],
'poster' => array(
'id' => $row['id_member'],
'name' => $row['poster_name'],
'href' => empty($row['id_member']) ? '' : $scripturl . '?action=profile;u=' . $row['id_member'],
'link' => empty($row['id_member']) ? $row['poster_name'] : '<a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '">' . $row['poster_name'] . '</a>'
),
'subject' => $row['subject'],
'replies' => $row['num_replies'],
'views' => $row['num_views'],
'short_subject' => shorten_subject($row['subject'], 25),
'preview' => $row['body'],
'time' => timeformat($row['poster_time']),
'timestamp' => forum_time(true, $row['poster_time']),
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['id_msg'] . ';topicseen#new',
'link' => '<a href="' . $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['id_msg'] . '#new" rel="nofollow">' . $row['subject'] . '</a>',
// Retained for compatibility - is technically incorrect!
'new' => !empty($row['is_read']),
'is_new' => empty($row['is_read']),
'new_from' => $row['new_from'],
'icon' => '<img src="' . $settings[$icon_sources[$row['icon']]] . '/post/' . $row['icon'] . '.gif" align="middle" alt="' . $row['icon'] . '" />',
);
}
$smcFunc['db_free_result']($request);

[feline]

Try this query...

Code Select Expand


global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS posterName, '. ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') .'
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)'. (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:memid})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board) AND lmr.id_member = {int:memid}' : '') .'
WHERE t.id_last_msg = m.id_msg
AND b.id_board = t.id_board'. (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude})') .'
AND {query_see_board}
AND ms.id_msg = t.id_first_msg
AND (lmr.id_msg IS NULL OR lmr.id_msg < m.id_msg_modified)
AND (lt.id_msg IS NULL OR lt.id_msg < m.id_msg_modified)
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array(
'memid' => $user_info['id'],
'exclude' => explode(',', $exclude_boards),
'limit' => $look_ahead,
)
);


To read the rows and free the mysql handle use:

Code Select Expand


while($row = $smcFunc['db_fetch_assoc']($request))
{
....
}

$smcFunc['db_free_result']($request);

Fel

[MoreBloodWine]

Edited my post above with some sadditional info & it's not my code so aside from replacing the query I will have to literally play dumb on everything else.

Edit: I would also add that anyone interested in helping needs to use the base query provided to modify / fix as it also has some custom coding to it like $look_ahead etc.

Edit 2: @Feline - I used your code as suggested but arent getting any results (There are no recent unread posts to display.) when in fact there are 5 as of right now. Also, does your modified query account for number 1 in regards to say an old post ?

Like if I go back and mark say a yr old post as new, it won't show up as a new post like it does if I use SMF's built in http://www.eojmarket.com/index.php?action=unread;all;start=0

Code Select Expand

<?php
// This file generates an XML file of unread topics.

header("Content-type: text/xml");

require_once('../../SSI.php');

// Get any passed variables.
if (isset($_GET['numtopics']))
$num_topics = $_GET['numtopics'];
else
$num_topics = 10;

if (isset($_GET['exclude']) && $_GET['exclude'] != 'null')
{
$exclude_boards = explode("-", $_GET['exclude']);
if (!is_array($exclude_boards) || empty($exclude_boards))
$exclude_boards = null;
}
else
{
$exclude_boards = null;
}

global $context, $settings, $scripturl, $db_prefix, $ID_MEMBER;
global $user_info, $modSettings;

// Database query etc taken from SSI.php and adapted...
if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
$exclude_boards = array($modSettings['recycle_board']);
else
$exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;

// Lets just see if there are more messages than asked for...
$look_ahead = $num_topics + 1;

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS posterName, '. ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') .'
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)'. (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:memid})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board) AND lmr.id_member = {int:memid}' : '') .'
WHERE t.id_last_msg = m.id_msg
AND b.id_board = t.id_board'. (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude})') .'
AND {query_see_board}
AND ms.id_msg = t.id_first_msg
AND (lmr.id_msg IS NULL OR lmr.id_msg < m.id_msg_modified)
AND (lt.id_msg IS NULL OR lt.id_msg < m.id_msg_modified)
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array(
'memid' => $user_info['id'],
'exclude' => explode(',', $exclude_boards),
'limit' => $look_ahead,
)
);

$posts = array();

while($row = $smcFunc['db_fetch_assoc']($request))
{
// Censor the subject.
censorText($row['subject']);

// Build the array.
$posts[] = array(
'id_topic' => $row['ID_TOPIC'],
'poster_link' => empty($row['ID_MEMBER']) ? $row['posterName'] : htmlspecialchars('<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>'),
'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['new_from'] . ';topicseen#new',
'short_subject' => shorten_subject($row['subject'], 25),
'time' => htmlspecialchars(timeformat($row['posterTime']))
);
}

$smcFunc['db_free_result']($request);

$post_count = count($posts);

echo '<unreadtopics items="' , $post_count , '">';

$counter = 0;

if($post_count > 0)
{
foreach($posts as $post)
{
if ($counter < $num_topics)
echo '
<topic id="' , $post['id_topic'] , '" href="' , $post['href'] , '" poster="' , $post['poster_link'] ,'" time="' , $post['time'] , '">
' , $post['short_subject'] , '
</topic>';

$counter++;
}
}

echo '
</unreadtopics>';
?>

Edit: Just noticed this in the SMF logs.

numtopics=10&exclude=nullWrong value type sent to the database. Array of integers expected. (exclude)File: /home/removed/public_html/tp-files/block-unreadtopics/unreadTopicsXml.php
Line: 63

Line 63 is the ); that marks the end of the query.

[MoreBloodWine]

Anyone else willin to take a crak at the query, again, the query used has some "custom" stuff in it so it's the one that needs to be modded to work with the other files thats a part of this "system".

Ty !

Edited my post above with some sadditional info & it's not my code so aside from replacing the query I will have to literally play dumb on everything else.

Edit: I would also add that anyone interested in helping needs to use the base query provided to modify / fix as it also has some custom coding to it like $look_ahead etc.

Edit 2: @Feline - I used your code as suggested but arent getting any results (There are no recent unread posts to display.) when in fact there are 5 as of right now. Also, does your modified query account for number 1 in regards to say an old post ?

Like if I go back and mark say a yr old post as new, it won't show up as a new post like it does if I use SMF's built in http://www.eojmarket.com/index.php?action=unread;all;start=0

Code Select Expand

<?php
// This file generates an XML file of unread topics.

header("Content-type: text/xml");

require_once('../../SSI.php');

// Get any passed variables.
if (isset($_GET['numtopics']))
$num_topics = $_GET['numtopics'];
else
$num_topics = 10;

if (isset($_GET['exclude']) && $_GET['exclude'] != 'null')
{
$exclude_boards = explode("-", $_GET['exclude']);
if (!is_array($exclude_boards) || empty($exclude_boards))
$exclude_boards = null;
}
else
{
$exclude_boards = null;
}

global $context, $settings, $scripturl, $db_prefix, $ID_MEMBER;
global $user_info, $modSettings;

// Database query etc taken from SSI.php and adapted...
if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
$exclude_boards = array($modSettings['recycle_board']);
else
$exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;

// Lets just see if there are more messages than asked for...
$look_ahead = $num_topics + 1;

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS posterName, '. ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') .'
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)'. (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:memid})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board) AND lmr.id_member = {int:memid}' : '') .'
WHERE t.id_last_msg = m.id_msg
AND b.id_board = t.id_board'. (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude})') .'
AND {query_see_board}
AND ms.id_msg = t.id_first_msg
AND (lmr.id_msg IS NULL OR lmr.id_msg < m.id_msg_modified)
AND (lt.id_msg IS NULL OR lt.id_msg < m.id_msg_modified)
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array(
'memid' => $user_info['id'],
'exclude' => explode(',', $exclude_boards),
'limit' => $look_ahead,
)
);

$posts = array();

while($row = $smcFunc['db_fetch_assoc']($request))
{
// Censor the subject.
censorText($row['subject']);

// Build the array.
$posts[] = array(
'id_topic' => $row['ID_TOPIC'],
'poster_link' => empty($row['ID_MEMBER']) ? $row['posterName'] : htmlspecialchars('<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>'),
'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['new_from'] . ';topicseen#new',
'short_subject' => shorten_subject($row['subject'], 25),
'time' => htmlspecialchars(timeformat($row['posterTime']))
);
}

$smcFunc['db_free_result']($request);

$post_count = count($posts);

echo '<unreadtopics items="' , $post_count , '">';

$counter = 0;

if($post_count > 0)
{
foreach($posts as $post)
{
if ($counter < $num_topics)
echo '
<topic id="' , $post['id_topic'] , '" href="' , $post['href'] , '" poster="' , $post['poster_link'] ,'" time="' , $post['time'] , '">
' , $post['short_subject'] , '
</topic>';

$counter++;
}
}

echo '
</unreadtopics>';
?>

Edit: Just noticed this in the SMF logs.

numtopics=10&exclude=nullWrong value type sent to the database. Array of integers expected. (exclude)File: /home/removed/public_html/tp-files/block-unreadtopics/unreadTopicsXml.php
Line: 63

Line 63 is the ); that marks the end of the query.

[MoreBloodWine]

So where are all you SMF SQL guru's ?

[IchBin™]

Change this line:
'exclude' => explode(',', $exclude_boards),

To this:
'exclude' => $exclude_boards,

See if that works.

[MoreBloodWine]

Change this line:
'exclude' => explode(',', $exclude_boards),

To this:
'exclude' => $exclude_boards,

See if that works.

That allows topics to populate the list where as before all I got was the default nothing to show message but when a topic link is clicked this is the link I get except its of the forum index and the post stays marked as new which I guess is because I didn't view it.

http://www.eojmarket.com/index.php?topic=.msg11945;topicseen#new

Edit: Also noticed some other weird stuff with times, all [December 31, 1969, 7:00:00 pm]

[IchBin™]

Try this:

Code Select Expand

<?php
// This file generates an XML file of unread topics.

header("Content-type: text/xml");

require_once('../../SSI.php');

// Get any passed variables.
if (isset($_GET['numtopics']))
$num_topics = $_GET['numtopics'];
else
$num_topics = 10;

if (isset($_GET['exclude']) && $_GET['exclude'] != 'null')
{
$exclude_boards = explode("-", $_GET['exclude']);
if (!is_array($exclude_boards) || empty($exclude_boards))
$exclude_boards = null;
}
else
{
$exclude_boards = null;
}

global $context, $settings, $scripturl, $db_prefix, $ID_MEMBER;
global $user_info, $modSettings;

// Database query etc taken from SSI.php and adapted...
if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
$exclude_boards = array($modSettings['recycle_board']);
else
$exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;

// Lets just see if there are more messages than asked for...
$look_ahead = $num_topics + 1;

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS posterName, '. ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') .'
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)'. (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:memid})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board) AND lmr.id_member = {int:memid}' : '') .'
WHERE t.id_last_msg = m.id_msg
AND b.id_board = t.id_board'. (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude})') .'
AND {query_see_board}
AND ms.id_msg = t.id_first_msg
AND (lmr.id_msg IS NULL OR lmr.id_msg < m.id_msg_modified)
AND (lt.id_msg IS NULL OR lt.id_msg < m.id_msg_modified)
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array(
'memid' => $user_info['id'],
'exclude' => $exclude_boards,
'limit' => $look_ahead,
)
);

$posts = array();

while($row = $smcFunc['db_fetch_assoc']($request))
{
// Censor the subject.
censorText($row['subject']);

// Build the array.
$posts[] = array(
'id_topic' => $row['id_topic'],
'poster_link' => empty($row['id_member']) ? $row['posterName'] : htmlspecialchars('<a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '">' . $row['posterName'] . '</a>'),
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['new_from'] . ';topicseen#new',
'short_subject' => shorten_subject($row['subject'], 25),
'time' => htmlspecialchars(timeformat($row['poster_time']))
);
}

$smcFunc['db_free_result']($request);

$post_count = count($posts);

echo '<unreadtopics items="' , $post_count , '">';

$counter = 0;

if($post_count > 0)
{
foreach($posts as $post)
{
if ($counter < $num_topics)
echo '
<topic id="' , $post['id_topic'] , '" href="' , $post['href'] , '" poster="' , $post['poster_link'] ,'" time="' , $post['time'] , '">
' , $post['short_subject'] , '
</topic>';

$counter++;
}
}

echo '
</unreadtopics>';
?>

[MoreBloodWine]

Looks like were golden with your modded code Ich, but there's three other things I wanted to touch base on though that I sort of mentioned in the original post. I'll start with this one...

All of this code was / is supposed to mimic more or less what the below link would show.

mysite.com/index.php?action=unread;all;start=0

However, the original coder wasnt able to work it out and suggested I find a "guru" of sorts in regards to SMF and SQL. Well, right now I have 7 unread posts but only 6 show on the mod, one of them is an old post. Not sure how far back the "block" goes but I tested with one of my sites oldest.

In any case, I marked it as new but it doesnt show in the block but does if I use SMF's built in stuff (linked above). Any idea how to make it show any post marked as unread regardless of age ?

[IchBin™]

No time to figure that out for you sorry. But removing the time limit allowing the query to grab any unread post regardless of age sounds like it could be a server killer.

[MoreBloodWine]

No time to figure that out for you sorry. But removing the time limit allowing the query to grab any unread post regardless of age sounds like it could be a server killer.

But here's the thing though, how does SMF do it ?

When the code was written it was supposed to more or less mimic what mysite.com/index.php?action=unread;all;start=0 does just for a TP block and it does just that, just not with an old post.

The guy who wrote it just didnt really understand SQL all to well so he couldnt get it to do more than what it already does.

Edit: I'm assuming the issue is in the query.

[MoreBloodWine]

No time to figure that out for you sorry. But removing the time limit allowing the query to grab any unread post regardless of age sounds like it could be a server killer.

But here's the thing though, how does SMF do it ?

When the code was written it was supposed to more or less mimic what mysite.com/index.php?action=unread;all;start=0 does just for a TP block and it does just that, just not with an old post.

The guy who wrote it just didnt really understand SQL all to well so he couldnt get it to do more than what it already does.

Edit: I'm assuming the issue is in the query.

Anyone wan't to take a stab at all of this ?

Here's where we stand... even with the items not mentioned but part of the "something else" mentioned in the title / main post.

1. Old posts dont show, but there has to be a clean fix for this since SMF's built in mysite.com/index.php?action=unread;all;start=0 shows all unread posts regardless of age which is what this "system" is supposed to mimmic.

2. I suppose this would be the main big thing, if a user signs up on my site and immediately goes to mysite.com/index.php?action=unread;all;start=0 they don't see anything as expected since this is their first visit and if timed right with the registration there hasnt been any new posts made. However, in the block all of this code is meant for... let's just say it's nice and full when it shouldn't be.

So ya, those are the only two things that need to be worked out to truly make this system 100% like mysite.com/index.php?action=unread;all;start=0 with the exception that it's all built in to a TP block.

For arguments sake, here's all of the code from this entire system as it stans right now in wat I would call  60% functional state, 20% reserved for both items one and two.

public_html/tp-files/blockcode.php (The file called inside of a TP block via a require)

Code Select Expand

<?php
if($context['user']['is_logged']){
// Block Code to display unread topics from XML file.
// Needs unreadTopicsXml.php to function.

// Uses the prototype javascript library.  If you are already using this library simply
// disable the reference to the google api below.
$prototype = 0; // 1 = Enabled, 0 = Disabled

// Configuration

// Set the number of unread topics to show at any one time.
$num_topics = 10;

// Exclude some boards ? Then fill the array eg : array(1,13,20), if not then leave empty like this : array().
$exclude_boards= array();

// End of Config.


// The code....

if (!empty($exclude_boards))
$exclude_boards_string = implode("-" , $exclude_boards);
else
$exclude_boards_string = 'null';

global $boardurl, $settings, $context, $scripturl;

if($prototype == '1'){
echo '
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/prototype/1.6.1.0/prototype.js"></script>
';
}

echo '
<div id="notifier" class="smalltext"></div>
<div id="unreadtopicscontainer" style="width: 100%; overflow: auto; max-height: 525px;">
</div>
<div id="markallread" class="smalltext" style="visibility: hidden">
<center>
<hr width="75%" />
<span style="cursor: pointer" onClick="markAllTopicsRead();"><< Mark all posts read. >></span>
</center>
</div>';

// Pass the variables to the javascript and load the custom js.
echo '
<script type="text/javascript">
var xmlrequesturl = "' , $boardurl , '/tp-files/block-unreadtopics/unreadTopicsXml.php?numtopics=' , $num_topics , '&exclude=' , $exclude_boards_string , '";
var newimage = "' , $settings['images_url'] , '/' . $context['user']['language'] . '/new.gif";
var dismissbutton = "' , $boardurl , '/tp-files/block-unreadtopics/removeButton.jpg";
var scripturl = "' , $scripturl , '";
var sessionid = "' , $context['session_id'] , '";
var moretopics = "0";
</script>
<script type="text/javascript" src="' , $boardurl , '/tp-files/block-unreadtopics/showUnread.js"></script>
';
}else{
echo '<center>Please <a href="'.$scripturl.'?action=login">login</a> or <a href="'.$scripturl.'?action=register">register</a> to use this block.</center>';
}
?>

public_html/tp-files/block-unreadtopics/showUnread.js

Code Select Expand

var numberOfUnreadTopics;

function getUnreadTopics()
{
new Ajax.Request( xmlrequesturl, {
onSuccess: function( transport )
{
var unreadTopics = transport.responseXML.getElementsByTagName( "topic" );

numberOfUnreadTopics = unreadTopics.length

if (numberOfUnreadTopics == 0)
{
notifyNoTopics();
}
else
{
var lookahead = transport.responseXML.getElementsByTagName( "unreadtopics" );

moretopics = lookahead[0].getAttribute("items").strip();

notifySomeTopics(numberOfUnreadTopics, moretopics);

for ( var i = 0; i < unreadTopics.length; i++ )
{
var subject = unreadTopics[i].firstChild.nodeValue.strip();
var id = unreadTopics[i].getAttribute("id").strip();
var href = unreadTopics[i].getAttribute("href").strip();
var poster = unreadTopics[i].getAttribute("poster").strip();
var time = unreadTopics[i].getAttribute("time").strip();

var divTag = document.createElement("div");
divTag.className = "smalltext";
divTag.setAttribute("id", "topic" + id);
divTag.setAttribute("style", "overflow:hidden");
divTag.innerHTML = '<img border="0" src="' + dismissbutton + '" alt="Dismiss" title="Dismiss" onClick="markTopicRead(\'' + id + '\');" />';
divTag.innerHTML += ' <a href="' + href + '">' + subject + '</a>';
divTag.innerHTML += '<br />By: <strong>' + poster + '</strong>';
divTag.innerHTML += '<br /><a href="' + href + '"><img border="0" src="' + newimage + '" alt="new" /></a>';
divTag.innerHTML += ' [' + time + ']';

if (i < unreadTopics.length - 1)
divTag.innerHTML = divTag.innerHTML + "<hr />";

document.getElementById("unreadtopicscontainer").appendChild(divTag);
}
}
}
});
}

getUnreadTopics();

function notifyNoTopics()
{
document.getElementById("notifier").innerHTML = '<center>There are no recent<br />unread posts to display.</center>';
document.getElementById("markallread").style.visibility = "hidden";
}

function notifySomeTopics(count, moreitems)
{
if (moreitems > count)
{
document.getElementById("notifier").innerHTML = '<center>Showing ' + count + '<br />of<br />' + moreitems + '+ Unread Posts.<br /><hr width="75%" /></center>';
}
else
{
document.getElementById("notifier").innerHTML = '<center>Number of Unread Posts: ' + count + '<br /><hr width="75%" /></center>';
}
document.getElementById("markallread").style.visibility = "visible";
}

function markTopicRead(idTopic)
{
new Ajax.Request( scripturl + "?topic=" + idTopic , {
onSuccess: function( transport )
{
myCollapse("topic" + idTopic);
numberOfUnreadTopics -= 1;
if(numberOfUnreadTopics < 1)
getUnreadTopics();
else
{
moretopics -= 1;
notifySomeTopics(numberOfUnreadTopics, moretopics);
}
}
});
}

function markAllTopicsRead()
{
new Ajax.Request( scripturl + "?action=markasread;sa=all;sesc=" + sessionid , {
onSuccess: function( transport )
{
document.getElementById("unreadtopicscontainer").innerHTML = '';
notifyNoTopics();
}
});
}

function myCollapse(targetDiv)
{
var temp = document.getElementById(targetDiv).offsetHeight;
var height = parseInt(temp);
var max = height;
var min = 1;

if (height > min)
{
height -= 1;
document.getElementById(targetDiv).style.height = height + "px";
setTimeout("myCollapse(\'" + targetDiv + "\')", 1);
}
else
{
document.getElementById(targetDiv).style.visibility = "hidden";
}
}

public_html/tp-files/block-unreadtopics/unreadTopicsXml.php

Code Select Expand

<?php
// This file generates an XML file of unread topics.

header("Content-type: text/xml");

require_once('../../SSI.php');

// Get any passed variables.
if (isset($_GET['numtopics']))
$num_topics = $_GET['numtopics'];
else
$num_topics = 10;

if (isset($_GET['exclude']) && $_GET['exclude'] != 'null')
{
$exclude_boards = explode("-", $_GET['exclude']);
if (!is_array($exclude_boards) || empty($exclude_boards))
$exclude_boards = null;
}
else
{
$exclude_boards = null;
}

global $context, $settings, $scripturl, $db_prefix, $ID_MEMBER;
global $user_info, $modSettings;

// Database query etc taken from SSI.php and adapted...
if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
$exclude_boards = array($modSettings['recycle_board']);
else
$exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;

// Lets just see if there are more messages than asked for...
$look_ahead = $num_topics + 1;

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS posterName, '. ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') .'
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)'. (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:memid})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board) AND lmr.id_member = {int:memid}' : '') .'
WHERE t.id_last_msg = m.id_msg
AND b.id_board = t.id_board'. (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude})') .'
AND {query_see_board}
AND ms.id_msg = t.id_first_msg
AND (lmr.id_msg IS NULL OR lmr.id_msg < m.id_msg_modified)
AND (lt.id_msg IS NULL OR lt.id_msg < m.id_msg_modified)
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array(
'memid' => $user_info['id'],
'exclude' => $exclude_boards,
'limit' => $look_ahead,
)
);

$posts = array();

while($row = $smcFunc['db_fetch_assoc']($request))
{
// Censor the subject.
censorText($row['subject']);

// Build the array.
$posts[] = array(
'id_topic' => $row['id_topic'],
'poster_link' => empty($row['id_member']) ? $row['posterName'] : htmlspecialchars('<a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '">' . $row['posterName'] . '</a>'),
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['new_from'] . ';topicseen#new',
'short_subject' => shorten_subject($row['subject'], 25),
'time' => htmlspecialchars(timeformat($row['poster_time']))
);
}

$smcFunc['db_free_result']($request);

$post_count = count($posts);

echo '<unreadtopics items="' , $post_count , '">';

$counter = 0;

if($post_count > 0)
{
foreach($posts as $post)
{
if ($counter < $num_topics)
echo '
<topic id="' , $post['id_topic'] , '" href="' , $post['href'] , '" poster="' , $post['poster_link'] ,'" time="' , $post['time'] , '">
' , $post['short_subject'] , '
</topic>';

$counter++;
}
}

echo '
</unreadtopics>';
?>

[MoreBloodWine]

Anyone still willing to try and help finally resolve this ?

No time to figure that out for you sorry. But removing the time limit allowing the query to grab any unread post regardless of age sounds like it could be a server killer.

But here's the thing though, how does SMF do it ?

When the code was written it was supposed to more or less mimic what mysite.com/index.php?action=unread;all;start=0 does just for a TP block and it does just that, just not with an old post.

The guy who wrote it just didnt really understand SQL all to well so he couldnt get it to do more than what it already does.

Edit: I'm assuming the issue is in the query.

Anyone wan't to take a stab at all of this ?

Here's where we stand... even with the items not mentioned but part of the "something else" mentioned in the title / main post.

1. Old posts dont show, but there has to be a clean fix for this since SMF's built in mysite.com/index.php?action=unread;all;start=0 shows all unread posts regardless of age which is what this "system" is supposed to mimmic.

2. I suppose this would be the main big thing, if a user signs up on my site and immediately goes to mysite.com/index.php?action=unread;all;start=0 they don't see anything as expected since this is their first visit and if timed right with the registration there hasnt been any new posts made. However, in the block all of this code is meant for... let's just say it's nice and full when it shouldn't be.

So ya, those are the only two things that need to be worked out to truly make this system 100% like mysite.com/index.php?action=unread;all;start=0 with the exception that it's all built in to a TP block.

For arguments sake, here's all of the code from this entire system as it stans right now in wat I would call  60% functional state, 20% reserved for both items one and two.

public_html/tp-files/blockcode.php (The file called inside of a TP block via a require)

Code Select Expand

<?php
if($context['user']['is_logged']){
// Block Code to display unread topics from XML file.
// Needs unreadTopicsXml.php to function.

// Uses the prototype javascript library.  If you are already using this library simply
// disable the reference to the google api below.
$prototype = 0; // 1 = Enabled, 0 = Disabled

// Configuration

// Set the number of unread topics to show at any one time.
$num_topics = 10;

// Exclude some boards ? Then fill the array eg : array(1,13,20), if not then leave empty like this : array().
$exclude_boards= array();

// End of Config.


// The code....

if (!empty($exclude_boards))
$exclude_boards_string = implode("-" , $exclude_boards);
else
$exclude_boards_string = 'null';

global $boardurl, $settings, $context, $scripturl;

if($prototype == '1'){
echo '
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/prototype/1.6.1.0/prototype.js"></script>
';
}

echo '
<div id="notifier" class="smalltext"></div>
<div id="unreadtopicscontainer" style="width: 100%; overflow: auto; max-height: 525px;">
</div>
<div id="markallread" class="smalltext" style="visibility: hidden">
<center>
<hr width="75%" />
<span style="cursor: pointer" onClick="markAllTopicsRead();"><< Mark all posts read. >></span>
</center>
</div>';

// Pass the variables to the javascript and load the custom js.
echo '
<script type="text/javascript">
var xmlrequesturl = "' , $boardurl , '/tp-files/block-unreadtopics/unreadTopicsXml.php?numtopics=' , $num_topics , '&exclude=' , $exclude_boards_string , '";
var newimage = "' , $settings['images_url'] , '/' . $context['user']['language'] . '/new.gif";
var dismissbutton = "' , $boardurl , '/tp-files/block-unreadtopics/removeButton.jpg";
var scripturl = "' , $scripturl , '";
var sessionid = "' , $context['session_id'] , '";
var moretopics = "0";
</script>
<script type="text/javascript" src="' , $boardurl , '/tp-files/block-unreadtopics/showUnread.js"></script>
';
}else{
echo '<center>Please <a href="'.$scripturl.'?action=login">login</a> or <a href="'.$scripturl.'?action=register">register</a> to use this block.</center>';
}
?>

public_html/tp-files/block-unreadtopics/showUnread.js

Code Select Expand

var numberOfUnreadTopics;

function getUnreadTopics()
{
new Ajax.Request( xmlrequesturl, {
onSuccess: function( transport )
{
var unreadTopics = transport.responseXML.getElementsByTagName( "topic" );

numberOfUnreadTopics = unreadTopics.length

if (numberOfUnreadTopics == 0)
{
notifyNoTopics();
}
else
{
var lookahead = transport.responseXML.getElementsByTagName( "unreadtopics" );

moretopics = lookahead[0].getAttribute("items").strip();

notifySomeTopics(numberOfUnreadTopics, moretopics);

for ( var i = 0; i < unreadTopics.length; i++ )
{
var subject = unreadTopics[i].firstChild.nodeValue.strip();
var id = unreadTopics[i].getAttribute("id").strip();
var href = unreadTopics[i].getAttribute("href").strip();
var poster = unreadTopics[i].getAttribute("poster").strip();
var time = unreadTopics[i].getAttribute("time").strip();

var divTag = document.createElement("div");
divTag.className = "smalltext";
divTag.setAttribute("id", "topic" + id);
divTag.setAttribute("style", "overflow:hidden");
divTag.innerHTML = '<img border="0" src="' + dismissbutton + '" alt="Dismiss" title="Dismiss" onClick="markTopicRead(\'' + id + '\');" />';
divTag.innerHTML += ' <a href="' + href + '">' + subject + '</a>';
divTag.innerHTML += '<br />By: <strong>' + poster + '</strong>';
divTag.innerHTML += '<br /><a href="' + href + '"><img border="0" src="' + newimage + '" alt="new" /></a>';
divTag.innerHTML += ' [' + time + ']';

if (i < unreadTopics.length - 1)
divTag.innerHTML = divTag.innerHTML + "<hr />";

document.getElementById("unreadtopicscontainer").appendChild(divTag);
}
}
}
});
}

getUnreadTopics();

function notifyNoTopics()
{
document.getElementById("notifier").innerHTML = '<center>There are no recent<br />unread posts to display.</center>';
document.getElementById("markallread").style.visibility = "hidden";
}

function notifySomeTopics(count, moreitems)
{
if (moreitems > count)
{
document.getElementById("notifier").innerHTML = '<center>Showing ' + count + '<br />of<br />' + moreitems + '+ Unread Posts.<br /><hr width="75%" /></center>';
}
else
{
document.getElementById("notifier").innerHTML = '<center>Number of Unread Posts: ' + count + '<br /><hr width="75%" /></center>';
}
document.getElementById("markallread").style.visibility = "visible";
}

function markTopicRead(idTopic)
{
new Ajax.Request( scripturl + "?topic=" + idTopic , {
onSuccess: function( transport )
{
myCollapse("topic" + idTopic);
numberOfUnreadTopics -= 1;
if(numberOfUnreadTopics < 1)
getUnreadTopics();
else
{
moretopics -= 1;
notifySomeTopics(numberOfUnreadTopics, moretopics);
}
}
});
}

function markAllTopicsRead()
{
new Ajax.Request( scripturl + "?action=markasread;sa=all;sesc=" + sessionid , {
onSuccess: function( transport )
{
document.getElementById("unreadtopicscontainer").innerHTML = '';
notifyNoTopics();
}
});
}

function myCollapse(targetDiv)
{
var temp = document.getElementById(targetDiv).offsetHeight;
var height = parseInt(temp);
var max = height;
var min = 1;

if (height > min)
{
height -= 1;
document.getElementById(targetDiv).style.height = height + "px";
setTimeout("myCollapse(\'" + targetDiv + "\')", 1);
}
else
{
document.getElementById(targetDiv).style.visibility = "hidden";
}
}

public_html/tp-files/block-unreadtopics/unreadTopicsXml.php

Code Select Expand

<?php
// This file generates an XML file of unread topics.

header("Content-type: text/xml");

require_once('../../SSI.php');

// Get any passed variables.
if (isset($_GET['numtopics']))
$num_topics = $_GET['numtopics'];
else
$num_topics = 10;

if (isset($_GET['exclude']) && $_GET['exclude'] != 'null')
{
$exclude_boards = explode("-", $_GET['exclude']);
if (!is_array($exclude_boards) || empty($exclude_boards))
$exclude_boards = null;
}
else
{
$exclude_boards = null;
}

global $context, $settings, $scripturl, $db_prefix, $ID_MEMBER;
global $user_info, $modSettings;

// Database query etc taken from SSI.php and adapted...
if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
$exclude_boards = array($modSettings['recycle_board']);
else
$exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;

// Lets just see if there are more messages than asked for...
$look_ahead = $num_topics + 1;

// Find all the posts in distinct topics.  Newer ones will have higher IDs.
global $smcFunc, $user_info;

$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS posterName, '. ($user_info['is_guest'] ? '0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') .'
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)'. (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:memid})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board) AND lmr.id_member = {int:memid}' : '') .'
WHERE t.id_last_msg = m.id_msg
AND b.id_board = t.id_board'. (empty($exclude_boards) ? '' : '
AND b.id_board NOT IN ({array_int:exclude})') .'
AND {query_see_board}
AND ms.id_msg = t.id_first_msg
AND (lmr.id_msg IS NULL OR lmr.id_msg < m.id_msg_modified)
AND (lt.id_msg IS NULL OR lt.id_msg < m.id_msg_modified)
ORDER BY t.id_last_msg DESC
LIMIT {int:limit}',
array(
'memid' => $user_info['id'],
'exclude' => $exclude_boards,
'limit' => $look_ahead,
)
);

$posts = array();

while($row = $smcFunc['db_fetch_assoc']($request))
{
// Censor the subject.
censorText($row['subject']);

// Build the array.
$posts[] = array(
'id_topic' => $row['id_topic'],
'poster_link' => empty($row['id_member']) ? $row['posterName'] : htmlspecialchars('<a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '">' . $row['posterName'] . '</a>'),
'href' => $scripturl . '?topic=' . $row['id_topic'] . '.msg' . $row['new_from'] . ';topicseen#new',
'short_subject' => shorten_subject($row['subject'], 25),
'time' => htmlspecialchars(timeformat($row['poster_time']))
);
}

$smcFunc['db_free_result']($request);

$post_count = count($posts);

echo '<unreadtopics items="' , $post_count , '">';

$counter = 0;

if($post_count > 0)
{
foreach($posts as $post)
{
if ($counter < $num_topics)
echo '
<topic id="' , $post['id_topic'] , '" href="' , $post['href'] , '" poster="' , $post['poster_link'] ,'" time="' , $post['time'] , '">
' , $post['short_subject'] , '
</topic>';

$counter++;
}
}

echo '
</unreadtopics>';
?>