Loop into a query and an array in PHP

Question

I have a table containing some date for dates:

___BillableDatas:

|----------|------------|---------------|
|BIL_Id    | BIL_Date   | BIL_BookingID |
|----------|------------|---------------|
| 1        | 2017-01-01 | 10            |
| 2        | 2017-01-02 | 10            |
| 3        | 2017-01-06 | 11            |
| 4        | 2017-01-07 | 11            |
| 5        | 2017-01-08 | 11            |
| 6        | 2017-01-12 | 14            |
|----------|------------|---------------|

And I have this array giving me all the dates between two dates:

$dates = getDatesFromRange('2017-01-01', '2017-01-15');

Array ( [0] => 2017-01-01 [1] => 2017-01-02 [2] => 2017-01-03 [3] => 2017-01-04 [4] => 2017-01-05 [5] => 2017-01-06 [6] => 2017-01-07 [7] => 2017-01-08 [8] => 2017-01-09 [9] => 2017-01-10 [10] => 2017-01-11 [11] => 2017-01-12 [12] => 2017-01-13 [13] => 2017-01-14 )

I want to be able to loop into this table to get all the days between a period (from 2017-01-01 to 2017-01-15 for example).

For example, the desired output should be the following Json or array:

"datas": {
    "2017-01-01": {
        "bookingId": "10"
    },
    "2017-01-02": {
        "bookingId": "10"
    },
    "2017-01-03": {
        "bookingId": "0"
    },
    "2017-01-04": {
        "bookingId": "0"
    },
    "2017-01-05": {
        "bookingId": "0"
    },
    "2017-01-06": {
        "bookingId": "11"
    },
    "2017-01-07": {
        "bookingId": "11"
    },
    "2017-01-08": {
        "bookingId": "11"
    },
    "2017-01-09": {
        "bookingId": "0"
    },
    "2017-01-10": {
        "bookingId": "0"
    },
    "2017-01-11": {
        "bookingId": "0"
    },
    "2017-01-12": {
        "bookingId": "14"
    },
    "2017-01-13": {
        "bookingId": "0"
    },
    "2017-01-14": {
        "bookingId": "0"
    },
    "2017-01-15": {
        "bookingId": "0"
    }
}

whats `getDatesFromRange()` function? you made this one? just modify it, looks like you could just change the array on how you want it constructed in the structure above, one way would be to just loop the days over from start to end and create the array — Kevin, Apr 11 '17 at 03:39
if you want to do it in the db layer, you could pad the empty dates, here's the idea, http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range there are other topics also just search for it, i'd do it in the application layer though — Kevin, Apr 11 '17 at 03:49

score 1 · Accepted Answer · answered Apr 11 '17 at 05:16

If i am not wrong you wants result including all the date range between two dates regardless it's present in DB table or not

you can loop and compare available date and create your desire array or json.

$new_array = array();
 foreach ($dates as $date){  // date range from "2017-01-01" to "2017-01-15"  
     $avalilable = 0;
        foreach ($bills as $bill){ // records From __BillableDatas table 
            if($bill->BIL_Date == $date)
            {
                $avalilable = 1;
                $new_array[$date] = array("bookingId" =>  $bill->BIL_BookingID);
                break;  
            }   
    } 
    if(!$avalilable)
        $new_array[$date] = array("bookingId" =>  0);
 }
   echo  json_encode($new_array);

Hope this will help

Excellent. Really helpful. – Mittul At TechnoBrave Aug 25 '17 at 09:50 — Mittul At TechnoBrave, Aug 25 '17 at 09:50

score 0 · Answer 2 · answered Apr 11 '17 at 03:16

0

If I understood your question correctly, then below is the way to calculate number of days between 2 dates. No need for loop.Comment on this if you think otherwise

<?php
$from = strtotime('2017-01-01'); 
$to = strtotime("2017-01-15");
$datediff = $to - $from;

echo floor($datediff / (60 * 60 * 24));

?>

answered Apr 11 '17 at 03:16

Pramod Patil

2,704
2
14
20

Ho I can get the same with me function. The problem is the bulid the output by combining the datas info my table. If the date exists into the db take it. – F__M Apr 11 '17 at 03:25

Loop into a query and an array in PHP

___BillableDatas:

2 Answers2