how to write this mysql query

Question

I am working on a social networking feature's site which has a section like facebook PEOPLE YOU MAY KNOW that consists the person who are not friends of logged in user.

TABLE 1 'users' structure: uid(primary), fname, lname, dob, etc.

TABLE 2 'friend_request' structure: id(primary) uid, fid, created

TABLE 3 'friends' structure: same as 'friend_request'

I want to show people other than logged in user and his/her friends.Because i want to keep it simple.

MYSQL query is below:

     <?php 
   /*
   section for displaying random person who are not friends.
   */
   $q =mysql_query("select * from users where uid!='".$_SESSION["logged"]."'");
   if(mysql_num_rows($q)>0)
     {
        while($fetch=mysql_fetch_array($q))
     {

    $sel=mysql_query("select * from friends where (uid='".$fetch["uid"]."' and
        fid!='".$_SESSION["logged"]."') or                    
     (uid!='".$_SESSION["logged"]."' and fid='".$fetch["uid"]."')") or   
          die(mysql_error());

      $num_rows=mysql_num_rows($sel);
      if($num_rows>0)
        {
        while($rows=mysql_fetch_array($sel))
         {

        $que=mysql_query("select * from users where uid='".$rows['uid']."' or
          uid='".$rows['fid']."'");                    
         if(mysql_num_rows($que)>0)
           {
            while($names=mysql_fetch_array($que))


            {

              ?>
        <li><a href="user_index1.php?id=<?php echo $names['uid']; ?>"><strong>
       <?php echo $names['fname']." ".$names['lname'] ?></strong></a></li>
       <?php }
        }
       }
      }
     }
    }
  ?>

i think my queries are wrong. Please suggest something so that i can detect the problem. — vinay singh, Apr 16 '13 at 18:10
what you exactly want to do you combined so many queries you can write it in single query — Yadav Chetan, Apr 16 '13 at 18:18
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Apr 16 '13 at 18:55
@FreshPrinceOfSO I know PDO but i am using conventional way because it works soon for me. But this news is really bad. Thanks for this info. — vinay singh, Apr 16 '13 at 19:01
@FreshPrinceOfSO But this is for PHP 5.5.0 and i am using PHP 5.3 — vinay singh, Apr 16 '13 at 19:02
Please see http://stackoverflow.com/questions/2941900/is-it-wrong-to-use-deprecated-methods-or-classes-in-java for why you shouldn't use depreciated functions. — RachelD, Apr 16 '13 at 19:36

RachelD · Answer 1 · 2013-04-16T19:59:45.470

0

Have you tried:

$sql = "SELECT * FROM `users`
        WHERE `id` NOT IN 
            (SELECT `fid` FROM `friends` WHERE `uid` = ".$_SESSION["logged"].")
        AND `id` != ".$_SESSION["logged"].";";

$que = mysql_query($sql);

By your comments I think were miss understanding each other. I have recreated the following tables in my MySQL database like so:

users

id    fname    lname
1      Bob1     One
2      Bob2     Two
3      Bob3     Three
4      Bob4     Four
5      Bob5     Five

friends

id    uid     fid
1      5       2
2      5       3
3      3       1
4      2       1
5      2       4

Then when I run the following query:

SELECT * FROM `users`
WHERE `id` NOT IN 
    (SELECT `fid` FROM `friends` WHERE `uid` = 1)
AND `id` != 1

I get the following result:

uid    fname    lname
2      Bob2     Two
3      Bob3     Three
4      Bob4     Four
5      Bob5     Five

Can you explain to me why that's not what your looking for? Or that Im not entering data into the databases incorrectly?

edited Apr 16 '13 at 19:59

answered Apr 16 '13 at 18:16

RachelD

4,072
9
40
68

@RachelID sorry for previous comment.It is not working correctly. friends are including in result. – vinay singh Apr 16 '13 at 18:34
I may not be understanding your database structure the way you described it. For the friends table the uid would be the logged in user and the fid would be the user.id for their friends right? Because that's what I was working with. – RachelD Apr 16 '13 at 18:40
no. that's not the case. uid and fid is both taken from TABLE users' uid. so logged in user may also in fid. That's why these queries are so messy. – vinay singh Apr 16 '13 at 18:55
That's what I was saying. Please see my edit where I explain what I mean more thoroughly. – RachelD Apr 16 '13 at 19:04
okay. i am checking my codes. – vinay singh Apr 16 '13 at 19:08
Great. Hope it works for you. If it does work out please accept my reply as the correct answer. – RachelD Apr 16 '13 at 19:21
@RachelID i edited your table. Please run this and see what happens.if UID of friends TABLE doesn't contain 1, then it shows wrong result. – vinay singh Apr 16 '13 at 19:44
According to your question you want return all users, that are not the logged in user and that are not the friends of the logged in user. So if you do not have any friends for the logged in user then you will return all users except the logged in user. Why is that not what you are asking for? – RachelD Apr 16 '13 at 19:57
See my edit for updates – RachelD Apr 16 '13 at 20:00
I have found the solution today.Now you can check my edits above. – vinay singh May 22 '13 at 20:28

vinay singh · Accepted Answer · 2013-05-22T20:26:12.487

0

The solution is :

   SELECT * FROM `users` WHERE `uid` NOT IN 
   (SELECT `uid` FROM `friends` WHERE `uid` = 1 or 'fid' = 1 union SELECT `fid` FROM 'friends' WHERE 'uid'=1 or 'fid'=1)
   AND `uid` != 1;

This will show the person who are not friend with uid=1 excluding logged in user. Thanks @RachelID for his suggestions.

edited May 22 '13 at 20:26

answered Apr 16 '13 at 20:10

vinay singh

55
8

how to write this mysql query

2 Answers2