Need help with a complex SQL statement

juanc · #1 (**permalink**) 05-22-08, 05:41

Hi I’m trying to start up a dating site (in php) but need some help with writing an SQL statement for showing members that have signed up.

Basically members are shown via an online form but I need to filter out those that have blocked the person that happens to be viewing.

For example if member “gorgeous_girl” blocks member “dirty_old_man”……so when “dirty_old_man” logs in and browses he can see all members except for “gorgeous_girl” since she’s blocked him.

So these are my mySQL tables

members

id,
name,
age,
username,
password,
email,
date_joined,
self,
seeking,
location,
logged_in,
ip

blocked

id,
id_of_banned,
name_of_banned,
ip_of_banned,
banned_by varchar,
id_of_banned_by

now here’s my statement before any blocking of the members….and it works fine.

Code:

SELECT id,
username,
intro,
photo
 FROM members WHERE (self = '$_SESSION[seeking]' AND seeking = '$_SESSION[self]')
 AND
 (age BETWEEN '$_SESSION[from]' AND '$_SESSION[to]') ORDER BY logged_in DESC

Now then I try to filter out those that blocked the member that’s logged in currently using a NOT IN sub query like so….

Code:

SELECT id,
username,
intro,
photo
 FROM members WHERE (self = '$_SESSION[seeking]' AND seeking = '$_SESSION[self]')
 AND 
(age BETWEEN '$_SESSION[from]' AND '$_SESSION[to]') 
AND
 (username NOT IN (SELECT banned_by FROM blocked JOIN members ON members.name = blocked.name_of_banned WHERE blocked.name_of_banned = '$_SESSION[username]')) ORDER BY logged_in DESC

Now this query does run without errors but it doesn’t do the job of “not showing” the people that blocked the current viewer.

So now here is a simpler version that does work!

Code:

SELECT username
FROM members
WHERE username NOT
IN (

SELECT banned_by
FROM blocked
JOIN members ON members.username = blocked.name_of_banned
WHERE blocked.name_of_banned = '$_SESSION[username]' OR blocked.ip_of_banned = '$_SERVER[REMOTE_ADDR]'
)

So basically can someone please advise on how the more complex version should be…..I think where I’m going wrong is in the order of all the stuff after the where clause and before the subquery.

many thanks

Juan.

Internet Services

Web Development

Digital Marketing

Consumer Tech