CODEWITHSUNDEEP
phpmysqlcodeigniter
Priya Jaiswal
Priya Jaiswal

Reputation: 75

Get True false result in mysql php

I have two tables (friends and users) and i want to get list of all users but there should be clear that whether that is my friend or not,my current id is "81"

Friends
id      user_id     friend_id 
32      81          169       
33      81          170
34      83          171

Users

id      first_name
81      byt          
169     abc               
170     def         
171     xyz

I tried with following code but i am getting only users who are in my friend list, i want all but with status/result that is this my friend or not

$this->db->select('friends.user_id,friends.friend_id,users.first_name,users.last_name,users.image');
$this->db->from('friends');
$this->db->join('users', 'users.id=friends.friend_id');
$this->db->where('friends.user_id', $add_data['user_id']);

Upvotes: 2

Views: 127

Answers (2)

Cynical
Cynical

Reputation: 9568

If you want to do it all within MySQL, this is the query you have to run:

SELECT u.id, NOT ISNULL(f.friend_id) as is_friend
FROM users u LEFT JOIN 
   (SELECT f.friend_id FROM friends f WHERE f.user_id = 81) f
ON u.id = f.friend_id
WHERE u.id != 81;

Basically, you join all users with the table of all friends of a specific id. Then you check whether the friend_id is null (due to the left join behavior).

See here an example.

Upvotes: 1

pr1nc3
pr1nc3

Reputation: 8338

$this->db->select('friends.user_id,friends.friend_id,users.first_name,users.last_name,users.image');
$this->db->from('friends');
$this->db->join('users', 'users.id=friends.friend_id');

If you want to get them all but know which ones are your friends then you need to handle that in php. Above query will retrieve all the info from both tables.

Now moving futher:

$result = $query->result_array();

foreach($result as $row){

    if($row['user_id'] == $add_data['user_id']){
        $friendsArray[] = $row['friend_id'];
    }
}

This will create a new array with your friend_ids that match your user_id. Basically the logic you tried to do in your where clause i did it in php since you wanted to retrieve all of them.

If you want to use the rest data that are not in your friendlist you can alway use an else statement inside the foreach loop.

Upvotes: 0

Related Questions