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

[rpedde]

@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 "
Played
Artist
Album
Track Title
Track #
$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 Played
Album
Artist
$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