Generating results from a database depending on a completely seperate query.

[eluser]Timothy_[/eluser]
Hello,

I am currently stuck on an array problem I have and I would greatly appreciate all help and input.

Basically, I am generating a list of event bookings, each of which can have several rooms booked to it, which I am also trying to generate within the actual event booking.

So here is my controller at the moment:

Code:

//get any bookings where the status is 0
$data['get_bookings'] = $this->db->query(
    "SELECT id, title
    FROM bookings
    WHERE status = 0");

//
$data['get_rooms'] = $this->db->query(
    "SELECT room_name
    FROM room_bookings
    LEFT JOIN bookings ON bookings.id = room_bookings.booking_id");

//load the view and send the booking data as well    
$this->load->view('my_bookings', $data);

There are several problems with this.

1. The get_rooms query is going to be retrieving every single room booking ever created, potentially thousands. So I need my second query to get data from the first query on which booking_ids it is going to be retrieving, so that I only select the appropriate rooms needed. I don't know how to do this.

2. Once I get_rooms working properly I need to output the relevant room bookings to the view and output them within their booking.

Here is my view file:

Code:

<?php if($get_bookings->result_array()) { ?>
    <?php foreach($get_bookings->result_array() as $bookingRow): ?>
        Booking Name: <?php echo $bookingRow['title']; ?>
        Rooms Booked: <?php //OUTPUT NAMES OF ROOMS HERE ?>
    <?php endforeach; ?>
<?php } else { ?>
No Bookings Found
<?php } ?>

I know these are basic concepts, but I have been struggling getting my head around this problem for over a day so I would greatly appreciate all help.

Thanks,

Tim

[eluser]mddd[/eluser]
In your second query you are selecting from " room_bookings left join bookings ". But in the end you want to show the rooms by booking. So you are doing it the wrong way around. You need to put the main thing (bookings) left and the secondary thing (rooms) right.

Also, you can easily combine your queries. Why do you first look up the bookings and then the rooms separately? Joining them is easy.
Your total query should then be

Code:

SELECT bookings.id, bookings.title, GROUP_CONCAT(room_bookings.room_name) AS names
FROM bookings
LEFT JOIN room_bookings ON room_bookings.booking_id = bookings.id
WHERE bookings.status=0
GROUP BY bookings.id

In this code, I also grouped the rooms for each booking together (GROUP BY bookings.id). You'll get a single result for each booking. The names
of the rooms will be in there, separated by commas. That's from the GROUP_CONCAT command.

Now it is totally easy to display the result:

Code:

foreach ($bookingsresult as $booking)
{
  Booking name: <?=$booking['title']?>
  Rooms booked: <?=$booking['names']?>
}

[eluser]Timothy_[/eluser]
Hello and thank-you,

I had no idea this was possible and your answer has opened up many new opportunities and cleaner coding for the future.

Thanks again