Not sure quite how to approach this

andars

I have a table that containts information about songs played in a media player (media player uses GET to send to url).

The fields are in the table are:

userid
title
artist
album
genre
filetype
dateplayed

I want to create some sort of statistics page that displays the most played song, album, artist, and genre. However I'm not sure what query I would use to look that up, or how I need to approach it. There is duplicate entries where everything is the same with the exception of the dateplayed field as it logs every song played.

I need a query which sees out of the entries what the most common appearing artist is for instance.

My guess is to use a group by/count query, kind of like what you would use looking for duplicate enties, but I'm not sure how I would tell it to count the amount of times a duplicate entry is found and temporarly store it.

Pieman86

I would really recommend using counters to keep track of all the plays. At the moment, it sounds like you need to recalculate lots of statistics every time the information is requested. If you're getting play information for individual users then that isn't so bad, but creating and comparing new stats for all the artists, genres, albums and songs is pretty inefficient.

You could use one table to log all the dates that songs are played, and another one to do the counting. For example:

CREATE TABLE counter_table (
   counter_type varchar(16) NOT NULL,
   counter_name varchar(16) NOT NULL,
   counter_value int(8) NOT NULL,
   PRIMARY KEY (counter_name)
);

You could have entries in this table like ('genre', 'jazz, '50'), ('artist', 'louis_armstrong', 10') and ('song', 'stardust', '1'). Then the next time "Stardust" by Louis Armstrong is played you insert the date info into your first table, and also update the counter table using:

UPDATE counter_table SET counter_value=counter_value+1 
WHERE counter_name='jazz' 
OR counter_name='louis_armstrong' 
OR counter_name='stardust'

This query will update the relevant artist, song and genre fields. If you then want to find the most popular genres for example, it's easy.

SELECT * FROM counter_table WHERE counter_type = 'genre' ORDER BY counter_value DESC

will return you a sorted list of genres with the most popular one on top.

I'm not saying this is absolutely the best way of doing things. The update query's a bit verbose and I'm sure the table structure could be improved but, however you decide to do it, storing all the values you need in the database will really make your life easier. 🙂

andars

Well, the reason I havn't done a counter table yet is because that means doing extra queries to see if the song exists in the counter table, and then inserting and updating it as well as inserting the song into the users played history everytime a song is sent to the url and I'm trying to minimize queries. When looking up the statistics, its just for the invidiual user.

all the music is grabbed from one table based on the userid.

I found a query like this works =)

SELECT TITLE, COUNT(TITLE) AS NumOccurrences FROM PLAYING WHERE USERID='1' GROUP BY TITLE HAVING ( COUNT(TITLE) > 1 ) ORDER BY NumOccurrences DESC

No variables in that, but basically I just replace the '1' with {$USER["ID"]} and bingo