Increment days in PHP MySQL date

Question

I have a table in MySQL with a date field (called NDate) which contains standard date values ("2017-04-17","2017-04-18", etc.).

Through PHP webpage, I am trying to take the system date (say today is 2017-04-17), and then pull all rows from the above table where NDate="2017-04-17". No issues till here.

I have a requirement to increment the day (starting today and going on for next 10 days - i.e. 2017-04-17 to 2017-04-26), and for each day report entries under a different heading like "Entries for 2017-04-17" which will list all rows having NDate 2017-04-17, "Entries for 2017-04-18" which will list all rows having NDate 2017-04-18.

I was trying to use a for loop with PHP date_modify function to increment the days one by one, but it is not showing any results.
Here are the selected pieces of code:

date_default_timezone_set('US/Eastern');
$datev = date("Y-m-d");
for ($x = 0; $x <= 10; $x++) 
{
    $datev=date_modify($date,"+$x days");
    echo "before date format<br>";      // echo statement 1
    echo "date is: $datev <br>";        // echo statement 2
    $sql = "SELECT * FROM tablename where Ndate='$datev'";
    echo "before result<br>";           // echo statement 3
    ...
    ...
    ...
}

Output on webpage shows only statement 1. But echo stats 2 and 3 are not printed.

gaganshera · Answer 1 · 2017-04-17T19:26:03.267

1

Try this:

$start = strtotime(date('Y-m-d'));
$end = strtotime(date('Y-m-d', strtotime('+10 days')));
while($start <= $end)
{
    $date = date('Y-m-d', $start);
    //use $date to do stuff
    //SELECT * FROM tablename where Ndate='$date'
    $start = strtotime("+1 day", $start);
}

edited Apr 17 '17 at 19:26

answered Apr 17 '17 at 19:12

gaganshera

2,629
1
14
21

Hello @gaganshera, Thanks for the prompt response. Can you also shed some light on what should be the exact SQL statement: "$sql = "SELECT * FROM tablename where Ndate='$start";" is not returning any results... Echoing $start (when its value is "2017-04-17" is publishing a value of "1492412400" – Aquaholic Apr 17 '17 at 19:23
Do or do not. There is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Apr 17 '17 at 19:25
@user6337701 1492412400 is the [unix timestamp](https://en.wikipedia.org/wiki/Unix_time) and represents the date in numeric form. Also, please check my comment in the answer for the query – gaganshera Apr 17 '17 at 19:28
Thanks @gaganshera. While Ozgur 's code worked in first shot, your's too did the work after the edit. +1 to you too – Aquaholic Apr 17 '17 at 19:42

Оzgur · Accepted Answer · 2017-04-17T19:31:59.680

1

You can increment days using strtotime function as a parameter to date function.

For 10 days, you can use for loop, to build an array of days. Then iterate over it, to execute queries you need.

$today = date('Y-m-d');
$dates=array($today);
for($i=1;$i<10;$i++) {
    $NewDate=date('Y-m-d', strtotime("+".$i." days"));
    $dates[]=$NewDate;
}

foreach($dates as $dt) {
    // sql stuff here
    echo "date is: $dt <br>";
    $sql = "SELECT * FROM tablename where Ndate='$dt'";
     echo "before result<br>";
    // .....
}

This code should work for your case. If any problems, just let me know.

edited Apr 17 '17 at 19:31

answered Apr 17 '17 at 19:15

Оzgur

432
2
10

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Apr 17 '17 at 19:26
@JayBlanchard you are right again I added information for the code example – Оzgur Apr 17 '17 at 19:32
Should I review your other answers? Or will you take the time to go back and add any needed information/explanation? – Jay Blanchard Apr 17 '17 at 19:33
Hello @Ozgur. Thanks a ton. This worked perfectly for my question. "@Jay Blanchard", Thanks for pointing to another similar question. However, please note that compared to the other question which was solely focused on PHP, this one combines the code for both PHP and its usage for SQL, and that's why the title says "Increment days in PHP MySQL date". Thanks again :-) Please let me know if any editing is required. – Aquaholic Apr 17 '17 at 19:35
@user6337701 can I get an upvote for it? – Оzgur Apr 17 '17 at 19:37
@Ozgur - Definitely, I've already given a upvote for this but there was some prompt message about 15 upvotes and no change in public viewing (or something similar)... Its a thumbsup ..You saved my day! :-) – Aquaholic Apr 17 '17 at 19:40
@JayBlanchard I will try adding for them too. It is time taking, but worth it. Thanks – Оzgur Apr 17 '17 at 19:41
@user6337701 you can 'accept as answer' or maybe you can't. Anyway.. – Оzgur Apr 17 '17 at 19:42
@Ozgur, Found it....and selected the "Accept as answer" :-) – Aquaholic Apr 17 '17 at 19:49

Increment days in PHP MySQL date

2 Answers2