CODEWITHSUNDEEP
phpmysqldatetime
william
william

Reputation: 117

adding time and date to datetime in mysql using php?

I'm trying to add date and time to a datetime field in mysql database using PHP.

My datetime in mysql database is stored like this:

2015-01-20 05:10:17

I tried to add 5 days to that field like this:

$Date = date();
$end_date = date("Y, n, j, G, i, s", strtotime($Date. ' + 5 days'));

UPDATE myTable SET end_date='$end_date' WHERE randKey='$id'

but when I look at the database for updates I see the end_date field like this:

0000-00-00 00:00:00

which means it has been updated but everything has been set to 0 for some reason!

could someone please advise on this issue?

Thanks in advance.

Upvotes: 0

Views: 344

Answers (3)

Fenistil
Fenistil

Reputation: 3801

First, the code you provided doesn't add 5 days to the value of the field, but five days to today's date. See, if your field contains 2015-01-01 and as you wrote, you would like to add 5 days to this field, you shouldn't add five days in PHP to date() because it will be 2015-01-25 instead of 2015-01-06. So if you want to add 5 days to the field value use this:

UPDATE myTable SET end_date=DATE_ADD(end_date, INTERVAL 5 DAY) WHERE randKey='$id';

But if you want to add five days after today, use this:

UPDATE myTable SET end_date=DATE_ADD(CURDATE(), INTERVAL 5 DAY) WHERE randKey='$id';

If you care for the time also, not just the date, use NOW() instead of CURDATE().

I hope it helps.

Upvotes: 1

Alec Deitloff
Alec Deitloff

Reputation: 447

Instead of trying to add the five days to the current date in one string, using the two parameters to strtotime would be much easier, as so:

$Date = date();
$end_date = date("Y, n, j, G, i, s", strtotime('+5 days', $Date));

The second parameter specifies what the "current" time is; that is, where to base the +5 days off of (start position). When unspecified, it defaults to time().

Upvotes: 2

vaso123
vaso123

Reputation: 12391

Check this:

$end_date = date("Y-m-d H:i:s", strtotime(date("Y-m-d H:i:s"). ' + 5 days'));

MySql store the datetime in YYYY-MM-DD HH:MM:SS format.

Upvotes: 2

Related Questions