Group entries by date and time from timestamps

I'm building a booking system and have opted to use timestamps to store the bookings in my mysql database. The user can book 2 or 3 slots consequtively however, for example:

9:00 - 9:30
9:30 - 10:00
10:00 - 10:30

or they can just book single slots.

I want to show the bookings made by a user but group them by the time frame, so for example in the times shown above it should show 9:00 - 10:30 instead of listing 3 items.

Each booking is stored with a bookingFrom and bookingUntil as a timetamp. I have managed to get it working for 2 bookings (eg 9:00-9:30 and 9:30-10:00 to show as just 9:00-10:00) but if there is a middle entry, i'm struggling with how to get this working.

Any ideas? I'm using PHP/mySQL

TABLE SCHEMA

bookingID, userID, bookingFrom (INT - unix timestamp), bookingUntil (INT - unix timestamp)

    CREATE TABLE IF NOT EXISTS `dma_bookings` (
  `bookingID` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `bookingFrom` int(11) NOT NULL,
  `bookingUntil` int(11) NOT NULL,
  `roomID` int(11) NOT NULL,
  PRIMARY KEY (`bookingID`),
  KEY `userID` (`userID`,`bookingFrom`,`bookingUntil`),
  KEY `roomID` (`roomID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;

--
-- Dumping data for table `dma_bookings`
--

INSERT INTO `dma_bookings` (`bookingID`, `userID`, `bookingFrom`, `bookingUntil`, `roomID`) VALUES
(95, 1, 1317362400, 1317364199, 1),
(96, 1, 1317364200, 1317365999, 1),
(97, 1, 1317366000, 1317367799, 1);

Code so far (apologies very messy!):

$bookingArray = $data['bookingInfo'];
        $bookingCleanArray = array();
        $reversedArray = array_reverse($data['bookingInfo']);
        $currentItemIndex=0;
        $nextItemIndex=1;

        foreach($reversedArray as $booking){
            echo "<hr>BookingID: ".$booking['bookingID']."<br/><br/>";

            $bookingFromMinusOne=$booking['bookingFrom']-1;

            if($bookingFromMinusOne==$data['bookingInfo'][$nextItemIndex]['bookingUntil']){

                //The current booking is part of a multiple booking range
                $bookingArray[$nextItemIndex]['bookingUntil']=$booking['bookingUntil'];
                unset($bookingArray[$currentItemIndex]);
            }else{
                echo "Si开发者_开发技巧ngle booking<br/>";
            }

            $currentItemIndex++;
            $nextItemIndex++;

        }

Assuming you've got separate fields for each time you load them into an array then use the first string and count to find the last string.

But yes as rahularyansharma said needs a bit more detail.

Okay... hhmm Wouldn't you just want to grab:

SELECT dma_bookings (bookingID, userID, bookingFrom, bookingUntil, roomID) WHERE userID = 1)

Which should give us:

bookingID, userID, bookingFrom, bookingUntil, roomID

 $bookingArray = array( 95, 1, 1317362400, 1317364199, 1 ),
 array( 96, 1, 1317364200, 1317365999, 1 ),
 array( 97, 1, 1317366000, 1317367799, 1 ),

so if we...

echo  $bookingArray[][2]; we get '1317362400'
$c = count($bookingArray);
echo $bookingArray[$c][3]; we get '1317367799'

Or have I completely misunderstood what you're trying to do?

I think my approach would be to select all the bookings for a given user, but order the results by the start time. I get the feeling your current loop has the right start, you just need a few more conditions to make sure the slots work.

I would start with the first slot, and keep adjusting the end time until I reach a start time that is greater than the current end time. At that point you finish with the current slot, (say push it onto another array), and set your current slot to the new one.

Here is an example, although I don't have the time to test it I think it should work:

$slots = array(
    array('bookingID' => 95, 'userID' => 1, 'bookingFrom' => 1317362400, 'bookingUntil' => 1317364199, 'roomID' => 1), 
    array('bookingID' => 96, 'userID' => 1, 'bookingFrom' => 1317364200, 'bookingUntil' => 1317365999, 'roomID' => 1), 
    array('bookingID' => 97, 'userID' => 1, 'bookingFrom' => 1317366000, 'bookingUntil' => 1317367799, 'roomID' => 1)
);

$condensed_slots = array();
$currentSlot = null;
foreach($slots as $slot){
    if($currentSlot == null) $currentSlot = $slot;
    else {
        if($slot['bookingFrom'] <= $currentSlot['bookingUntil']){
            $currentSlot['bookingUntil'] = $slot['bookingFrom'];
        }
        else {
            $condensed_slots[] = $currentSlot;
            $currentSlot = $slot;
        }
    }
}

So after that $condensed_slots should contain a reduced set (if possible) of slots, although the bookingID may not necessarily reflect the original from and until timestamps (obviously).