Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

sorting using data from two tables.

5:59 pm on Mar 4, 2008 (gmt 0)

New User

5+ Year Member

joined:Mar 4, 2008
posts: 2
votes: 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.