Link two tables with Order: MySQL and PHP

Question

I have this table called user_friends:

accid  |  user1  |  user2
1       mark_12     john_24
2       john_24     jim_01
3       mark_12     jim_01
4       nico_7      mark_12 

And this table called users_students:

studentuniqueid  |  firstname  |  lastname  |  age 
mark_12           Mark Anthony   Sierra       20
john_24           Johhny         Powers       21
jim_01            James          Sanders      21
nico_7            Nicolai        Renade       19 
janice_53         Janice         Allaine      24
peter_41          Peter          Allan        21

Now, here's what I wanted to do:

1.Select all entry/data in the user_friends table that has mark_12 (Mark's ID). Mark's ID could either be on user1 or user2 right? So I can just use SELECT * FROM user_friends WHERE user1 = 'mark_12' OR user2 = 'mark_12' and echo its accid, it will then display:

1, 3, 4 since mark_12 appears on those entries on the table.

2.Now I want to select and link the user_friends and users_student so that I could alphabetically arrange Mark's friends based on the user_friends table ORDERING them based on the lastname on the users_student table. I know this is possible, I just don't know how to do it yet.

I tried:

$user_id = $_SESSION['id'];
$sql = "SELECT user_friends.accid,users_student.studentuniqueid FROM user_friends INNER JOIN IN (user_friends.user1,user_friends.user2)=users_student.studentuniqueid WHERE user1 = '$user_id' OR user2 = '$user_id' ORDER BY users_student.lastname ASC ";

And also:

$sql = "SELECT user_friends.accid,users_student.studentuniqueid FROM user_friends INNER JOIN user_friends.user1,user_friends.user2=users_student.studentuniqueid WHERE user1 = '$user_id' OR user2 = '$user_id' ORDER BY users_student.lastname ASC ";

But somehow didn't work. I want to achieve something like:

Mark's Friends:

Johnny Powers
Nicolai Renade
James Sanders

Answer 1

I have solved it and made a demo for you in the SQLFiddle

SELECT users_students.FirstName, users_students.LastName FROM users_students 
LEFT JOIN user_friends ON user_friends.user2 = users_students.studentuniqueid 
WHERE (user_friends.user1 ="mark_12" OR user_friends.user2 ="mark_12" ) 
ORDER BY users_students.lastname ASC

Now you can change your mark_12 by yourvariable

Answer 2

you should use inner join on the result for the select for filter the accid

  select 
      t.accid
    , users_student.studentuniqueid 
    , users_student.firstname,
    , users_student.lastname  
  from (
  select accid 
  from user_friends
  where user1 = 'mark_12' 
  or user2 = 'mark_12' 
  ) t on. t.accid = users_student.studentuniqueid
  order by users_student.lastname ASC

Answer 3

you can use

select * from user_students where StudentUniqueId in (select user1 from user_friends where user2='mark_12' union select user2 from user_friends where user1 ='mark_12');

Answer 4

Do it like below:-

SELECT * FROM users_students LEFT JOIN user_friends ON user_friends.user2 = users_students.studentuniqueid WHERE (user_friends.user1 ="mark_12" OR user_friends.user2 ="mark_12" ) ORDER BY users_student.lastname ASC;

Output:- http://sqlfiddle.com/#!9/cd4043/6/0

Now your full code need to look like this (what you asked in comment):-

<?php
    //comment these two lines when your code satrted working fine
    error_reporting(E_ALL);
    ini_set('display_errors',1);

    //i have hard-code below two lines so comment them while using at your end
    $user_uniqueid = "mark_12";

    $con = mysqli_connect ('localhost','root','ourdesignz','stack');


    $mf_sql = "SELECT * FROM users_students LEFT JOIN user_friends ON user_friends.user2 = users_students.studentuniqueid WHERE (user_friends.user1 ='$user_uniqueid' OR user_friends.user2 ='$user_uniqueid' ) ORDER BY users_students.lastname ASC ";

    $mf_result = mysqli_query($con,$mf_sql) or die(mysqli_error($con));

    while($mf_row = mysqli_fetch_assoc($mf_result)){
        $friend_user1 = $mf_row['User1'];
        $friend_user2 = $mf_row['User2'];


        if($friend_user1!==$user_uniqueid){
          $ind_uniqueid =$friend_user1;
        }

        if($friend_user2!==$user_uniqueid){
          $ind_uniqueid =$friend_user2;
        }

        $us2_sql = "SELECT lastname FROM users_students WHERE studentuniqueid = '$ind_uniqueid' ORDER BY users_students.lastname ASC";
        $us2_result = mysqli_query($con,$us2_sql);
        $us2_row = mysqli_fetch_assoc($us2_result);


        $st_lastname = $us2_row['lastname'];

        echo $st_lastname."<br/>";
    }
?>