Jump to content

Select All but Count Only last 7 Days


wastedthelight

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/127675-select-all-but-count-only-last-7-days/
Share on other sites

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 />";
}
?>

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.