Filter Array or XML with Time between Start and End Time

Question

I have an API call. The call returns back XML, I convert to XML into an array and then use json_encode to send back the response in a jsonp call. What I am doing is working but the API doesn't allow me to filter by a status, filter by start and end time and also make sure that the room is available.

It gets crazy with several jsponp calls. Again, what I have is working but I need to figure out a way to filter one more time with my results. I have returned the correct status and the correct date, but I still have to filter out times. If the time is currently 10:29am I don't want to show booked rooms that start at 10:30 am because it isn't booked until 10:30am. So, if my current time is 10:29 am I need to filter through my events in my array and only show events that have a start and end time that have the current time in between.

A booking from 10-11 am should stay in my array but a booking from 2-4 pm shouldn't show in my array. As I said the array is first a returned XML document, then an array, then JSON so technically I can use whatever works to filter. PHP to filter in XML, PHP to filter the array. Even Javascript onces that PHP file is ready by my ajax call.

Here is my array before I turn it into JSON and return. The times I filter should be TimeEventStart and TimeEventEnd. I can either use current Javascript time or PHP date function time. I can either filter the array or use something like XMLPath to filter. Not sure what is the most efficient and would work best.

$axml = Array{
   "Bookings":{
      "Data":[
         {
            "BookingDate":[
               "2014-05-09T00:00:00"
            ],
            "RoomDescription":[
               "Room06"
            ],
            "TimeEventStart":[
               "2014-05-09T14:00:00"
            ],
            "TimeEventEnd":[
               "2014-05-09T14:30:00"
            ],
            "EventName":[
               "Jake Long"
            ],
            "SetupTypeDescription":[
               "(none)"
            ],
            "ReservationID":[
               "137"
            ],
            "OpenTime":[
               "1900-01-01T00:00:00"
            ],
            "CloseTime":[
               "1900-01-01T00:00:00"
            ],
            "EventTypeDescription":[
               [

               ]
            ],
            "BookingID":[
               "709"
            ],
            "TimeBookingStart":[
               "2014-05-09T14:00:00"
            ],
            "TimeBookingEnd":[
               "2014-05-09T14:30:00"
            ],
            "GMTStartTime":[
               "2014-05-09T18:00:00"
            ],
            "GMTEndTime":[
               "2014-05-09T18:30:00"
            ],
            "TimeZone":[
               "ET"
            ],
            "RoomCode":[
               "R06"
            ],
            "Room":[
               "Room 6"
            ],
            "RoomID":[
               "34"
            ],
            "StatusID":[
               "3"
            ],
            "EventTypeID":[
               "0"
            ],
            "DateAdded":[
               "2014-05-09T13:47:29.087"
            ],
            "DateChanged":[
               "2014-05-09T13:47:29.087"
            ],
            "ChangedBy":[
               "Admin"
            ]
         },
         {
            "BookingDate":[
               "2014-05-09T00:00:00"
            ],
            "RoomDescription":[
               "Room06"
            ],
            "TimeEventStart":[
               "2014-05-09T18:00:00"
            ],
            "TimeEventEnd":[
               "2014-05-09T20:00:00"
            ],
            "EventName":[
               "Rob Brown"
            ],
            "SetupTypeDescription":[
               "(none)"
            ],
            "ReservationID":[
               "142"
            ],
            "OpenTime":[
               "1900-01-01T00:00:00"
            ],
            "CloseTime":[
               "1900-01-01T00:00:00"
            ],
            "EventTypeDescription":[
               [

               ]
            ],
            "BookingID":[
               "714"
            ],
            "TimeBookingStart":[
               "2014-05-09T18:00:00"
            ],
            "TimeBookingEnd":[
               "2014-05-09T20:00:00"
            ],
            "GMTStartTime":[
               "2014-05-09T22:00:00"
            ],
            "GMTEndTime":[
               "2014-05-10T00:00:00"
            ],
            "TimeZone":[
               "ET"
            ],
            "RoomCode":[
               "R06"
            ],
            "Room":[
               "Room 6"
            ],
            "RoomID":[
               "34"
            ],
            "StatusID":[
               "3"
            ],
            "EventTypeID":[
               "0"
            ],
            "DateAdded":[
               "2014-05-09T14:58:55.71"
            ],
            "DateChanged":[
               "2014-05-09T14:58:55.71"
            ],
            "ChangedBy":[
               "Admin"
            ]
         },
         {
            "BookingDate":[
               "2014-05-09T00:00:00"
            ],
            "StartBookingDate":[
               "2014-05-09T00:00:00"
            ],
            "RoomDescription":[
               "Room06"
            ],
            "TimeEventStart":[
               "2014-05-09T11:00:00"
            ],
            "TimeEventEnd":[
               "2014-05-09T12:00:00"
            ],
            "EventName":[
               "Jimmy James"
            ],
            "SetupTypeDescription":[
               "(none)"
            ],
            "ReservationID":[
               "141"
            ],
            "OpenTime":[
               "1900-01-01T00:00:00"
            ],
            "CloseTime":[
               "1900-01-01T00:00:00"
            ],
            "EventTypeDescription":[
               [

               ]
            ],
            "BookingID":[
               "713"
            ],
            "TimeBookingStart":[
               "2014-05-09T11:00:00"
            ],
            "TimeBookingEnd":[
               "2014-05-09T12:00:00"
            ],
            "GMTStartTime":[
               "2014-05-09T15:00:00"
            ],
            "GMTEndTime":[
               "2014-05-09T16:00:00"
            ],
            "TimeZone":[
               "ET"
            ],
            "RoomCode":[
               "R06"
            ],
            "Room":[
               "Room 6"
            ],
            "RoomID":[
               "34"
            ],
            "StatusID":[
               "1"
            ],
            "EventTypeID":[
               "0"
            ],
            "DateAdded":[
               "2014-05-09T14:58:15.17"
            ],
            "DateChanged":[
               "2014-05-09T14:58:15.17"
            ],
            "ChangedBy":[
               "Admin"
            ]
         }
      ]
   }
}
;

