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.