Ordering Database Records

kbc1

I need help with the ordering of records by specific fields within those records.

I have a table of Flights for a passenger booking. Amongst other fields the important fields of each record would be:

BookingID
PassengerID
OutboundFlightDate
OutboundFlightTime
OutboundFlightNumber

Now each passenger on a booking can have multiple flights, some on the same day some on different days. What I need to do is find a way to pull out all the flights for a passenger and display them in order of:

1) OutboundFlightDate
2) OutboundFlightTime

Therefore if two or more flights for a passenger have the same OutboundFlightDate, it resorts to using the OutboundFlightTime as the primary display order value. The OutboundFlightDate is always the primary order value to begin with.

I assume I need to SELECT all of this information from the database first (as there could be many rows of data for a single passenger) and store it into an array and then somehow output the array contents into my HTML page in the required order.

Can anyone offer any advice or sample code on how I can achieve this fuictionality?

Unfortunately I cannot change the database structure.

Thanks in advance.

laserlight

It looks quite routine to me. Just SELECT ... WHERE PassengerID=:id ORDER BY OutboundFlightDate, OutboundFlightTime and you're done.

kbc1

Hi Laserlight

I guess I forgot to mention the reason why I can't do it that way which would have been helpful in the first place!

The record also has inbound flight fields as such:

BookingID
PassengerID
OutboundFlightDate
OutboundFlightTime
OutboundFlightNumber
InboundFlightDate
InboundFlightTime
InboundFlightNumber

I need to be able to order by the earliest date/time combination of either outbound or inbound for each record of each passenger.

So essentially I need to see a list of flights assigned to the passenger in order as the flights actually happen in real-time. If the dates of any flights are on the same date, then we use the flight time to order them further. Each passenger can have multiple flights on the same booking.

I hope that helps clear up why I can just use ORDER BY in my query and why I believe I have to somehow store the results into a large array and then pull out and display them in the correct order form the array!

Thanks again for any help.

kbc1

laserlight - if its any help, i have pulled my data form the database for one passenger and stored the infomration into an array. You can see there are 3 records, each with 2 flights each in the array. I need to now pull out and order the flight date in order of real-time flights:

Array
(
    [15860] => Array
        (
            [flightid] => 15860
            [bookingpassengerid] => 8156
            [outflightnumber] => LY318
            [outdepartdate] => 2012-04-17
            [outdeparttime] => 22:30:00
            [indeparturedate] => 2012-04-23
            [indeparturetime] => 17:25:00
            [inflightnumber] => LY317
        )

[15858] => Array
    (
        [flightid] => 15858
        [bookingpassengerid] => 8156
        [outflightnumber] => BA1403
        [outdepartdate] => 2012-04-17
        [outdeparttime] => 17:20:00
        [indeparturedate] => 2012-04-24
        [indeparturetime] => 11:10:00
        [inflightnumber] => BA1388
    )

[15764] => Array
    (
        [flightid] => 15764
        [bookingpassengerid] => 8156
        [outflightnumber] => LY401
        [outdepartdate] => 2012-04-18
        [outdeparttime] => 07:30:00
        [indeparturedate] => 2012-04-23
        [indeparturetime] => 14:15:00
        [inflightnumber] => LY404
    )

)

laserlight

kbc1 wrote:
So essentially I need to see a list of flights assigned to the passenger in order as the flights actually happen in real-time. If the dates of any flights are on the same date, then we use the flight time to order them further. Each passenger can have multiple flights on the same booking.

I hope that helps clear up why I can just use ORDER BY in my query and why I believe I have to somehow store the results into a large array and then pull out and display them in the correct order form the array!

You should still be able to use ORDER BY, except that it should be the MIN of the concatenation of the inbound flight date and time, and the concatenation of the outbound flight date and time.

kbc1 wrote:
I need to now pull out and order the flight date in order of real-time flights

If you want to do the sorting in PHP, then consider [man]uasort[/man].

bradgrafelman

Is there a reason why the dates and times were separated into two separate columns? What good does knowing one but not the other do for you? If you never want to know things like "Your flight leaves some time on August 2nd, 2012" or "Your flight leaves some day at 12:00 PM", then it doesn't really make sense to split the two pieces of related data into separate columns. (And even if you did want to know such unhelpful information, note it would still be easily done if the dates and times were stored in a single DATETIME column.)

If they weren't split, for example, you'd simply do the MIN() of both "datetime" columns in the ORDER BY.

Weedpacket

What is the entity actually being modelled in this table? (It's not Flights because, as already noted, each record contains information about two flights - presumably one after the other).

What is it that has an outbound departure on one flight and an inbound departure on another flight - but no arrivals?

Does this entity represent time spent by a person between flights? Does this mean that information about one flight (its departure time and its flight number) is duplicated in two records (one where it is outbound and one where it is inbound)? Is it possible for there to be two consecutive outbound departures without an intervening inbound departure?