Select All but Count Only last 7 Days

[wastedthelight]

I'm having a problem figuring out how to do the following.

 

The query should return a list of every DJ in the system and then show how many entries they put in within the last 7 days.

 

Currently what I have is only returning those who actually inputted data in the last 7 days, not including those who didn't and have 0 returned rows.  I want it to show even people who have 0.

 

$query = "SELECT dj, COUNT(date) FROM playlist WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY dj"; 

 

The count is on date but doesn't really matter what is there since it just needs to count how many rows were found in the 7 day span....or does it? ha.

 

Thanks everyone.

[wastedthelight]

I tried to do a sub query with no luck as well. I feel like I'm on the right track but apparently my SQL code is wrong as I get a syntax error:

 

<?php
// Make a MySQL Connection

$query = "SELECT dj, artist, (SELECT date, COUNT(artist) FROM playlist WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS total_plays FROM playlist GROUP BY dj"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['dj'] ." played ". $row['total_plays'] ." songs on their last show.";
echo "<br />";
}
?>

[Barand]

if you can attach a dump of your playlist data I'll see what I can can come up with