wastedthelight Posted October 9, 2008 Share Posted October 9, 2008 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 More sharing options...
wastedthelight Posted October 9, 2008 Author Share Posted October 9, 2008 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 />"; } ?> Link to comment https://forums.phpfreaks.com/topic/127675-select-all-but-count-only-last-7-days/#findComment-660992 Share on other sites More sharing options...
Barand Posted October 9, 2008 Share Posted October 9, 2008 if you can attach a dump of your playlist data I'll see what I can can come up with Link to comment https://forums.phpfreaks.com/topic/127675-select-all-but-count-only-last-7-days/#findComment-661413 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.