You are here: Home » Reply » Reply To: FireStats – Top 40 php script (now with added charts)

Reply To: FireStats – Top 40 php script (now with added charts)

#14690
rpedde
Participant

@sonichouse wrote:

I have butchered together a script to find out what the most popular tracks are on my Firefly server.

The script is written in php, and has some hardcoded paths for the database. I am new to php so this could be more elegant.

Just copy the firefly.css to your web folder (in my case it was /opt/share/www/cherokee) and then create firefly.php as


<?php

if(isset($_GET))
$mode = $_GET;
else
$mode = "Tracks";

try {

if($mode=="Tracks") {
$Target = "Albums";
$limit=40;
} else {
$Target = "Tracks";
$limit=10;
}

$query="
select Artist,Album,title as Track,play_count,track as No
from songs
where play_count >= 1
group by 4,1,2,3,5
order by 4 desc,1 asc,2 asc,5
limit $limit";

$album_query="
select Album,Artist,sum(play_count) as Played
from songs
where play_count > 0 and album''
group by 1,2
having sum(play_count) >= 1
order by 3 desc,1 asc,2 asc
limit $limit";

$dbh = new PDO('sqlite:/opt/var/mt-daapd/songs3.db');
$dbh->exec('SET CHARACTER SET UTF8');

print "nnn";
print "th{font-size: 7pt}td{font-size: 7pt; padding: 2 1em 0 1em;border-right: thin solid; border-bottom: thin dotted;}n";

print "

Firefly - Top $limit $mode

n";
print "nShow $Target";print "";

if($mode=="Tracks")
{

print "ntnn";

foreach ($dbh->query($query) as $row)
{
print "t";
print "";
print "";
print "";
print "";
print "";
print "n";
}
print "
PlayedArtistAlbumTrack TitleTrack #
$row[play_count]$row[Artist]$row[Album]$row[Track]$row[No]
n";
}

if($mode=="Albums")
{

print "ntnn";

foreach ($dbh->query($album_query) as $row)
{
print "t";
print "";
print "";
print "";
print "n";
}
print "
Tracks PlayedAlbumArtist
$row[Played]$row[Album]$row[Artist]
n";
}

print "nn";

$dbh = null;
}
catch (PDOException $e)
{
print "Error!: " . $e->getMessage() . "
";
die();
}
?>

Once I learn how to add some nice graphics etc this can only imrove.

[Edit]
Updated to toggle between Tracks and Albums
[/Edit]

/Steve

And this is the best example yet of “why sqlite”. The gdbm backend was faster than sqlite, but you couldn’t do this kind of thing with gdbm as backend.

Nice. Thanks for sharing this.

— Ron