[RESOLVED] Simpler UnreadReplies query

[B Patterson]

HI all.

I searched here but didn't turn anything up that would help me.  So if it's been asked before, please kindly point me in the right direction.

What I would like to do is find out what query I would need to run in order to retrieve the most recent unread replies for a user.  It's just like the "See new replies to your posts" link, but I want to customize it a bit more.

So what I thought of doing was looking at Recent.php and the function UnreadReplies() to see how you guys do it   Unfortunately you use a lot of different queries and don't really document what you're doing.

I'm fairly well versed in SQL and PHP, so even an explanation of what each SQL table does, the relationships and such would help here too.  I deduced that I'd need to use the smf_messages table (duh) to get the message part.  But I can't seem to find where you store the subscriptions to forums/topics.  That's where I'm lost.  If I knew that, I'd be golden.

Anyone know of where it's stored?

Thanks.

~Brett

[B Patterson]

Code Select Expand

SELECT
ms.ID_TOPIC, ms.ID_BOARD, ms.ID_MSG, ms.subject, ms.posterTime,
mems.memberName, mems.realName
FROM (smf_messages AS ms, smf_messages AS ml, smf_topics AS t, smf_boards AS b)
LEFT JOIN smf_members AS mems ON (mems.ID_MEMBER = ms.ID_MEMBER)
LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN smf_log_topics AS lt ON (lt.ID_TOPIC = t.ID_TOPIC AND lt.ID_MEMBER = 2)
LEFT JOIN smf_log_mark_read AS lmr ON (lmr.ID_BOARD = t.ID_BOARD AND lmr.ID_MEMBER = 2)
WHERE t.ID_TOPIC = ms.ID_TOPIC
AND b.ID_BOARD = t.ID_BOARD
AND ms.ID_MSG = t.ID_FIRST_MSG
AND ml.ID_MSG = t.ID_LAST_MSG
AND t.ID_LAST_MSG >= (SELECT MAX(lmr.ID_MSG)
FROM smf_boards AS b
LEFT JOIN smf_log_mark_read AS lmr
ON (lmr.ID_BOARD = b.ID_BOARD
AND lmr.ID_MEMBER = 2)
)
AND IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, 0)) < ml.ID_MSG
ORDER BY ml.posterTime DESC
LIMIT 0,25
That gets me close, but I'm not sure it's what I want.  That's just unread topics, still not sure how to get the subscriptions....

[tomis]

Anyone know of where it's stored?

From a superficial glance at the DB structure, it looks like you need to check smf_log_topics.  Someone correct me if I'm wrong

[B Patterson]

i got that much, but that's not the only thing....

Well, let me go further in saying this:
I kept working, and this seems to get something, but not what I'm looking for.  It's basically what I think I need as extrapolated from the Recent.php file.

Code Select Expand

SELECT msg.subject, msg.ID_TOPIC, FROM_UNIXTIME(msg.posterTime, '%a %b %e, %Y - %H:%i') AS fdate, mem.realName
FROM smf_messages AS msg
INNER JOIN smf_members AS mem
ON msg.ID_MEMBER = mem.ID_MEMBER
WHERE msg.ID_MSG > mem.ID_MSG_LAST_VISIT
AND msg.ID_TOPIC IN (SELECT ln.ID_TOPIC FROM smf_log_notify AS ln WHERE ln.ID_MEMBER = 2)
AND msg.ID_MSG IN (SELECT lmr.ID_MSG FROM smf_log_mark_read AS lmr WHERE lmr.ID_MEMBER = 2)
GROUP BY msg.ID_TOPIC
ORDER BY msg.posterTime DESC
LIMIT 0, 10;

A dev/support member response would be nice   Thanks for tryin though.

[tomis]

It would help if you mentioned what you want to customize regarding the See new replies to your posts link.   

[B Patterson]

Here's what I want:

Add a function to my custom SSI.php file that will allow me to query the SMF database tables and return the current unread replies of that users posts.

Much like how SMF has the "unread replies to your posts" link in the header, this needs to be slimmed down, and made easier.  I don't need the full-blown error checking as that's already done by me (to see if they're a guest or not).  I just need the array of information returned so that I can manipulate it as I need in my portal page.

Is that enough, or do you need more?

[B Patterson]

Okay... new post since it's similar but different.

I think I got the query right, but I can't be sure.  Maybe I've just stumbled across something that works for me for now.  But still, if the query doesn't work, I'd still like to get a function into my own SSI.php file.  I've aptly named the function, and tried including it in my portal page.  Unfortunately it comes back as a Hacking attempt.  Can you see why?  I can't tell.  I'm not doing anything wrong, and I pretty much just copied from other functions:

Code Select Expand

<?php
function rc_unreadReplies($num_replies = 8, $output_method = 'echo')
{
global $context, $settings, $scripturl;

$request = db_query("
SELECT m.ID_TOPIC, m.subject, m.posterName, m.posterTime, m.body, m.smileysEnabled, m.ID_MSG
FROM {$db_prefix}log_notify AS ln
LEFT JOIN {$db_prefix}messages AS m
ON ln.ID_TOPIC = m.ID_TOPIC
WHERE ln.ID_MEMBER = " . $context['user']['id'] . "
AND m.ID_MSG > (
SELECT MAX(lmr.ID_MSG)
FROM {$db_prefix}log_mark_read AS lmr
WHERE lmr.ID_MEMBER = " . $context['user']['id'] . "
LIMIT 0, 1
)
AND ln.sent = 1
GROUP BY m.ID_TOPIC
ORDER BY m.posterTime DESC
LIMIT 0, $num_replies", __FILE__, __LINE__);

$return = array();
while($row = mysql_fetch_assoc($request))
{
$row['body'] = parse_bbc($row['body'], $row['smileysEnabled'], $row['ID_MSG']);

if (strlen($row['body']) > 512)
$row['body'] = substr($row['body'], 0, 512) . '...';

censorText($row['subject']);
censorText($row['body']);

$return[] = array(
'id' => $row['ID_TOPIC'],
'subject' => $row['subject'],
'shortsubject' => shorten_subject($row['subject'], 25),
'poster' => $row['posterName'],
'timestamp' => forum_time(true, $row['posterTime']),
'time' => timeformat($row['posterTime']),
'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0;topicseen',
'link' => '<a href="' . $scripturl.'?topic=' . $row['ID_TOPIC'] . '.0;topicseen">' . shorten_subject($row['subject'], 25) . '</a>',
'preview' => substr($row['body'], 0, 80) . '...',
'body' => $row['body']
);
}

if($output_method != 'echo' || empty($return))
return $return;

echo '
<table>
<tr>
<th>Subject</th><th>Poster</th><th>Posted</th>
</tr>';
foreach($return as $topic)
{
echo '
<tr>
<table>
<tr>
<td style="border-top, border-left: 1px solid #000;">' . $topic['link'] . '</td>
<td style="border-top: 1px solid #000;">' . $topic['poster'] . '</td>
<td style="border-top, border-right: 1px solid #000;">' . $topic['time'] . '</td>
</tr>
<tr>
<td colspan="3" style="border: 1px solid #000; border-top: 1px solid #ccc; background-color: #ccc">' . $topic['preview'] . '</td>
</tr>
</table>
</tr>';
}
echo '
</table>';
}
?>

[B Patterson]

Okay... consider this one resolved.... apparently SMF doesn't like nested queries

So the solution was to remove it.  And now it seems to work!!

[tomis]

Sorry I wan't of any help, glad to hear you got it sorted.