Server Help :: View topic - Looking for some SQL help

Server Help

Trash Talk - Looking for some SQL help

Mine GO BOOM - Tue Aug 10, 2004 12:33 pm
Post subject: Looking for some SQL help

Ok, I'm going to give a bit of effort into making an Ignore thread feature. To do so, I'll need a bit of SQL help. I'd like to make the SQL database ignore the threads for me, but am not sure how to go about it. Here is the current sql call for getting the list of threads:

Code: Show/Hide

$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
WHERE t.forum_id = $forum_id
   AND t.topic_poster = u.user_id
   AND p.post_id = t.topic_first_post_id
   AND p2.post_id = t.topic_last_post_id
   AND u2.user_id = p2.poster_id
   AND t.topic_type <> " . POST_ANNOUNCE . "
   AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
   $limit_topics_time
ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
LIMIT $start, ".$per_page;

The IGNORE_THREAD table is formated like this: user_id, thread_id. I'd like to throw into this SQL where it will not match if the thread_id is equal to one in the IGNORE_THREAD table in which the user_id's of the two lines match.

Once someone helps on this, it will be simple to add into the index.php and adding in the needed links to ignore the thread.

Dr Brain - Tue Aug 10, 2004 12:45 pm
Post subject:

add IGNORE_THREAD table as i to the FROM, and then AND i.user_id = u.user_id AND i.thread_it <> t.whatever_the_thread_id_is_called.

Solo Ace - Tue Aug 10, 2004 12:57 pm
Post subject:

So that would only select the records in the IGNORE_THREAD table which have the same user ID but not the same thread ID?

Guess that'd be the easiest way then, I started messing with IN ().

Dr Brain - Tue Aug 10, 2004 1:02 pm
Post subject:

You're right. That wouldn't work unless everyone had an entry in the ignore table.

Mine GO BOOM - Tue Aug 10, 2004 1:09 pm
Post subject:

I could always just do a check on every thread inside of PHP, but figured there maybe a better way.

Solo Ace - Tue Aug 10, 2004 1:56 pm
Post subject:

Well, if you're using Brain's way let's add a thread_id = -1 for every user_id.

And of course make sure new users get an entry too.