I have the following two queries that show the number of books and the number of movies for each user that match the choices of one selected user. I need some suggestions as to how I can use this information to find the user who best matches the one selected user by having the most matching books and movies when combined. I can't think how to structure the SQL query to achieve this. I keep thinking I will need to combine the two queries into a single query, but I am having trouble doing this.
$userID = $_POST['userID'];
//calculate how many books for each user that match the selected user
$sql1 ="
SELECT userID, COUNT(user_book.userID) AS 'subtotalBooks'
FROM user_book
INNER JOIN user USING (userID)
WHERE bookID IN
(
SELECT bookID
FROM user_book
WHERE userID='$userID'
)
GROUP BY user.userID
";
$result1 = mysqli_query($con, $sql1) or die(mysqli_error($con));
echo "<h2>Books</h2>";
while ($row1 = mysqli_fetch_array($result1))
{
echo "<p>User " . $row1['userID'] . " likes " . $row1['subtotalBooks'] . " books the same as User " . $userID . "</p>";
}
//calculate how many movies for each user that match the selected user
$sql2 ="
SELECT userID, COUNT(user_movie.userID) AS 'subtotalMovies'
FROM user_movie
INNER JOIN user USING (userID)
WHERE movieID IN
(
SELECT movieID
FROM user_movie
WHERE userID='$userID'
)
GROUP BY user.userID
";
$result2 = mysqli_query($con, $sql2) or die(mysqli_error($con));
echo "<h2>Movies</h2>";
while ($row2 = mysqli_fetch_array($result2))
{
echo "<p>User " . $row2['userID'] . " likes " . $row2['subtotalMovies'] . " movies the same as User " . $userID . "</p>";
}
