The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
||||
|
||||
Trying to change thread query to accept more dates
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: Code:
$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] "); thanks, Ed |
#2
|
||||
|
||||
hmm i think this should work, but you have t make sure the entries for lastreads do exist!
PHP Code:
|
#3
|
||||
|
||||
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 |
#4
|
||||
|
||||
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 |
#5
|
||||
|
||||
Yes, the data already exists in lastreads. In addition to a visual check of the table, I also ran the following test query:
Code:
//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>'; } 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. |
#6
|
||||
|
||||
Quote:
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 |
#7
|
||||
|
||||
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 |
Thread Tools | |
Display Modes | |
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|