Skip to content
Advertisement

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

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:

enter image description here

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.

Advertisement

Answer

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()) {
    ...
}
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement