So at work I had to make graphs showing a lot of different data, that is GROUP BY‘d on DATE(created_time) from MySQL The problem arise when there hasn’t been any action during a certain day, there will be a gap and it will make the graphs look might strange. My first solution was to just do the number table trick in the INSERT of the data, but this started to pose problems when I wanted to do more advanced things and aggregate some of my statistical data without storing it in the format that my graphs and number table trick can use it. So instead of copy paste the number table trick into every SELECT query that needed it, I wanted to break it out into a proper function.

<?php

namespace lib\Date;

class DateFiller
{
    public static function fillDates( array $inputArray = array(), $dateKey = 'date_time', $sort = true, $format = 'Y-m-d' )
    {
        if( $sort )
        {
            self::sortByDate($inputArray, $dateKey);
        }

        $firstRow = reset($inputArray);
        $lastRow  = end($inputArray);      


        return self::fillDateRange( $inputArray, $firstRow[$dateKey], $lastRow[$dateKey],$dateKey, false, $format );
    }

    public static function fillDateRange( array $inputArray, $from, $until, $dateKey = 'date_time', $sort = true, $format = 'Y-m-d' )
    {

        if($from =='' || $until == '')
        {
            die(__FILE__. ' $from $until can not be empty');
        }

        if( $sort )
        {
            self::sortByDate($inputArray, $dateKey);
        }

        $begin    = new \DateTime($from);
        $end      = new \DateTime($until);
        $end->modify( '+1 day' );
        $interval = new \DateInterval('P1D');
        $period   = new \DatePeriod($begin,$interval,$end);

        $rowStruct = array_combine( array_keys( reset( $inputArray ) ), array_map( function($a){
            return ( is_numeric( $a ) ? 0 : '' );

        }, reset( $inputArray )) );

        $i = 0;
        foreach ( $period as $day )
        {
            if( !isset( $inputArray[$i] ) || $inputArray[$i][$dateKey] != $day->format( $format ) )
            {
                    array_splice($inputArray, $i, 0, 0);
                    $inputArray[$i] = $rowStruct;
                    $inputArray[$i][$dateKey] = $day->format( $format );
            }

            $i++;
        }
        return $inputArray;
    }



    public static function sortByDate( array &$inputArray = array(), $dateKey = 'date_time')
    {
        usort($inputArray, function($a, $b) use ($dateKey){
            return strToTime( $a[$dateKey] ) - strToTime( $b[$dateKey] );

        });
    }






}

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>