Music Charting pages
refreshingapathy - September 28, 2009 - 16:20
| Project: | Station |
| Version: | 6.x-2.x-dev |
| Component: | Playlists |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
So one of the big demands that I've been catering to is that our music directors should be able to do their charts from the website... I've got most of the SQL code figured out but I my brain just fries when I try to think of a way to put a date restriction on it. So far, I've been using the following code inside a page (this example for our Indie rotation). Yes, I hijacked the "label" field for the rotation name as an experiment.
<?php
$header = array('Artist', 'Title', 'Rotation', 'Link');
$rows = array();
$sql = 'SELECT artist, title, label, link FROM {station_playlist_track} WHERE label = "Indie" ORDER BY artist';
$res = db_query($sql);
while ($row = db_fetch_array($res)) {
$rows[] = $row;
}
print theme('table', $header, $rows);
?>Suggestions for making this into a query that pulls only the last 7 days worth?
#1
Funny story, I just finished talking with my music director about how to do charting through our site as well.
I also just finished writing a module to extend the station_playlist module, allowing playlists to denote whether a track is "new" or not, and hope to continue extending that module's functionality, at least until CCK 6.x-3.x is widely adopted and used by station.
This is my big project right now, I'll update this with what I've got as I progress.
#2
http://github.com/timplunkett/Station-Playlist-Extender
#3
Very cool. Is new just implying some sort of rotation? We chart to CMJ, hence the interest in a specific charting genre for the new music.
I'm still looking to at least get the pasted code somewhat functional... I'm thinking there is some way to query if the node was created in the past week?
#4
Full CMJ charting is my final goal.
"New" implies that its in our rotation.
Genre was my next step.
And if you use views, you can sort by Node: Post date.
#5
True story on the views. I'll have to mess with creating a view for it instead. Thanks for the hint!
#6
tim.plunkett, why do you have the views boolean views handler? it looks like you're just duplicating the one that views ships. am i missing something?
the one thing i've wanted to do for a while is if the catalog is enabled have a catalog number field that if you put in the catalog number it auto fills the artist/album/label fields. this would make the reporting for logged albums much easier.
#7
I added another display option, 'New' or ''.
I gave up on figuring out how to extend a handler, so I just copied that one.
#8
i think there's a bug in the current playlist views code. trying to add a relationship from a station_playlist_track based view to the playlist node results in the following error:
user warning: Unknown column 'station_playlist_track.iid' in 'field list' query: SELECT station_playlist_track.iid AS iid, station_playlist_track.album AS station_playlist_track_album, station_playlist_track.artist AS station_playlist_track_artist, station_playlist_track.title AS station_playlist_track_title FROM station_playlist_track station_playlist_track INNER JOIN node node_station_playlist_track ON station_playlist_track.nid = node_station_playlist_track.nid LIMIT 0, 10 in /Users/amorton/Sites/d6/sites/all/modules/views/includes/view.inc on line 731.#9
oh right i'm remembering this now. views won't join tables on a composite key (nid/delta in this case) so i had started to add a new column to the table that was a serial value just for use as the primary key for view's purposes.
i also wanted to drop a link to #181883: Playlist Statistics which was some old code i'd done for kpsu for a similar purpose.
#10
tim.plunkett, for the views bool handler you should really look at making the true/false value configurable and submitting it back as a patch. i've struggled with that several times so i'm sure it would be useful for others as well.
#11
re: #6
yes, that would be cool. except WKDU doesn't use the catalog number the same way you do, so not immediately helpful.
the idea i had was that if you've already entered the artist, restrict the album autocomplete to albums by that artist, and vice versa
re: #8-9
so, that's an error due to code that's a WIP? are you on top of that or should i try and figure it out?
re: #10
that is a fantastic idea. i will look into that, we'll see how i do.
#12
tim.plunkett, just installed your new track module. Got the following:
http://skitch.com/rickheil/nb286/timplunketts-module
As you can see, the check boxes seem to live below everything else (this is with the latest -dev version, no AJAX patch).
#13
Oops, forgot to mention the code I added to my template.php file:
<?php
function THEMENAME_station_playlist_track_form($form) {
$header = array('', t('Artist'), t('Title'), t('Album'), t('Label'), t('Link'), t('New track'), t('Weight'));
$rows = array();
foreach (element_children($form) as $key) {
$row = array();
$row[] = '';
$row[] = drupal_render($form[$key]['artist']);
$row[] = drupal_render($form[$key]['title']);
$row[] = drupal_render($form[$key]['album']);
$row[] = drupal_render($form[$key]['label']);
$row[] = drupal_render($form[$key]['link']);
$row[] = drupal_render($form[$key]['newtrack']);
$form[$key]['weight']['#attributes']['class'] = 'track-weight';
$row[] = drupal_render($form[$key]['weight']);
$rows[] = array('data' => $row, 'class' => 'draggable');
}
$output = '';
if (count($rows)) {
drupal_add_tabledrag('station-playlist-tracks', 'order', 'sibling', 'track-weight');
$output .= theme('table', $header, $rows, array('id' => 'station-playlist-tracks'));
}
return $output . drupal_render($form);
}
?>
That should do the trick. Obviously replace THEMENAME with your... theme's name.
#14
I'll try that code out and report back, thanks tim.plunkett.
As a solution to the original SQL code question of the issue report, I've got it sorted out (literally):
<?php
$header = array('Artist', 'Title', 'Rotation', 'NID', 'UTS');
$rows = array();
$sql = 'SELECT station_playlist_track.artist, station_playlist_track.album, station_playlist_track.label FROM station_playlist_track, node WHERE station_playlist_track.nid = node.nid AND node.created>(unix_timestamp(now())-604800) ORDER BY station_playlist_track.artist';
$res = db_query($sql);
while ($row = db_fetch_array($res)) {
$rows[] = $row;
}
print theme('table', $header, $rows);
?>
Embed in a page with PHP inputs turned on, and you're good to go.
I tried to do it with views and found out that I am not very good with views yet.
#15
tim.plunkett, I tried adding the code you posted in #13 to template.php in my active theme... didn't change things. Is that the correct location to be editing?
#16
Well, maybe.
Its just after midnight for me, so I won't be up for testing it out more until tomorrow.
At which time I will also try out your code and see if I can get a view working nicely.