adding DATE_SUB to query to return range of values in mysql

Question

Here is my original query:

$query = mysql_query("SELECT s.*, UNIX_TIMESTAMP(`date`) AS `date`, f.userid as favoritehash FROM songs s LEFT JOIN favorites f ON f.favorite = s.id AND f.userid = '$userhash' ORDER BY s.date DESC");

This returns all the songs in my DB and then joins data from my favorites table so I can display wich items a return visitors has clicked as favorites or not. Visitors are recognized by a unique has storred in a cookie and in the favorites table.

I need to alter this query so that I can get just the last months worth of songs. Below is my attempt at adding DATE_SUB to my query:

$query = mysql_query("SELECT s.*, UNIX_TIMESTAMP(`date`) AS `date`, f.userid as favoritehash FROM songs s WHERE `date` >= DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) LEFT JOIN favorites f ON f.favorite = s.id AND f.userid = '$userhash' ORDER BY s.date DESC");

Suggestions?

Answer 1

You just have to move the WHERE clause:

SELECT
    s.*,
    UNIX_TIMESTAMP(`date`) AS `date`,
    f.userid as favoritehash
FROM
    songs s
    LEFT JOIN favorites f ON f.favorite = s.id AND f.userid = '$userhash'
WHERE `date` >= DATE_SUB( NOW( ) , INTERVAL 1 MONTH )
ORDER BY s.date DESC

LEFT JOIN is a part of the list of tables in FROM, you can't have conditions where.

Answer 2

Here is a useful tool that help you use the date_sub function http://www.mysqlformatdate.com/mysql-functions/date_sub/