CODEWITHSUNDEEP
phpmysqlarrayssortingdate
Don
Don

Reputation: 89

How to add missing dates at the beginning and end of an array?

I have an SQL query which is querying datas from certain dates. For example between the 01.01.2018 and the 20.01.2018. But if there aren't any datas for certain dates, lets say for example the 01.01.2018, 02.01.2018, 19.01.2018 and 20.01.2018 my SQL Query will not return those dates. There would be just a SQL return for the 03.01.2018 till the 18.01.2018.

And here my problem now comes. Even if there aren't datas at the beginning and ending for my dates, I still want to add those dates to the array and add a 0 for the date. So, the user still knows "Ok, there weren't any datas on the 01.01.2018, 02.01.2018, 19.01.2018 and 20.01.2018.

But how can I do that? Here is an example of the datas I have and which datas I do get by my SQL query

$fromDate = '01.01.2018';
$toDate = '20.01.2018';

returned would be 2 columns with those data:

03.01.2018 - 5
04.01.2018 - 2
06.01.2018 - 8
07.01.2018 - 6
08.01.2018 - 10
09.01.2018 - 1
10.01.2018 - 6
13.01.2018 - 7
14.01.2018 - 2
16.01.2018 - 5
17.01.2018 - 7
18.01.2018 - 1

The data array is containing the dates and the clicks array is containig the number of clicks for the date. The order of both arrays are very important because the e.g. the key 5 is returning the date of the datas array and the amount of clicks for the date from the clicks array.

I already know how I can fill gaps between dates. But I just don't know how I can fill the gaps between the beginning date and the first returned date of the SQL query and the last date and the last returned date of the SQL Query.

This is my code to to fill gaps between the first and last date. For example, because the SQL query didn't return and values for the 05.01.2018, 11.01.2018, 12.01.2018 and 15.01.2018, those dates would be added to the data array and the clicks array (with 0 to the clicks array).

But as I already asked, how can I do this also for the beginning date and the first returned date of the SQL query and the last date and the last returned date of the SQL Query?

        // assume the first element is right
        $fixed_data = array($data[0]);
        $fixed_clicks = array($clicks[0]);

        for ($i = 1; $i < count($data); $i++) {

            echo "Going through " . $data[$i] . "\n";

            // if there is a gap, fill it (add 0s to corresponding array)
            while (date('d.m.Y', strtotime(end($fixed_data) . ' + 1 day')) != $data[$i]) {
                $fixed_data[] = date('d.m.Y', strtotime(end($fixed_data) . ' + 1 day'));
                $fixed_clicks[] = 0;

                echo "Added " . date('d.m.Y', strtotime(end($fixed_data) . ' + 1 day')) . "\n";
            }

            // add the data which exists after gap
            $fixed_data[] = $data[$i];
            $fixed_clicks[] = $clicks[$i];
        }

        foreach ($fixed_data as $key => $var) {
            $fixed_data[$key] = (string)$var;
        }

        foreach ($fixed_clicks as $key => $var) {
            $fixed_clicks[$key] = (string)$var;
        }

        $leadsArray[] = array(array("daten" => $fixed_data), array("anzahl" => $fixed_clicks));
        var_dump($leadsArray);

Upvotes: 0

Views: 112

Answers (1)

iamruskie
iamruskie

Reputation: 766

Why not just add the beginning date to the beginning of the array and last date to the end of the array?

//Add beginning date
array_unshift($data, $fromDate); //'01.01.2018'
array_unshift($clicks, 0);

//Add last date
array_push($data, $toDate); //'20.01.2018'
array_push($clicks, 0);

The loop should take care of the rest.

Upvotes: 1

Related Questions