how to perform 2 queries within same function using mysql db & slim 3 PHP framework

Question

I am trying to retrieve users that were friend requested in ios app and return them into a friend request list in app. To do this I am using a PHP function that queries the notification table for the user_id it was sent from, then return all all rows that match that with a specific notification type and are marked as unread. Then I would like to query my user table and return the users who have sent the request to this particular user_id. Here is the code I have constructed:

function getSquadRequests($req, $res) {
   global $db;

   $user_id = validateUserAuthentication($req);

   if ($user_id) {
       $query = $db->prepare(
         'SELECT * 
          FROM tblNotification 
          WHERE notification_to_id   = :user_id 
            AND notification_type    = 3 
            AND notification_is_read = 0
         ');

       $query->bindParam(':user_id', $user_id);

       if ($query->execute()) {
           $user = $query->fetch(PDO::FETCH_NAMED);

           // if user exist
           if ($user) {
               $query = $db->prepare(
                   'select * from tblUser where user_id = :not_from_id'
               );
               $query->bindParam(':not_from_id', $user['notification_from_id']);

               if ($query->execute()) {
                   $data = $query->fetchAll(PDO::FETCH_ASSOC);
               }

               $newRes = makeResultResponseWithObject($res, 200, $data);

           }
       } else {
           $newRes = makeResultResponseWithString(
               $res, 400, $query->errorInfo()[2]
           );
   }

   return $newRes;
}

score 0 · Answer 1 · answered Apr 06 '18 at 07:36

If I understand you correctly, then your problem is only an sql problem and this query should return the expected users:

SELECT u.* 
FROM tblUser AS u
    LEFT JOIN tblNotification AS n 
    ON u.user_id = n.notification_from_id
WHERE n.notification_to_id    = :user_id
  AND n.notification_type     = 3 
  AND n.notification_is_read  = 0
  AND n.notification_from_id <> :not_from_id

how to perform 2 queries within same function using mysql db & slim 3 PHP framework

1 Answers1