CODEWITHSUNDEEP
phpsqlmysqljoinunion
JulesUK
JulesUK

Reputation: 487

What is the quickest and most efficient way to combine two MYSQL queries into one JSON result

I know how to use basic SQL queries but need to know the best way to combine two queries.

For example, I can output users within a certain distance and it automatically adds a distance field. Ideally what I would like to do is do a further query on a different table and add another field to the output. I am trying to explain this the best I can, so please bear with me if it seems an odd way to.

I have looked into UNION and JOINs but can't quite grasp the concept.

So I can do a query to get users within a certain distance as I said like so from a table named users For simplicity I have already added the LAT and LNG for original coordinates :-

$logged_user = $_GET["logged_user"];

$sql = "SELECT 
*, 
(
   3959 *
   acos(cos(radians(52.41357)) * 
   cos(radians(lat)) * 
   cos(radians(lng) - 
   radians(-1.51314816)) + 
   sin(radians(52.41357)) * 
   sin(radians(lat )))
) AS distance 
FROM users 
HAVING distance < 10 
ORDER BY distance LIMIT 0,99";
$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));
 
$userOnlineStatus = array();
while($row = mysqli_fetch_assoc($result)){
    $userOnlineStatus[] = $row;
}

echo json_encode($userOnlineStatus);

This will output something like : -

[{"id":"10102","userID":"933ce029-e0b3-4e18-ad52-9e6c2019189b","name":"test","username":"test","verified":"YES","lat":"52.4194975","lng":"-1.5101260","status":"offline","distance":"0.4289222996651918"}],

This automatically adds the table distance to the output. How do I then check to see if logged_user has liked another user from the first result from another table?

The second table called isLike simply has 2 fields, from_user and to_user, the from_user will be the logged_user. As it stands, I am running another separate query like so :-

$logged_user = $_GET["logged_user"];
$to_user = $_GET["to_user"];

$sql="SELECT * FROM likes  WHERE to_user = '$to_user' AND logged_user = '$logged_user'";  
$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

if ($result->num_rows > 0) {
echo "true";
} else {
echo "false";
}

As you can see this is far from ideal and it has to run a query on every user individually. The to_user is the ID of user from the first results.

So what I would like to is check to see that the logged_user has an entry of each of the to_user from the first result and add a new field called isLiked with either true or false.

The ideal output would be :-

[{"id":"10102","userID":"933ce029-e0b3-4e18-ad52-9e6c2019189b","name":"b","username":"933","verified":"YES","lat":"52.4194975","lng":"-1.5101260","status":"offline","distance":"0.4289222996651918","isLiked":"true"}],

I know and I am sorry if I may of explained this in a way that is hard to understand but I cannot really word it any other way.

So which method is the best or am I approaching this in the totally wrong way? I am not asking for anyone to do this, just point me in the right direction.

Many thanks in advance

Upvotes: 0

Views: 84

Answers (1)

bsraskr
bsraskr

Reputation: 630

I think, the best approach is to use a single SQL query with a LEFT JOIN.

Could you please try this,

$logged_user = mysqli_real_escape_string($conn, $_GET["logged_user"]);

$sql = "
SELECT 
    u.*, 
    (
        3959 * acos(
            cos(radians(52.41357)) * 
            cos(radians(u.lat)) * 
            cos(radians(u.lng) - radians(-1.51314816)) + 
            sin(radians(52.41357)) * 
            sin(radians(u.lat))
        )
    ) AS distance,
    CASE 
        WHEN l.to_user IS NOT NULL THEN 'true'
        ELSE 'false'
    END AS isLiked
FROM users u
LEFT JOIN likes l 
    ON u.id = l.to_user 
    AND l.from_user = '$logged_user'
HAVING distance < 10
ORDER BY distance 
LIMIT 99";

$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

$userOnlineStatus = array();
while ($row = mysqli_fetch_assoc($result)) {
    $userOnlineStatus[] = $row;
}

echo json_encode($userOnlineStatus);

and you should also think about indexing.

Hope it will works!

Upvotes: 0

Related Questions