Tuesday, November 08, 2011

finding the first played date and displaying it

A small gotcha in implementing the "First broadcast on ...." feature on the radiosai schedule page - our local database has the first played values per bcid. So, the first of all those values has to be selected. After some trial and error with group by - which would fail -

finally used this technique,
SELECT firstplayed FROM play_history_tablename WHERE filename = NameofFile.mp3 order by firstplayed  limit 1

This query conveniently gets the single result which we want, which is the first played date on any stream. Then incorporated this into our csv export script, added an extra field on the web-server's database, and added code to the schedule and search pages over the course of a week. On the search page, sorting by first played adds more functionality. So, added the code for that, and made the table clickable to enable sort by Description field also, using the sorrtable javascript library from kryogenix.

No comments:

Post a Comment