counting most downloaded files within last 10 days

[thara]

Hi..

 

In my web site, users can download music videos. Now im going to add a top chart to my site. so need to add song to the chart according to their downloaded amount in a song and also it should be added only it's added date in last 10 days. I can create php script but have a problem in creating mysql query. Any help appreciated.

 

Thank you..

[trq]

What have you tried?

[thara]

SELECT song_id, song_title, side_bname, CONCAT_WS(' ', artist_fname, artist_mname, artist_lname) AS artist_name

FROM songs

INNER JOIN artists ON songs.artist_id = artists.artist_id

WHERE songs.song_type = 'video'

ORDER BY downloaded DESC

[thara]

but its only display songs according to my downloaded system. so I need to select songs within a particular time period as well...

 

any help appreciated.

[fenway]

You need to add:

 

songs.yourDateField > NOW() - INTERVAL 10 DAY

 

To the WHERE clause.

[thara]

thanks for your reply...

 

as you said, I changed my query like this....

 

SELECT song_id, song_title, side_bname, downloaded, CONCAT_WS(' ', artist_fname, artist_mname, artist_lname) AS artist_name

FROM songs

INNER JOIN artists ON songs.artist_id = artists.artist_id

WHERE songs.song_type = 'video' AND songs.added_date > NOW() - INTERVAL 10 DAY

ORDER BY downloaded DESC LIMIT 10

 

but it doesn't work

[fenway]

Doesn't work How?