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

Question

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)

Hanky Panky · Accepted Answer · 2014-06-18 07:27:22Z

1

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
    } 
 ?>

answered Jun 18, 2014 at 7:27

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

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

1 Comment

kellymandem Over a year ago

Thank you very much @Hanky 웃 Panky. Makes a lot of sense.

user2067005 · Accepted Answer · 2014-06-18 07:30:16Z

0

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;
}

answered Jun 18, 2014 at 7:30

user2067005

8397 silver badges15 bronze badges

5 Comments

Hanky Panky Over a year ago

That would be a terrible database design

kellymandem Over a year ago

That would violate database design rules of Fisrt normal form of no repeating groups and each cell in a table should contain one value.

user2067005 Over a year ago

I have seen databases use JSON arrays do to the fact that storing individual values into there own table and record would be absurd and degrade performance when queried for all the values. By the way, rules are meant to be broken.

kellymandem Over a year ago

Thanks for the great idea anyway @user2067005, would probably use it some other time but not right now. Thank you again.

user2067005 Over a year ago

No problem. In all honestly, it's best to do it the way you are. Easier to manage the pictures.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related