Generate Document

programming
1

Hi!
I´ve done a booking system for a bus travel company. It works great but I need to do a piece of the system more dynamic. This is the database tables:

  • boarding_places: Where all boarding places and boarding times are stored.
  • customers: Where all the customers/travellers are stored.
  • bookings: All saved bookings for all tours.
  • tours: Where all the tours that can be booked are stored.
  • relations: The relation-table that holds booking-ID, customer1-ID, customer2-ID, customer3-ID and tour-ID.

The travel company can generate up to five different travel documents in the system and one document, Borading Places - contains all boarding places where there´s customer. So it could look like follow example:

Boarding Places

Place 1 06:00 PM

  1. Jake Styles
  2. Jamie Oliver

Place 2 06:30 PM

  1. John Great
  2. Lisa Great

Place 3 07:25 PM

  1. Billy Sane
  2. Margret Sane
  3. Lovisa Johnson

You can book up to three travellers on the same booking because I will group some travellers together in other travel documents. This is how the SQL relationship looks like when generating the travel document, done in three steps:

  1. Gets all data for the tour and traveller one.
  2. Gets traveller two data.
  3. Gets traveller three data.

So there is three SQL queries. This is how the relations are built up:

bla bla...WHERE customers.id = relations.customer_id
AND bookings.id = relations.booking_id
AND tours.id = relations.tour_id
AND tours.id = $tid";

and for traveller two: …WHERE customers.id = relations.customer2_id … and so on.

Right now I´ve a static switch-case that prints out all the boarding places and the travellers under that. I want to do this dynamical instead: Get all boarding places from the table ‘boarding_places’, get all travellers - one, two and three - and print them out under correct boarding place. The place may not be printed more than once so I have to group all boarding places. How can this be done in an good dynamical way? Hope you understand my english :drool: