CODEWITHSUNDEEP
phpmysqlsqldate
villasenor
villasenor

Reputation: 107

Select SQL item with date within current week

We have a database of films, and need to select the film with a start and end date within the current week. We have two columns in the SQL table, both of type "date", and are written in the format 2015-01-25 (ISO)(YYYY-MM-DD). We need to select the entry that occurs within the current week. The start date for the film is usually the Friday of the week, and the end date is usually the Sunday of the week. I want to be able to show what the upcoming film for the upcoming weekend will be, no matter what day of the week they check. I have written the following PHP and SQL query, but I'm not getting any data back when I echo the $result.

$date = strtotime("now");
$last = strtotime('next Sunday');
$date = date('Y-m-d',$date);
$last = date('Y-m-d',$last);

$result = mysql_fetch_array(mysql_query("SELECT * FROM campusFilms WHERE startDate BETWEEN $date AND $last"));

Any assistance would be greatly appreciated, and I am happy to elaborate on anything needed. I have searched other StackOverflow questions but none of them quite answered this specific case.

Upvotes: 2

Views: 3203

Answers (3)

John Ruddell
John Ruddell

Reputation: 25842

Why not just use YEARWEEK() ?

SELECT whatever_you_need 
FROM campusFilms 
WHERE YEARWEEK(CURDATE()) BETWEEN YEARWEEK(startDate) AND YEARWEEK(endDate);

yearweek returns the year and week of any given date which will handle multiple years.

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

SELECT * 
  FROM campusFilms 
 WHERE WEEK('2015-02-05') BETWEEN WEEK(startDate) AND WEEK(endDate);

http://sqlfiddle.com/#!2/476d3/3

This solution cannot use an index so, if performance is an issue, we can look at alternative solutions.

Upvotes: 3

Aleks G
Aleks G

Reputation: 57306

If all you care is to find records where startdate is between the Friday and Sunday of the current week, then you don't even need to use PHP to compute dates, you can get mysql to do all the work for you:

SELECT *
  FROM campusFilms
 WHERE startDate BETWEEN
       DATE_ADD(CURDATE(), INTERVAL(-2 + MOD(8-DAYOFWEEK(CURDATE()),7)) DAY)
       AND
       DATE_ADD(CURDATE(), INTERVAL(MOD(8-DAYOFWEEK(CURDATE()),7)) DAY)

This works regardless of what day of the week it is today. If it's Friday, it'll give you films between today and the day after tomorrow. For Saturdays, it'll give you films between yesterday and tomorrow and for Sundays, it'll give you films between two days ago and today.

Upvotes: 1

Related Questions