My array is being returned from a SOAP call in XML. I am getting the XML back and using

$sxml = simplexml_load_string($xml)

Then I am using a function called xmlToArray to put it into the array posted.

$axml = xmlToArray($sxml);

Here is the XML before I convert to an Array and then JSON.

<?xml version="1.0" encoding="utf-8"?><Bookings>
  <Data>
    <BookingDate>2014-05-13T00:00:00</BookingDate>
    <StartBookingDate>2014-05-13T00:00:00</StartBookingDate>
    <RoomDescription>Room 06</RoomDescription>
    <TimeEventStart>2014-05-13T10:00:00</TimeEventStart>
    <TimeEventEnd>2014-05-13T10:30:00</TimeEventEnd>
    <EventName>Jake Long</EventName>
    <ReservationID>159</ReservationID>
    <ClosedAllDay>false</ClosedAllDay>
    <OpenTime>1900-01-01T00:00:00</OpenTime>
    <CloseTime>1900-01-01T00:00:00</CloseTime>
    <BookingID>731</BookingID>
    <TimeBookingStart>2014-05-13T10:00:00</TimeBookingStart>
    <TimeBookingEnd>2014-05-13T10:30:00</TimeBookingEnd>
    <GMTStartTime>2014-05-13T14:00:00</GMTStartTime>
    <GMTEndTime>2014-05-13T14:30:00</GMTEndTime>
    <RoomCode>R09</RoomCode>
    <Room>Room 9</Room>
    <RoomID>37</RoomID>
    <StatusID>3</StatusID>
    <DateAdded>2014-05-13T08:38:22.36</DateAdded>
    <DateChanged>2014-05-13T08:38:22.36</DateChanged>
    </Data>
</Bookings>

Answer 1

You can filter the XML using Xpath. Xpath 1.0 can not compare strings, but you can register php functions, too.

So write the condition in PHP:

function isBetween($current, $min, $max) {
  $current = strtotime($current.'Z');
  $min = strtotime($min.'Z');
  $max = strtotime($max.'Z');
  return $current >= $min && $current <= $max;
}

Create an DOM document and load the XML. Create the Xpath instance register the PHP namespace and the function.

$dom = new DOMDocument();
$dom->loadXml($xml);
$xpath = new DOMXpath($dom);
$xpath->registerNamespace("php", "http://php.net/xpath");
$xpath->registerPhpFunctions('isBetween');

Define an Xpath expression that uses the function to filter the Data element nodes:

// use gmtime() for current time
$now = strtotime('2014-05-13T14:00:00Z');
$expression = sprintf(
  '/Bookings/Data[
    php:function("isBetween", "%s", string(GMTStartTime), string(GMTEndTime))
   ]',
  gmdate('Y-m-d\\TH:i:s', $now)
);

Fetch the Data element nodes using the Xpath expression:

$result = [];
foreach($xpath->evaluate($expression) as $dataNode) {
  $record = [];
  // convert the matching data into a record array
  foreach ($xpath->evaluate('*', $dataNode) as $childNode) {
    $record[$childNode->localName] = $childNode->nodeValue;
  }
  $result[] = $record;
}
var_dump($result);

Demo: https://eval.in/151732

Answer 2

Okay, so I wrote some PHP functions, to filter out what you demand.

To compare dates in php it is easiest to convert them to Unix time. That's what the first function does. Unix time is the time elapsed in seconds since January 1st, 1970. Converting them gives the advantage you can simply use < and > operators to compare two times.

