Increment Date

[jcstanley]

Hi

 

What I am trying to do is select items from a databse but only between two dates, but I am not sure how I can make the $week variable = today + 7 days  and the    $month variable = today + 1 month.

 

$today = date("Y-m-d");
$week = ?
$month =  ?

 

I guess the SQL would look something like

 

SELECT * FROM table BETWEEN $today AND $week

 

Any ideas? 

 

 

 

 

[Snooble]

http://us2.php.net/date

 

 

[jcstanley]

Thanks - but which section am i meant to look at?

[Snooble]

Because i'm nice:

 

Look at the mktime() part!

 

http://us2.php.net/manual/en/function.mktime.php

 

Sorry my fault thought i gave you the link. :S

 

Read read read

 

Snooble

[jcstanley]

Thanks

 

I have managed to increment the dates and have checked that it does work, but now having trouble with the SQL

 

This is what I have but finds no rows.

 

$today = date('Y-m-d');
$month = date('Y-m-d', strtotime('+1 month'));
.
.
.
$query = "SELECT eventid, name, DATE_FORMAT(date, '%d %b %y') AS date, time, type, extra FROM events WHERE type = 'Race' AND date BETWEEN $today AND $month ORDER BY 'date' asc";

 

Not sure what has gone wrong, and yes there are events in the databse within the date range 

 

 

[obsidian]

I wouldn't use PHP at all for this since you're using it within a MySQL query. You're much better off letting MySQL do the work for you:

SELECT eventid, name, DATE_FORMAT(date, '%d %b %y') AS formatDate, time, type, extra
FROM events
WHERE type = 'Race' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

 

Notice that to run your date comparison, you have to select your DATE_FORMAT result as a different name in order to return accurate results.

[jcstanley]

Thanks that works perfectly

 

Can the same be done with an interval of 1 Month?

[obsidian]

Thanks that works perfectly

 

Can the same be done with an interval of 1 Month?

 

Definitely. Also, to make it more readable, and as I'm sure fenway would be quick to point out, the following is cleaner:

BETWEEN CURDATE() AND CURDATE + INTERVAL 7 DAY;

 

Check out the MySQL Date and Time functions for more details about INTERVAL.