Jump to content


Photo

Need Help With Query Logic

php mysql

  • Please log in to reply
1 reply to this topic

#1 Mohammedmehdi

Mohammedmehdi

    Newbie

  • New Members
  • Pip
  • 1 posts

Posted 18 October 2013 - 12:36 PM

Hello :D
 
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 :)


#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,190 posts
  • LocationCheshire, UK

Posted 18 October 2013 - 01:57 PM

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";

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com