CODEWITHSUNDEEP
phpmysql
roa3
roa3

Reputation: 901

Can I merge two MySQL queries into one?

I had the following code, which uses 2 queries to get the friends list.

<?php

$getFriendStatement = <<<EOS
  SELECT DISTINCT u.username
    FROM users AS u 
      INNER JOIN test_friends AS f on u.Id = f.user_id 
    WHERE f.friend_id = ?
       && f.active=1
EOS;

$getFriendQuery = $mysqli->prepare($getFriendStatement);
$getFriendQuery->bind_param('i', $userID);
$getFriendQuery->execute() or die ($mysqli->error);
$getFriendResult = $getFriendQuery->get_result();

$friendName = "";

while ($getFriendFetch = $getFriendResult->fetch_assoc()) {
  $friendName .= $getFriendFetch['username'] . ", ";
}


$getFriendStatement = <<<EOS
    SELECT u.username 
      FROM users AS u 
        INNER JOIN test_friends AS f ON u.id = f.user_id 
      WHERE (f.friend_id = ? AND active=1) 
         OR (f.user_id = ? AND active=1)
EOS;

$getFriendQuery = $mysqli->prepare($getFriendStatement);
$getFriendQuery->bind_param('ii', $userID, $userID);
$getFriendQuery->execute() or die ($mysqli->error);
$getFriendResult = $getFriendQuery->get_result();


while ($getFriendFetch = $getFriendResult->fetch_assoc()) {
  $friendName .= $getFriendFetch['username'] . ", ";
}
                        

if (!empty($friendName)){
  echo "Your friends: " . $friendName ;
} else {
  echo "You do not have any friends yet";
}   

?>

Is there a way to execute just 1 query to retrieve all friends?

Schema information: the above relies on two tables, users and test_friends:

CREATE TABLE users (
  `id` int(11),
  `username` varchar(256)
);

CREATE TABLE test_friends (
  `user_id` int(11),
  `friend_id` int(11),
  `active` tinyint,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`)
    ON UPDATE CASCADE ON DELETE CASCADE
);

Upvotes: 4

Views: 18184

Answers (4)

Michael Logothetis
Michael Logothetis

Reputation: 137

As your first query appears to be a subset of your second query, you should only need to execute the second query.

I assume that in the Test_Friends table the user_id field represents the userid of the user and the friend_id field represents the userid of the user's friend.

If this is the case then you can execute the query:

SELECT DISTINCT U.username 
   FROM Test\_Friends F 
   INNER JOIN Users U ON F.friend\_id = U.user\_id 
WHERE 
   F.user\_id = '{$userID}' AND 
   F.active = 1

Upvotes: 0

Evan
Evan

Reputation: 18599

You should be able to do a union to do both queries in one. Your SQL will look like this:

SELECT U.username
FROM   users AS U
INNER JOIN test_friends AS F
ON     U.Id = F.user_id
WHERE  F.friend_id = '{$userID}'
AND    F.active = 1
UNION
SELECT u.username
FROM   users u
INNER JOIN test_friends f
ON     u.id = f.user_id
WHERE  ( f.friend_id = '{$userID}'
AND      active = 1 )
OR     ( f.user_id = '{$userID}'
AND      active = 1 )

It will also remove duplicates for you automatically, as if you included DISTINCT on the whole lot. (You do "UNION ALL" if you don't want that.)

Also, if you want to order the results, add "ORDER BY 1 ASC" on the end. You can only use result set column numbers in the ORDER BY clause with unions.

Union queries only work if the number and types of the columns returned in the result set by each sub-query are the same.

Aside: Your first query appears to be a subset of the second query, so you really only need to so the second query. I've left it as is as a demonstration of how to do unions, but you don't really need to in this case.

Upvotes: 6

jab11
jab11

Reputation: 867

so you want names of $userID's friends AND names of users who have $userID as friend ? how about

select distinct U.username 
from users U 
inner join test_friends f 
on 
    (f.user_id = U.id AND f.friend_id={userID}) OR 
    (f.friend_id=U.id AND f.user_id={userID})
where active=1

Upvotes: 0

Beep beep
Beep beep

Reputation: 19141

You can perform a UNION between the two queries. For example:

SELECT username FROM users WHERE username like '%billy%'
UNION 
SELECT username FROM users WHERE username like '%bob%'

will return all users with names like billy or bob. Combining your entire two queries above with a UNION should work.

Upvotes: 3

Related Questions