vb.org Archive - Trying to change thread query to accept more dates

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Trying to change thread query to accept more dates (https://vborg.vbsupport.ru/showthread.php?t=67448)

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]

        ");

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

thanks,

Ed

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

PHP 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')

        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]

    ");

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

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

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>';



}

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.

Quote:

Originally Posted by Xenon

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

:)

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 ;)

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01100 seconds Memory Usage 1,737KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: