[RESOLVED] Finding Out Result Number (For Music Charts)

Smurlos

Hi.

When I play songs on my computer, it opens a php file which:

If the track doesn't already exist in the database it adds it to the database and updates the playcount to 1 and the timestamp.
Id the track already exists, it updates the playcount+1 and the timestamp.

Now, when I have a song playing, on my intranet system it shows what I am currently listening to but I want it to display say "Popularity: Number 35 of 205 songs" for example.

My database "songs"

SongID
title
artist
playcount

timestamp

So I need a way of ordering my songs by playcount (SELECT title,artist,playcount FROM songs ORDER BY playcount DESC) would be my MySQL query to order by most popular.

So I have my selection but now I'm a bit clueless on how I would find out which Result number out of the total results, a certain song falls under.

So say I am listening to:

SongID: 192
title: Beautiful Girls
artist: Sean Kingston
playcount: 190

To find which song I am currently listening to I have (SELECT title,artist,playcount FROM songs ORDER BY timestamp DESC) which selects the song currently playing.

So it will display something like: "Now Playing: Sean Kingston - Beautiful Girls (Play count: 190)" but I also want to put on there something like "Chart Position: 35" as mentioned above or "popularity: 35 out of 205 songs".

So say I ran a query that ordered results by descending (i.e. most popular first) and it returned 10 results. And I was using result number 5, how would I display the fact that this was result number 5 of 10?

Would be grateful if anyone could help me with this! Cheers!

maxpup979

I believe you can use a user variable to accomplish this. Something like:

set @a:=0;
select @a:=@a+1 as rownum, songid, songname from songs where etc.

Smurlos

maxpup979 wrote:
I believe you can use a user variable to accomplish this. Something like:
set @a:=0;
select @a:=@a+1 as rownum, songid, songname from songs where etc.

How would I go about putting that into a php script?

Part of my current script:

<?
// Select Latest Track Played
$select_latest = mysql_query("SELECT tracktime,starttime,datetime,title,album,artist,playcount,albumcover,filename FROM songs ORDER BY datetime DESC LIMIT 1");
$latest = mysql_fetch_array($select_latest);

// Function here that shows 'Played 5 mins ago', 'currently playing' etc.

// Show Track Info
SongStatus($trackunixdate,$time,"status"); 
SongStatus($trackunixdate,$time,"time");
echo("</u><br><b><font size='3'><a href='$filename'>$tracksong...</a></font>");
echo("<br>$trackartist ($trackalbum)<br>");
echo("<font size='1'><b>Duration:</b> $tracktime | <b>Play Count:</b> $playcount | <a href='javascript:MusicChart();'>Full Chart</a> | <a href='javascript:LastPlayed();'>Last Played</a></font>");
?>

This displays:

Now Playing: (or Played 5 min ago: etc.)
Beautiful Girls
Sean Kingston (No Album Entry)
Duration: 3:05 | Play Count: 109 | Full Chart | Last Played

What I want is:

Now Playing:
Beautiful Girls
Sean Kingston (No Album Entry)
Duration: 3:05 | Play Count: 109 (Number 3 in Charts) | Full Chart | Last Played

So say I had the following:

SongID Title Artist PlayCount

01 Beautiful Girls Sean Kingston 105
02 Chelsea Dagger The Fratellis 112
03 Alfie Lily Allen 96
04 Foundations Kate Nash 206

And it was Foundations that was playing (SongID: 4), I would like it to show that this is currently position 1 in the charts (as it's got the highest play count).

Similarly if Alfie (Lily Allen) were playing, I'd like it to show that it's currently at position 4 in the charts as it has the lowest play count. So out of 4 results, Lily Allen is result #4 and out of 4, Kate Nash is #1 and Fratellis #2 and Sean Kingston #3.

Thanks
Carl

Smurlos

Hi, it's ok, I have found a temporary fix for it. As it's only personal on my computer, it may use a lot of resources, but I used a while loop and just counted +1 each time. Not the most economic way but again, it's only for personal use on my pc.

Thanks anyway

laserlight

Assuming $playcount is the play count of the current song, you could find the song's current position with a query:

SELECT COUNT(*)+1 AS current_position FROM songs WHERE playcount > $playcount