Create a way to search a date range from JSON datetime

Question

I've been battling this issue for the past few days, and I am stumped. Time to take it to you all.

My JSON data looks like this:

{
  "apiVersion": "0.1",
  "data": {
    "offset": 0,
    "limit": 50,
    "count": 50,
    "total": 783,
    "reports": [
      {
        "type": "ROOM_CHARGE",
        "data": {
          "datetime": "2014-11-04T14:00:27-08:00",
          "originator": "639a",
          "roomNumber": "639",
          "chargeItem": "Premium Services",
          "chargeAmountCents": 495
        }
      },
      {
        "type": "STB_EVENT_TIMED",
        "data": {
          "datetime": "2014-11-04T12:58:38-08:00",
          "originator": "249b",
          "roomNumber": "249",
          "eventItem": "Watched movie 31008",
          "duration": "P0Y0M0DT1H12M18.907S"
        }
      },

I can display the data just fine, but I'm trying to create a way to search a date range from the datetime data in the JSON.

Here is what I have tried:

if(isset($_GET['submit_search'])){ 
  $room_number = $_GET['id'];
  $first_date = new DateTime($_GET['first_date']);
  $last_date = new DateTime($_GET['last_date']);
}

<?php foreach($output1['data']['reports'] as $reporting): ?>

    <!-- inside the date range -->
    <?php $report_date = new DateTime($reporting['data']['datetime']); ?>
    <?php if($first_date >= $report_date && $last_date <= $report_date): ?>

        <?php if($reporting['type'] == "ROOM_CHARGE"): ?>
          <tr>
          <td><?php echo 'Room Charge'; ?></td>
          <td><?php echo $report_date; ?></td>
          <td><?php echo $report_date->format('Y-m-d'); ?></td>
          <td><?php echo $report_date->format('h:i:s A'); ?></td>
          <td><?php echo ($reporting['data']['roomNumber']) ?>  </td>
          <td><?php echo ($reporting['data']['originator']) ?>  </td>                            
          <td><?php echo ($reporting['data']['chargeItem']) ?></td>
          <td><?php echo number_format(($reporting['data']['chargeAmountCents'])/100, 2, '.', ',') ?></td>
          <td>-</td>              
          </tr>
        <?php elseif($reporting['type'] == "STB_EVENT_TIMED"): ?>
          <tr>
          <td><?php echo 'Event'; ?></td>
          <td><?php echo $report_date->format('Y-m-d'); ?></td>
          <td><?php echo $report_date->format('h:i:s A'); ?></td>
          <td><?php echo ($reporting['data']['roomNumber'])?>  </td>
          <td><?php echo ($reporting['data']['originator']) ?>  </td>                     
          <td><?php echo ($reporting['data']['eventItem']) ?></td>
          <td>-</td>   
          <td><?php echo substr($reporting['data']['duration'], -10, 2) ?>:<?php echo substr($reporting['data']['duration'], -7, 2) ?>:<?php echo substr($reporting['data']['duration'], -4, 2) ?></td>                
          </tr>
        <?php endif; ?>
    <?php endif; ?>
<?php endforeach; ?>

the $first_date and $last_date are set to whatever a user enters in a form, in the format 2014-11-05 xxxx-xx-xx

ANY help would be appreciated. I can provide more code, or anything else you need. Thank you for checking out my post.

Answer 1

No need to substring each date, you could use DateTime classes in this case, then you could compare them inside the if conditions:

Simple example:

<?php

$json_string = '{"apiVersion": "0.1","data": {"offset": 0,"limit": 50,"count": 50,"total": 783,"reports": [{"type": "ROOM_CHARGE","data": {"datetime": "2014-11-04T14:00:27-    08:00","originator": "639a","roomNumber": "639","chargeItem": "Premium Services","chargeAmountCents": 495}},{"type": "STB_EVENT_TIMED","data": {"datetime": "2014-11-04T12:58:38-    08:00","originator": "249b","roomNumber": "249","eventItem": "Watched movie 31008","duration": "P0Y0M0DT1H12M18.907S"}}]}}';
$first_date = new DateTime($_GET['first_date'] . '-08:00'); // post/get user input values
$last_date = new DateTime($_GET['last_date'] . '-08:00');
$last_date->setTime(23, 59, 59);

$output1 = json_decode($json_string, true);

?>
<?php foreach($output1['data']['reports'] as $reporting): ?>
    <!-- inside the date range -->
    <?php $report_date = new DateTime($reporting['data']['datetime']); ?>
    <?php if($report_date >= $first_date && $report_date <= $last_date): ?>
        <?php if($reporting['type'] == "ROOM_CHARGE"): ?>
            <tr>
                <td><?php echo 'Room Charge'; ?></td>
                <td><?php echo $report_date->format('Y-m-d'); ?></td>
                <td><?php echo $report_date->format('h:i:s A'); ?></td>
                <td><?php echo ($reporting['data']['roomNumber']) ?> </td>
                <td><?php echo ($reporting['data']['originator']) ?> </td>
                <td><?php echo ($reporting['data']['chargeItem']) ?></td>
                <td><?php echo number_format(($reporting['data']['chargeAmountCents'])/100, 2, '.', ',') ?></td>
                <td>-</td>
            </tr>
        <?php elseif($reporting['type'] == "STB_EVENT_TIMED"): ?>
            <tr>
                <td><?php echo 'Event'; ?></td>
                <td><?php echo $report_date->format('Y-m-d'); ?></td>
                <td><?php echo $report_date->format('h:i:s A'); ?></td>
                <td><?php echo ($reporting['data']['roomNumber'])?> </td>
                <td><?php echo ($reporting['data']['originator']) ?> </td>
                <td><?php echo ($reporting['data']['eventItem']) ?></td>
                <td>-</td>
                <td><?php echo substr($reporting['data']['duration'], -10, 2) ?>:<?php echo substr($reporting['data']['duration'], -7, 2) ?>:<?php echo substr($reporting['data']['duration'],     -4, 2) ?></td>
            </tr>
        <?php endif; ?>
    <?php endif; ?>
<?php endforeach; ?>
</table>
<?php endif; ?>

Supplemental info:

Once its a DateTime object, you could now use its method:

<td><?php echo $report_date->format('Y-m-d'); ?></td>
<td><?php echo $report_date->format('h:i:s A'); ?></td>