Hi I'm trying to start a dating site and need help with an SQL statement. Basically all the members would be shown when searched for except for those who have blocked the member that is visiting and searching. I'll know who the member that's visiting is by capturing is username with php $_SESSION['username'] and also his IP address.

These are the columns in the tables I have...

members

id,
username,
photo,
ip

blocked

id,
name_of_banned,
ip_of_banned,
banned_by
member_id_of_banned_by

so basically select all from members except for the members appearing in the
"banned_by" column of the blocked table that also have named_of_banned = to the current $_SESSION['username'] or the ip_of_banned = to the current users IP (I'll use $_SERVER['REMOTE_ADDR'])

Please note I want to be able to check by both $_SESSION['username'] and $_SERVER['REMOTE_ADDR'] since perhaps the user that's banned may be browsing whilst not logged in.

many thanks
juanc is online now Report Post Edit/Delete Message

sorry if i have mucked up the code, i havn't tested it... So yeh :P But hope it works. Any problems just reply.

is that a query inside a loop? does the inner query return all the members except one? and the inner query does this as many times as there are rows in the blocked table?

ahh yes, u have made me think. I have juffed it up.

Im off to bed now (GMT) but if nobody has sorted it by morning i will happily fix.

Thanks for pointing that out though mate.

Hi there since I've posted this I've found the answer so I thought I would post here so anyone else may benefit.....I needed a NOT IN subquery

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]'
)
LIMIT 0 , 30