The next function (nowBetween), looks if the current time is between the two times given through ($start and $end).

Then I took your JSON string (as we didn't really get to see the XML source when I wrote this post) and converted it to PHP arrays. We can be sure it are arrays, as the second parameter in json_decode is set to true.

Last but not least, I loop through all bookings in the "data" section of your JSON content, read the GMTStart and GMTEnd times and check if the current time is between them using our function. If not so, then I remove the booking from the $bookings array.

The result is only the bookings with the correct times stay. My output now was empty, but that's because your bookings date from 2014-05-09... When I adapted the time it worked.

I hope this answer suits your needs.

function convertDateTime($datetime) {
    //FORMAT IS date("YYYY-MM-DDThh:mm:ss")
    $datetime = str_replace("T"," ",$datetime);
    return date("U",strtotime($datetime));
}
function nowBetween($start,$end) {
    $now = gmdate("U");
    $start = convertDateTime($start);
    $end = convertDateTime($end);
    if ($start <= $now && $end > $now) 
    { 
        return true;
    }
    else
    {
        return false;
    }
}

$bookings = '{"Bookings":{"Data":[{"BookingDate":["2014-05-09T00:00:00"],"RoomDescription":["Room06"],"TimeEventStart":["2014-05-09T14:00:00"],"TimeEventEnd":["2014-05-09T14:30:00"],"EventName":["Jake Long"],"SetupTypeDescription":["(none)"],"ReservationID":["137"],"OpenTime":["1900-01-01T00:00:00"],"CloseTime":["1900-01-01T00:00:00"],"EventTypeDescription":[[]],"BookingID":["709"],"TimeBookingStart":["2014-05-09T14:00:00"],"TimeBookingEnd":["2014-05-09T14:30:00"],"GMTStartTime":["2014-05-09T18:00:00"],"GMTEndTime":["2014-05-09T18:30:00"],"TimeZone":["ET"],"RoomCode":["R06"],"Room":["Room 6"],"RoomID":["34"],"StatusID":["3"],"EventTypeID":["0"],"DateAdded":["2014-05-09T13:47:29.087"],"DateChanged":["2014-05-09T13:47:29.087"],"ChangedBy":["Admin"]},{"BookingDate":["2014-05-09T00:00:00"],"RoomDescription":["Room06"],"TimeEventStart":["2014-05-09T18:00:00"],"TimeEventEnd":["2014-05-09T20:00:00"],"EventName":["Rob Brown"],"SetupTypeDescription":["(none)"],"ReservationID":["142"],"OpenTime":["1900-01-01T00:00:00"],"CloseTime":["1900-01-01T00:00:00"],"EventTypeDescription":[[]],"BookingID":["714"],"TimeBookingStart":["2014-05-09T18:00:00"],"TimeBookingEnd":["2014-05-09T20:00:00"],"GMTStartTime":["2014-05-09T22:00:00"],"GMTEndTime":["2014-05-10T00:00:00"],"TimeZone":["ET"],"RoomCode":["R06"],"Room":["Room 6"],"RoomID":["34"],"StatusID":["3"],"EventTypeID":["0"],"DateAdded":["2014-05-09T14:58:55.71"],"DateChanged":["2014-05-09T14:58:55.71"],"ChangedBy":["Admin"]},{"BookingDate":["2014-05-9T00:00:00"],"StartBookingDate":["2014-05-09T00:00:00"],"RoomDescription":["Room06"],"TimeEventStart":["2014-05-09T11:00:00"],"TimeEventEnd":["2014-05-09T12:00:00"],"EventName":["Jimmy James"],"SetupTypeDescription":["(none)"],"ReservationID":["141"],"OpenTime":["1900-01-01T00:00:00"],"CloseTime":["1900-01-01T00:00:00"],"EventTypeDescription":[[]],"BookingID":["713"],"TimeBookingStart":["2014-05-09T11:00:00"],"TimeBookingEnd":["2014-05-09T12:00:00"],"GMTStartTime":["2014-05-09T15:00:00"],"GMTEndTime":["2014-05-09T16:00:00"],"TimeZone":["ET"],"RoomCode":["R06"],"Room":["Room 6"],"RoomID":["34"],"StatusID":["1"],"EventTypeID":["0"],"DateAdded":["2014-05-09T14:58:15.17"],"DateChanged":["2014-05-09T14:58:15.17"],"ChangedBy":["Admin"]}]}}';
$bookings = json_decode($bookings,true);


foreach ($bookings["Bookings"]["Data"] as $index=>$arrContent) 
{
    if (!nowBetween($arrContent["GMTStartTime"][0],$arrContent["GMTEndTime"][0])) 
    {
        unset($bookings["Bookings"]["Data"][$index]);
    }
}