PHP get all dates in a range from sql

Question

Complete newb and total novice, so apologies in advance...

Say I have a table in SQL with three columns: name; datein; dateout. I have 2 records in the table.

For each individual record, I want to echo the dates from 'datein' to the day before 'dateout'.

So for example...

Record 1 in the db is JohnDoe, 2017-03-27, 2017-03-30

Record 2 in the db is JaneDoe, 2017-04-10, 2017-04-12

I want to echo the results:

2017-03-27

2017-03-28

2017-03-29

2017-04-10

2017-04-11

Searched but couldn't quite find something that does this... Hope someone can help :)

[This](http://stackoverflow.com/questions/4312439/php-return-all-dates-between-two-dates-in-an-array) may help — Carl Binalla, Mar 27 '17 at 03:23
http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — Louis Loudog Trottier, Mar 27 '17 at 03:26
Possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — But those new buttons though.., Mar 27 '17 at 04:34

score 1 · Answer 1 · answered Mar 27 '17 at 03:30

1

While there probably is a pure MySQL solution, here's a PHP alternative using the DateTime class. Set $start_date and $end_date to the values from your database, and let it run! The loop automatically increments the day by 1 until you reach your final date.

$start_date = "2017-03-27";
$end_date = "2017-03-30";

$start = new DateTime($start_date);
$end = new DateTime($end_date);

while ($start < $end) {
    echo $start->format("Y-m-d")."\n";
    $start->modify("+1 day");
}

Output:

2017-03-27
2017-03-28
2017-03-29

Live demo

answered Mar 27 '17 at 03:30

Qirel

25,449
7
45
62

Thanks Qirel for the quick answer, much appreciated. Just one quick question... how would I get it to repeat the output for each record in the db? – Ryan Watson Mar 27 '17 at 03:39
You put it in the loop where you fetch the rows. `while (fetch rows) { /* code above */ }` – Qirel Mar 27 '17 at 03:42

Gert · Answer 2 · 2017-03-27T04:32:59.360

you just need to use 2 loops. 1 to fetch your result the other to check the condition and echo. first loop this is if you use PDO then the code is correct ,otherwise if you use mysql old way then it will be "while ($row = mysql_fetch_assoc($result)) "

$i=0;
    while ($row = $query->fetch(PDO::FETCH_ASSOC)){
        $date1=$row[$i]['datein'];
        $date2=$row[$i]['dateout'];
       // we check that entry date is not bigger then the exit day
        IF ($date1 > $date2){
            echo 'error in date entry date bigger then exit date';
            //continue will bypass the next loop and go to the next row
            continue; 
            }   
           while ($date1 != $date2){
           echo $date1;
           $date1=date('Y-m-D',strtotime($date1 . ' + 1 day);
           }
$i++;    
}

hope it can help you

PHP get all dates in a range from sql

2 Answers2