Need Help With Query Logic

[Mohammedmehdi]

Hello

 

2 tables

- users (userid, username)

- friends (friendshipid, user1, user2, accepted)

 

I have a friends search box (works like google's/facebook's auto suggest). 

This search box is meant to be used to pick a friend to send a private message to.

So far, all my code can do is autosuggest people on the social network (basically display all people on the users table based on the characters typed).

 

<?php

 mysql_connect("localhost","username","pass");

 mysql_select_db("dbname");



 $term=$_GET["term"];



 $query=mysql_query("SELECT userid, username FROM users where username like '%".$term."%'");

 $json=array();



    while($friend=mysql_fetch_array($query)){

         $json[]=array(

                    'value'=> $friend["username"]

                        );

    }



 echo json_encode($json);



?>

 

 

I wanted it to filter out those who are not friends.

 

BTW,  just to clarify, the logged in user who wants to send a private message, his userid could be on either user1/user2 of the friends table.

 

Any idea behind the logic that could sort this issue?

Will really appreciate any help

[Barand]

try

$sql = "SELECT u.userid, u.username
    FROM users u
    JOIN friends f ON u.userid = f.user1
    WHERE f.user2 = $loggedinuser AND u.username LIKE '%$term%'
    UNION
    SELECT u.userid, u.username
    FROM users u
    JOIN friends f ON u.userid = f.user2
    WHERE f.user1 = $loggedinuser AND u.username LIKE '%$term%'
    ORDER BY username";