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
– Kevin, Commented Apr 11, 2017 at 3:39
if you want to do it in the db layer, you could pad the empty dates, here's the idea, stackoverflow.com/questions/3538858/… there are other topics also just search for it, i'd do it in the application layer though — Kevin
– Kevin, Commented Apr 11, 2017 at 3:49

Meera Tank · Accepted Answer · 2017-04-11 05:16:10Z

1

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

answered Apr 11, 2017 at 5:16

Meera Tank

7196 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mittul At TechnoBrave Over a year ago

Excellent. Really helpful.

Pramod Patil · Accepted Answer · 2017-04-11 03:16:33Z

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, 2017 at 3:16

Pramod Patil

2,7632 gold badges16 silver badges20 bronze badges

1 Comment

F__M Over a year ago

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.

Collectives™ on Stack Overflow

Loop into a query and an array in PHP

___BillableDatas:

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

___BillableDatas:

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related