[SOLVED] COUNT only within last X amount of days

[wastedthelight]

Hello. Need a little help here please.

 

The script should grab only last 7 days of database entries which it does.  Also it also needs to count to see how many times a CD was played, which it also does, but gives overall total and not just within that 7 days.  Any help getting it to count only last x amount of days?

 

thanks.

 

<?php
$query = "SELECT artist, new, section, sectionnumber, 
date, COUNT(sectionnumber) FROM playlist WHERE new = '0' 
AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
GROUP BY section, sectionnumber ORDER BY section";

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

echo "
<table class=\"listfont\" width=\"700\" border=\"1\" 
bordercolor=\"#000000\" cellspacing=\"0\" cellpadding=\"0\" 
align=\"center\">
    <tr bgcolor=\"c0c0c0\">
</td>
<td>CD</td>
<td>Artist</td>
<td>Date</td>
<td>Times Played</td>
</tr>

";
while($row = mysql_fetch_array($result)){
echo "
<td>". $row['section'] ." ". $row['sectionnumber'] ." </td>
<td>". $row['artist'] ."</td>
<td>". $row['date'] ."</td>
<td>". $row['COUNT(sectionnumber)'] ."</td></tr>";
}
echo "</table>";
?>

[adam291086]

wont you need to store the dates that the song was played. The you can do a simple count with -7 days from the current date? As i am new to this i think this is a simple or maybe wrong solution.

[wastedthelight]

wont you need to store the dates that the song was played. The you can do a simple count with -7 days from the current date? As i am new to this i think this is a simple or maybe wrong solution.

 

The dates are stored and that's how it's grabbing the last 7 days of music only.  That part works as I said. The problem is that it counts not only the 7 days but since the creation of the database. It only displays on screen the last 7 days. but counts over 7 days.

[wastedthelight]

it already worked perfectly 

Some improper entries made it pull large numbers that made it look like it was pulling for the last month.  my bad.

 

well there's some code example though!