Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Unanswered: Need help with a simle SQL statement

    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

  2. #2
    Join Date
    Apr 2008
    Location
    West Sussex
    Posts
    19
    Quote Originally Posted by juanc
    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
    PHP Code:
    $getusers mysql_query("SELECT * FROM `blocked`");
    while (
    $gotusers mysql_fetch_array($getusers)){
    $prousers mysql_query("SELECT * FROM `members` WHERE `username` != '$gotusers[username]'");
    while (
    $allusers mysql_fetch_array($prousers)){
    echo (
    "$allusers[username]<br>");
    }

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2008
    Location
    West Sussex
    Posts
    19
    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.

  5. #5
    Join Date
    Dec 2005
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •