Trying to change thread query to accept more dates

[edschaum]

I'm trying to change the query listed below. Right now, the query returns threads based on a single date. I want the query to accept a separate date for each forum, which I've already stored in another table.

I think the line that has to be changed is "AND thread.lastpost >=$datecut"

Instead of using the date in $datecut, I want to pull a separate date from another table called lastreads which contains the following fields:
lastreads_id, lr_member, lr_forum and lr_lastviewed. (key to fields: lastreads_id is an autoincrement index, lr_member contains a userid, lr_forum contains a forumid, and lastviewed contains the "timenow" that lr_member last read lr_forum.

I want to pull the value in lr_lastviewed where forumid = lr_forum and $bbsuserinfo('userid') = lr_member and plug that value into the query so that it uses the separate date that has been stored for each forum.

Original Query that I want to change:
$threads = $DB_site->query("
SELECT threadid
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "deletionlog AS delthread ON(delthread.primaryid = thread.threadid AND delthread.type = 'thread')
WHERE forumid IN(" . implode(', ', $forumids) . ")
AND thread.lastpost >= $datecut
AND visible = 1
AND delthread.primaryid IS NULL
AND sticky IN (0,1)
ORDER BY lastpost DESC
LIMIT $vboptions[maxresults]
");


Can someone help me revise this query so that it does what I want?

thanks,

Ed

[Xenon]

hmm i think this should work, but you have t make sure the entries for lastreads do exist!

$threads = $DB_site->query("
SELECT threadid
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "deletionlog AS delthread ON(delthread.primaryid = thread.threadid AND delthread.type = 'thread')
LEFT JOIN " . TABLE_PREFIX . "lastreads AS lastreads ON(lastreads.lr_forum = thread.forumid AND lr_member = $bbuserinfo[userid])
WHERE forumid IN(" . implode(', ', $forumids) . ")
AND thread.lastpost >= lastreads.lr_lastviewed
AND visible = 1
AND delthread.primaryid IS NULL
AND NOT lastreads.lastreads_id IS NULL
AND sticky IN (0,1)
ORDER BY lastpost DESC
LIMIT $vboptions[maxresults]
");

[edschaum]

Thanks for the very fast response. That query loooks like it makes the exact request that I want, but it's not returning any threads.

I'm sure it's something I haven't quite figured out yet on my end, so I'll study it for a while to see if I can figure out what I'm doing wrong.

Thanks again, I'll let you know if I get this working, it's the final piece I need to change the entire Mark Read system over to being persistant.

Ed

[Xenon]

as said, are you sure there are already entries in the lastreads table?

because, it won't get any thread, if there is no entry for the actual browsing user

[edschaum]

Yes, the data already exists in lastreads. In addition to a visual check of the table, I also ran the following test query:

//go through list of forums already loaded into $forumarray previously
While ($edforum = $DB_site->fetch_array($forumarray))
{
$qsforumid = $edforum['forumid'];

$edlastread = $DB_site->query_first("SELECT lr_lastviewed FROM " . TABLE_PREFIX . "lastreads
WHERE lr_member = $bbuserinfo[userid] and lr_forum=$qsforumid");

$viewed = $edlastread['lr_lastviewed'];

echo 'forumid = ' . $qsforumid;
echo ' date = ' . $viewed . '<br>';

}



returns a result like this:

forumid = 1 date = 1090252769
forumid = 2 date = 1090252769
forumid = 3 date = 1090252769
forumid = 4 date = 1090252769
forumid = 5 date = 1090252769
forumid = 6 date = 1090253340
forumid = 7 date = 1090252769
.
.
The values returned are the correct values stored for my userid/forum.

I'm sure it's just my thick head getting in the way of making this work. I'll keep messing around with it and see if I can figure out what I'm doing wrong.

[edschaum]

hmm i think this should work, but you have t make sure the entries for lastreads do exist!




I got it working! I removed the "AND NOT lastreads.lastreads_id IS NULL" and it started working right away. I'll figure out how to deal with error conditions elsewhere, that's unimportant compared to this leap forward.

I can't thank you enough for this, it is the key to making the hack useful, and you've made it possible. I could never have put that join together even if I stared at the query for a year.

Ed

[Xenon]

:)

glad i could help :)

Good you figured out how to solve it. Not that i see right now, why that line doesn't fit, but well ;)