Hello readers!

If you are developing the web based friendship structure, Then in this blog we will help you to make myfriends query with database.

Let's suppose you have database in following structure

In the table above, the senderMob is the friend, who have sent an invitation to receiverMob and receiverMob will confirm this invitation after accepting a friend request. If you look into the case for 2nd row a user has sent request to 9458168754. Hence, in this case this mobile is a receiver one, so it should also come in the friends query.

Now, in this case, we will get the friends of user with mobile 9458168754 using cake PHP  3X

The query will go like this:-

/**
 *
 * @access public
 * @return type array
 */
public function myFriends() {

        extract($this->request->data);
        $limit = isset($limit) ? $limit : 10;
        $offset = isset($offset) ? $offset : 0;     
        $receiverMob = $this->current_user['mobile'];       

        // for getting count
        $conn = ConnectionManager::get('default');
        $stmt_count = $conn->execute("SELECT U.id
        FROM users U, invitations F
        WHERE
        CASE
        WHEN F.receiverMob = $receiverMob
        THEN F.senderMob = U.mobile
        WHEN F.senderMob= $receiverMob
        THEN F.receiverMob= U.mobile
        END
        AND 
        F.confirm='1' ");
        $results_count = $stmt_count ->fetchAll('assoc');
        $totalCount = count($results_count);    

        $stmt = $conn->execute("SELECT  F.id as invitaionId, U.id , U.username, U.mobile, U.image
        FROM users U, invitations F
        WHERE
        CASE
        WHEN F.receiverMob = $receiverMob
        THEN F.senderMob = U.mobile
        WHEN F.senderMob= $receiverMob
        THEN F.receiverMob= U.mobile
        END
        AND 
        F.confirm='1' LIMIT $limit, $offset");
        $results = $stmt ->fetchAll('assoc');

       if($totalCount > 0){
          $i=0;


        //  print_r($results);die;
        
          foreach ($results as $result) {
             $contacts[$i]['invitationId'] = $result['invitaionId'];
             unset($result['invitaionId']);            
             $contacts[$i++]['user'] = $result;             
           }
           $data = array();
           $data['total_count'] = $totalCount;
           $data['contacts'] = array_values($contacts);          
        } else {
        $data['total_count'] = 0;
        $data['contacts'] = array();
       }
       return $this->_returnJson(true, 'Users accepted friends list', $data );        
  }

In the code above will have used the CASE, This is used in two-way join in user table. 
This function will generate the output in json format with limit and offsets.

The output of the above code in json format will be as shown below:-

{
    "status": "success",
    "message": "Users accepted friends list",
    "data": {
        "total_count": "3",
        "contacts": [
            {
                "invitationId": "34",
                "user": {
                    "id": "87",
                    "username": "Test1",
                    "mobile": "+911234458796",
                    "image": "https://www.google.co.in/search?q=images&client=ubuntu&hs=p0X&channel=fs&source=lnms&tbm=isch&sa=X&ved=0ahUKEwiJquW8wPvSAhVMpY8KHQCSA_EQ_AUICCgB&biw=1535&bih=805#imgrc=TCzIUatmlfq74M:"
                }
            },
            {
                "invitationId": "30",
                "user": {
                    "id": "91",
                    "username": "Test9",
                    "mobile": "+91129023867",
                    "image": "logo.jpg"
                }
            },
            {
                "invitationId": "32",
                "user": {
                    "id": "92",
                    "username": "Test10",
                    "mobile": "+91129023867",
                    "image": "logo.jpg"
                }
            }
        ]
    }
}