MySQL Query

[Zarkov]

i am trying to get a list of all members who have not posted in a particular group of forums.
The sql query i have so far is

$nonposters=$DB_site->query("SELECT threadid, forumid, postusername, postuserid, dateline FROM thread WHERE forumid=45 OR forumid=39 OR forumid=56 OR forumid=54 OR forumid=55 OR forumid=57 OR forumid=58 OR forumid=59");

Only problem is this gives me an array of all members that have posted in 1 of them forums and every post from them.

What i want is just a list of members that have not posted in those forums.

anyone any ideas on how to do that?

Thanks for your help.

[Toliman]

$nonposters=$DB_site->query("SELECT threadid, forumid, postusername, postuserid, dateline FROM thread WHERE forumid<>45 AND forumid<>39 AND forumid<>56 AND forumid<>54 AND forumid<>55 AND forumid<>57 AND forumid<>58 AND forumid<>59");

[Mas*Mind]

shorter:

$nonposters=$DB_site->query("SELECT threadid, forumid, postusername, postuserid, dateline FROM thread WHERE forumid NOT IN(45, 39, 56, 54, 55, 57, 58, 59");

[Zarkov]

Originally posted by Toliman
$nonposters=$DB_site->query("SELECT threadid, forumid, postusername, postuserid, dateline FROM thread WHERE forumid<>45 AND forumid<>39 AND forumid<>56 AND forumid<>54 AND forumid<>55 AND forumid<>57 AND forumid<>58 AND forumid<>59");

Thanks but that would give a list of all posts not in those forums, Would it not?

i want a list of all members who have not posted IN those forums.

[Toliman]

this would give you a list of all the users that haven't started a thread in those forums:

$nonposters=$DB_site->query("SELECT username FROM thread,user WHERE forumid NOT IN(45, 39, 56, 54, 55, 57, 58, 59) AND user.userid=thread.postuserid");

but maybe you want a list of all the users that haven't posted, no matter if it is in answer of a thread, then you could use this, but I think it's a little intensive query:

$nonposters=$DB_site->query("SELECT username FROM thread,user,post WHERE thread.forumid NOT IN(45, 39, 56, 54, 55, 57, 58, 59) AND user.userid=post.userid AND thread.threadid=post.threadid");

I haven't tested this queries yet, but I think they should work.

[Zarkov]

Thanks a lot Toliman, That first one looks like its the one. cheers

[Zarkov]

Hi, sorry to be a pain but thats not quite it.

That query gives me an array of all usernames posted in any forum but them ones.

What im after is we have a rule that members have to be active to retain their access. What im trying to do is get is a list of our members who have not started a new thread in one of them forums

[Toliman]

That query can actually give you the usernames of the people that have posted in that forum... then could you simply allow access to those people, then deny access to any one outside the list?

or we can correct the previous query to add also the users that haven't started a thread in any forum, hence wouldn't appear in the previous query:

$nonposters=$DB_site->query("SELECT username FROM thread,user WHERE (forumid NOT IN(45, 39, 56, 54, 55, 57, 58, 59) AND user.userid=thread.postuserid) OR user.posts=0");

[Zarkov]

hehe i didnt realise that the query statements could be so complex.

Im looking at bying a book on MySQL (rather than the PHP book i got) to learn it a little better.

thanks for all your help on this. :)