How to JOIN MySQL tables with specific ID of each table with PHP

Question

I don't know how to join tables and fetch informations from each of them. I have clients, who has made reservations and when I click on specific client I need not only information about him from clients table, but also need information related to him by id from reservations table.

This is my ER diagram for better database understanding:

In the overview table of all reservations, based on its status I am using this query:

<?php
      $query = $conn->query("SELECT * FROM `reservations` NATURAL JOIN `clients` NATURAL JOIN `houses` WHERE `status` = 'Pending' ORDER BY firstName") 
      or die(mysqli_error());
      hile($fetch = $query->fetch_array()){
?>

It works perfectly fine, but now I need to display the reservations related only to the specific user I clicked on via link based on ID, example:

<td> <a href = "./Client-Detail.php?client_id=<?php echo $fetch['client_id']?>">
         <?php echo $fetch['firstName']." ".$fetch['lastName']?></a></td>  

I am not quite sure the right way how to display the reservation data of specific client.

Answer 1

You need to put the client_id check in the WHERE clause instead of checking status.

$stmt = $conn->prepare("
    SELECT * 
    FROM `reservations` 
    NATURAL JOIN `clients` 
    NATURAL JOIN `houses` 
    WHERE client_id = ?");
$stmt->bind_param("i", $_GET['client_id']);
$query = $stmt->execute();
while ($fetch = $query->fetch_array()) {
    ...
}

Answer 2

SELECT r.*, c.*, h.* 
FROM `reservations` r 
LEFT JOIN `clients` c ON (c.client_id = r.client_id) 
LEFT JOIN `houses` h ON (h.house_id = r.house_id)
WHERE r.status = 'Pending' AND c.client_id = '$0'
ORDER BY c.firstName ASC

This should work and bind ID via PDO or MySQLi