how to echo specific columns of database using while loop with 3 different tables

Question

I want to select different columns form 3 different tables and shows in table data .

i have a table named as room. in that that i have two foreign keys named as Hotel_id and Room_Type_id . other columns in room table is price , facilities etc. i want to get hotel_name from hotel table using FK hotel_id and room_type_name from room_type table Using FK Room_Type_id.

i had a lot of brain storming and make separate queries using joins to find hotel_name and room_type_name. using below queries

//Query to find hotel_name

$query = "SELECT hotels.hotel_name FROM hotels INNER JOIN room ON room.Hotel_Id=hotels.hotel_id";
$res = mysqli_query($connection, $query);
while ($data1 = mysqli_fetch_array($res)) {

//Query to find room_type_name:
 $sel_cus = "SELECT room_type.Room_Type_Name FROM room INNER JOIN room_type ON room.room_type=room_type.Room_Type_Id";
$res_cus = mysqli_query($connection, $sel_cus);
while ($data = mysqli_fetch_array($res_cus)) {

//Query to select price,and picture of room:
    $count=1; // counter to print serial numbers in table
$sel_cus = "select * from room Order by room_id ASC";
$res_cus = mysqli_query($connection, $sel_cus);
while ($row = mysqli_fetch_array($res_cus)) {

// here is code to print record in table

    <tbody>
            <tr>
            <td><?php echo $count;  ?></td>

            <td ><?php echo $data1['hotel_name'];?></td> 
                    <td><?php echo $data['Room_Type_Name']; ?></td>
                <td><?php echo $row['price']; ?></td>




  } // End Query to find hotel_name 


                                   } // End Query to find room_type_name:

} // end of Query to select price,and picture of room

above code iterate hotel_name and room_type_name as per record lies in room table currently there is 2 records lies in room table it iterate record 2 times , Please help me to sort this issue.

Output with above code :

Sr      hotel_name         Roomtype   price      picture 
 1       ABC                Delux       600       a.jpg
 2       ABC                Delux       600       a.jpg
 3       XYZ                 Super      8000      bcv.jpg
 4       XYZ                Super       8000      bcv.jpg

I want the output like below
Sr      hotel_name         Roomtype   price      picture 
 1       ABC                Delux       600       a.jpg
 2      XYZ                 Super      8000       bcv.jpg

Please help

ScaisEdge · Accepted Answer · 2019-04-02 18:18:22Z

1

You could do using just a single query eg:

SELECT hotels.hotel_name 
    ,  room_type.Room_Type_Name
    , room.price 
    , room.facilities
    , room.picture 
FROM hotels 
INNER JOIN room ON room.Hotel_Id=hotels.hotel_id
INNER JOIN room_type ON room.room_type=room_type.Room_Type_Id
ORDER BY hotels.hotel_name ,  room_type.Room_Type_Name, room.price

answered Apr 2, 2019 at 18:18

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to echo specific columns of database using while loop with 3 different tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related