Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to Make MyFriends Query in MySQL?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 594
    Comment on it

    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

    ID senderMob receiverMob confirm created
    1 9458168754 +911234458796 1 2017-03-30 06:25:00
    2 +911234567890 9458168754 1 2017-03-30 03:35:00
    3 9458168754 +91129023867 1 2017-03-30 04:12:00

    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"
                    }
                }
            ]
        }
    }

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: