Need help with SQL query to return latest threads but not from ignored users....

[charlesk]

Hey all,

I am attempting to modify the excellent "most recent threads" hack so that threads that were just responded to by someone on the ignore list are not shown. Before changing the code I am trying to figure out what SQL I need, so I have been testing with direct queries.

The closest I have gotten is this:

select post.*, thread.* from post,thread where post.userid not in ('275','135','1') AND post.threadid = thread.threadid order by post.dateline desc

In this case the "('275','135','1')" are the three sample user IDs I am "ignoring".

This works.. but the problem is that because it orders by posts, if there is more than one recent response to the same thread, the thread appears twice. I can't figure out how to get it to show only one thread no matter how many recent posts it has.

Can anyone help?

Thanks.

[miz]

so you tring to get lastest threads but not from ignored users
am i right ?

and please use php/sql tags in codes postings

[Dean C]

Rightio, I'm leaving for a couple of days now. I'll give you a hand when I get back providing no-one has helped you before then :)

[charlesk]

Correct miz. Sorry about not using the right tags.

Thanks Dean, hoping I can find some help sooner but if not, that will be nice. :)

I realized I can "kludge" around this by keeping track of the threads already seen in the loop that processes the results, but that's ugly.)

[sabret00the]

can't you select distinct?

[charlesk]

distinct doesnt work because the post information is different.

c

[BluPhoenix]

add this before the latest threads code:

// ## USERS IGNORE ##
if (trim($bbuserinfo['ignorelist'])) {
$ignoredposters = 'AND thread.postuserid NOT IN ('.str_replace(' ', ',', $bbuserinfo['ignorelist']).')';
}


and then in the latest threads code, add:
$ignoredposters

just before:
ORDER BY lastpost

[charlesk]

Thanks. I found a kludgy solution already but that looks much more elegant. I need to take a look and see if I can adapt it tomorrow.

[charlesk]

Just checked this again, it doesn't do what I need because it ignores whole threads based on the thread starter. I need it to ignore on a post-by-post basis. Thanks anyway though.

[Andreas]

What about this:

select post.*, thread.* from post,thread where post.userid not in ('275','135','1') AND post.threadid = thread.threadid group by thread.threadid order by post.dateline desc

[miz]

What about this:

select post.*, thread.* from post,thread where post.userid notin('275','135','1') AND post.threadid = thread.threadid groupbythread.threadid order by post.dateline desc
the ignore list changes - if he add or remove ppl from ignore,
so then the query here wont work


if you have time to test
you can find a code on forumdisplay.php that dosent show threads from ignored users
try forumdisplay query and add
order by post.dateline desc

to query.