Looking to select a row from the database using DATETIME

I have the following query

SELECT * FROM ".TBL_FOOT_GAMES." ORDER BY id DESC LIMIT 1

I need to add a WHERE clause on the field date_confirmed.

date_confirmed is a DATETIME t开发者_StackOverflowype.

I need to select only rows that are within 7 days of the current moment.

MORE CODE

SELECT g.home_user, g.away_user, g.home_score, g.away_score, g.id AS gameid, g.date_confirmed, 
       hu.username AS home_username, au.username AS away_username, ht.team AS home_team, at.team AS away_team
       FROM tbl_foot_games g INNER JOIN tbl_users hu ON hu.id = g.home_user INNER JOIN tbl_users au ON au.id = g.away_user 
       INNER JOIN tbl_foot_teams ht ON ht.id = g.home_team INNER JOIN tbl_foot_teams at ON at.id = g.away_team
       WHERE (g.type = '1' OR g.type = '2' OR g.type = '3' OR g.type = '4') AND g.status = '3' AND g.date_confirmed BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 50 WEEK)
       ORDER BY g.id DESC LIMIT 1

The statement works fine until I add the WHERE clause for the 50 week interval.

Presuming only seven days in the future (it looks like you're going to list upcoming football games):

SELECT *
  FROM `tbl`
 WHERE `date_confirmed` BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK)
 ORDER BY `id` DESC
 LIMIT 1

Please read the documentation first next time; the answers are all there.

... WHERE date_confirmed BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY) ...

Have a look at the NOW() and DATE_SUB() functions.

These should let you create a date 7 days ago, then in your where clause you can check that the datetime column is greater than this.

You can use the date_sub function of MySQL to see if the diff is 7 days or less.

SELECT * FROM ".TBL_FOOT_GAMES." 
WHERE DATE_ADD(DATE_CONFIRMED, INTERVAL '7 00:00:00' DAYS_SECOND) >= TIMESTAMP(CURDATE())
ORDER BY id DESC LIMIT 1

If you are interested in seeing only 7 days of difference from current date (ignoring the time value), then you can use DATEDIFF function like this:

SELECT * FROM ".TBL_FOOT_GAMES." 
WHERE DATEDIFF(CURDATE(), DATE_CONFIRMED) <= 7
ORDER BY id DESC LIMIT 1