CODEWITHSUNDEEP
phpmysql
kellymandem
kellymandem

Reputation: 1769

Looping through database table data and displaying it in a proper format

I have two tables in the database.

TABLE ROOM has the following columns and data

id(int)    room_type(varchar)      price(int)
 1           single room              25000
 2           double room              50000

And TABLE ROOM_IMAGE has the following columns and data

id(int)     image(varchar)         room_id(int)
 1            single.jpg                 1
 2            single1.jpg                1
 3            double.jpg                 2

When i use the following PHP code:

    <?php
    $query = "SELECT a.room_type, s.image FROM room as a 
            JOIN room_image as s
            ON a.id = s.room_id ";
?>
<?php if ($rooms = $mysqli->query($query)) { ?>

  <?php while ($room = $rooms->fetch_object()) { ?>

    <h4><?php echo $room->type; ?></h4>
    <p><?php echo $room->image; ?></p>

  <?php } ?>

<?php } ?>

I get the following results:

        single room
       single.jpg
    single room
           single1.jpg
    double room
           double.jpg

Yet i want my results to display as follows

        single room
           single.jpg
           single1.jpg
        double room
           double.jpg

So could somebody help me on write the proper php syntax to produce the desired result (preferably using a join sql statement)

Upvotes: 0

Views: 64

Answers (2)

user2067005
user2067005

Reputation: 839

Instead of using a join query. Just add the images to a field under the room table and query just one table.

Database:

id(int)    room_type(varchar)      price(int)    images(varchar)
 1           single room              25000         ["single.jpg","single1.jpg"]
 2           double room              50000         ["double.jpg"]

PHP:

$images = json_decode($room->image);
foreach($images as $image){
  echo $image;
}

Upvotes: 0

Hanky Panky
Hanky Panky

Reputation: 46900

First add an ORDER BY clause to your query, based on room type. This will line up all the rooms of each type together

$query = "SELECT a.room_type, s.image FROM room as a 
        JOIN room_image as s
        ON a.id = s.room_id  ORDER BY a.room_type";

Then, display a type only if it was not displayed before. Use an extra variable to keep track of it.

 <?php 
  $lastType="";
  while ($room = $rooms->fetch_object())
    { 
      if($lastType!=$room->type) 
      {
       echo "<h4>",$room->type,"</h4>";
      }
      echo "<p>",$room->image,"</p>";
      $lastType=$room-type;          // This variable keeps track of room type display
    } 
 ?>

Upvotes: 1

Related Questions