How to get two sets of results fom DB - users that own an item, and friends of the user, that own the item

Question

So I have a MySQL database that I want to get two sets of results from for my Android app.

I want to have all the users that own a certain item, and also I want to have a set of those users that are friends of the current user (and own this item)

I dont want to do it in the android code, so I have to do it on the server.

Im handling the database using php.

Here is the script and query that get the users that own this item:

////////////////////////////////////////////////////////////////////
///////////////GET ITEM OWNERS ARRAY ///////////////////////////////
////////////////////////////////////////////////////////////////////

$query = "SELECT user_items.user_id, users.user_name, users.user_sex, users.user_avatar
          FROM user_items
          LEFT OUTER JOIN users ON users.user_id = user_items.user_id
          WHERE item_id = :item_id"; 

try {

    $sth = $connection->prepare($query);
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    $sth->execute(array(':item_id' => $item_id));   
    $response["item_users"] = $sth->fetchAll();


} catch (PDOException $ex) {

    $response["success"] = $http_response_server_error;
    $response["message"] = $http_message_server_error . " " . $ex;

} 
////////////////////////////////////////////////////////////////////
$connection = null;
$response["success"] = $http_response_success;
echo json_encode($response);

Here is my friendships table:

CREATE TABLE IF NOT EXISTS `friendships` (
  `friendship_id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_one` int(11) NOT NULL,
  `friend_two` int(11) NOT NULL,
  `time_of_friendship_start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`friendship_id`),
  KEY `friendships_ibfk_1` (`friend_one`),
  KEY `friendships_ibfk_2` (`friend_two`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `friendships`
--
ALTER TABLE `friendships`
  ADD CONSTRAINT `friendships_ibfk_1` FOREIGN KEY (`friend_one`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `friendships_ibfk_2` FOREIGN KEY (`friend_two`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 1

As I understand, you need to have all users with the item, and the ones who are friends with the current user shall be marked. You should add a BOOLEAN column to your query to mark the friends in the list.

SELECT
    user_items.user_id,
    users.user_name,
    users.user_sex,
    users.user_avatar,
    (SELECT COUNT(*) FROM friendships WHERE friend_one=user_items.user_id AND friend_two=:currentUserID) as is_friend
FROM user_items
LEFT OUTER JOIN users ON users.user_id = user_items.user_id
WHERE item_id = :item_id

If by some restriction you absolutely have to send two result sets (two lists), then assign the first as you did to $response['item_users'] then do the second query and assign it to $response['item_friends'], so they try block should look like this:

$sth = $connection->prepare($query);
$sth->setFetchMode(PDO::FETCH_ASSOC);
$sth->execute(array(':item_id' => $item_id));   
$res1 = $sth->fetchAll()
$sth = $connection->prepare('SELECT user_items.user_id, users.user_name, users.user_sex, users.user_avatar
      FROM user_items
      LEFT OUTER JOIN friendships fr ON fr.friend_one=user_items.user_id
      LEFT OUTER JOIN users ON users.user_id = user_items.user_id
      WHERE item_id = :item_id AND fr.friend_two=:currentUserID');
$sth->setFetchMode(PDO::FETCH_ASSOC);
$sth->execute(array(':item_id' => $item_id,'currentUserID'=>?????));   
$res2 = $sth->fetchAll()
$response['item_users'] = $res1;
$response['item_friends'] = $res2;

Note two further things:

1. These codes assume that the friendships table is symmetric, so if it includes a (friend_one=1, friend_two=2) record than it also has one with (friend_one=2, friend_two=1), otherwise you'd need more complex queries where the roles of the two fields are exchanged.
2. You use LEFT JOIN so you'll always get back at least one row if the item exists. If nobody has it, than that row will be filled with NULLs in the user-values. Sure this is what you're after, or would you trade them for INNER JOINs so that 0 rows will be returned if no-one has the item?

Answer 2

If your requests fetch the same number of columns, then you can use MySQL UNION directive