homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

sorting using data from two tables.

5+ Year Member

Msg#: 3591060 posted 5:59 pm on Mar 4, 2008 (gmt 0)

I have a custom-made forum system for which I'm trying to implement a somewhat unusual feature >_>

currently, threads are sorted just like any forum system: by the date they were last updated - a value which is stored to each thread whenever a new post is made to that thread.

however, my forum has an "ignore" feature, allowing people to hide posts in threads from anyone they've ignored. the problem is that, since each thread only knows when it was last updated, ignoring a person does not prevent their post to a thread from being moved up to the top. really, we'd like to ignore their post entirely, including the "last update" time stamp.

therefore, in the event someone has people on their ignore list, I'd like to sort the threads in a more complicated way :P I'd like to find the last post made to a thread that is NOT from someone on their ignore list, and use that post's date as the last update value for the thread, and sort by that. fun?

but I can't figure out how to state the query :)

so here's the basic, normal way to sort, for example/reference:

$command = 'SELECT * ' .
'FROM board_threads ' .
'WHERE board=' . $boardid . ' ' .
'ORDER BY sticky DESC, updatedate DESC ' .
"LIMIT $start_thread,$num_threads";

and after a few failed attempts to sort in the new, desired way, I ended with this, also-failed attempt >_> I know that part of why I can't come up with something that works is because I cannot figure out how to select the single, last post by which to sort the threads... I've been trying things I know would not work anyway, to try and get some clues from the error messages as what might actually work. MySQL error messages seem to be almost entirely useless, however :P Anyway, maybe this will help illustrate what I'm trying to accomplish. Or maybe it will only illustrate how lost I am :P

$command = 'SELECT * FROM board_threads ' .
'WHERE board=' . $boardid . ' ' .
'LEFT JOIN board_posts ON board_posts.thread=board_threads.idnum ' .
'WHERE board_posts.createdby NOT IN (' . $ignore_list . ') ' .
'ORDER BY board_threads.sticky DESC, board_posts.creationdate DESC ' .
"LIMIT $start_thread,$num_threads";

where $ignorelist is a comma-separated string of user idnumbers, as I think you might easily guess.

any help would be greatly appreciated. I'm sure I'm missing one vital thing that will make everything else fall in to place.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved