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

[Iris]

@sonichouse wrote:

@Iris wrote:

I just update summary.php and here’s the error:

Unable to execute: select 'Songs' as Item,count(1) as Desc from songs union all select 'Playlists' as Item, count(1) as Desc from playlists union all select 'Albums' as Item, count(distinct album) as Desc from songs union all select 'Albums Played' as Item, count(distinct Album) as Desc from songs where play_count>0 union all select 'Songs Played' as Item, sum(play_count) as Desc from songs where play_count>0 union all select 'Unique Songs Played' as Item, count(1) as Desc from songs where play_count>0 ;



HY000 1 near "distinct": syntax error

Iris

Hi Iris,

sqlite2 does not like the

select 'Albums Played' as Item, count(distinct Album) as Desc from songs where play_count>0

bit in that sql. I will find an approach that does work – for now can you just comment out that one sql statement ?

Steve, to get that error to go away I basically (one at a time) eventually commented out all the lines from that section with the exception of ‘Unique Songs Played’ …

Iris

[Iris]

Steve,

Bug?? I noticed that the time played for my tracks was the UTC time not my actual. I checked the time on my MSS, in the firefly log, etc. and they’re all correct – it’s just this. Is that the way you programmed it or is this a bug?

Iris

[greenleaf]

@sonichouse wrote:

@greenleaf wrote:

I just downloaded and set up firestats and it’s looking great, except I’m getting the following error on most every page instead of a graph:

Warning: copy(img/Albums.png) [function.copy]: failed to open stream: Permission denied in /var/www/firestats/functions.php on line 314

I’m pretty much a n00b when it comes to anything more complex than the simplest PHP, so I have no clue how to fix it myself.

My server’s running Ubuntu Feisty, with Firefly build svn-1376 and PHP both installed using apt, and Firestats 1.19.

It is trying to pull the .png file from google API to the img directory.

Is that directory writable ?

It wasn’t, but it is now, and now it works wonderfully. Thanks!

@Iris wrote:

Greenleaf: I thinkyou need a moe up-to-date version of Firefly.

Yeah, I do.

[sonichouse]

@Iris wrote:

Steve,

Bug?? I noticed that the time played for my tracks was the UTC time not my actual. I checked the time on my MSS, in the firefly log, etc. and they’re all correct – it’s just this. Is that the way you programmed it or is this a bug?

Iris

hi Iris,
I use the localtime in the datetime format, is your locale set correctly ?

try

sqlite dummy.db "select datetime('now',  'localtime');"

to see if that returns the correct time.

/Steve

[Iris]

@sonichouse wrote:

@Iris wrote:

Steve,

Bug?? I noticed that the time played for my tracks was the UTC time not my actual. I checked the time on my MSS, in the firefly log, etc. and they’re all correct – it’s just this. Is that the way you programmed it or is this a bug?

Iris

hi Iris,
I use the localtime in the datetime format, is your locale set correctly ?

try

sqlite dummy.db "select datetime('now',  'localtime');"

to see if that returns the correct time.

/Steve

I’m not even sure of I’m executing that command right – I get a SIGSEGV error.

# /opt/bin/sqlite /tmp/dummy.db "select datetime('now', 'localtime');"

[sonichouse]

wierd 🙂

try

 $ sqlite

SQLite version 2.8.16

Enter ".help" for instructions

sqlite> select datetime('now', 'localtime');

2007-12-16 21:38:22

sqlite> .quit

[sonichouse]

@sonichouse wrote:

sqlite2 does not like the

select 'Albums Played' as Item, count(distinct Album) as Desc from songs where play_count>0

bit in that sql. I will find an approach that does work – for now can you just comment out that one sql statement ?

Fixed in 1.21

summary.php:select 'Albums Played' as Item, count(Album) as Desc from (select distinct Album from songs where play_count>0)

Discussed in my blog here.

[Iris]

Well done, Steve! 😀

v1.21 worked flawlessly. I just needed to add in the code:

set_time_limit(60);

If it doesn’t hurt anything maybe you might want to consider incorporating this into functions.php permanently. Most of my charts still render in under 5 secs. I can certainly live with that until Ron comes out with another nightly and maybe then I’ll switch to sqlite3 too – lowering those rendering times for the two charts.

Now that I’m getting the summary page rendered without error I did notice that my total songs is the same as total albums. If I’m thinking total albums should be counting the actual number of albums then what it is reporting is wrong. All the other numbers look correct.

As far as my time issue I’ll have to work on that some more. What I do know is that the time my MSS reports back is correct and the time that FireFly reports in the logs is correct also. What’s confusing me is that since your app is really just querying the songs.db why would there be a discrepancy. Originally, I was thinking that it was a problem or oversight with your app but you say it’s reporting the correct time on your end. I guess I’ll just have to wait for Ron or someone more familiar then I with the MSS and sqlite to walk me through some commands to issue against it.

Thanks for your excellent efforts over the weekend though. I’m glad we got these issues ironed out. Looking forward to future enhancements 😉

Iris

[Iris]

Hi Steve,

After I wrote the last post I visited your blog and then your stats page. Wow, your rendering times are fast. I’m using DynDNS, perhaps they’ve introduced a bit of a delay in their free service. 🙄 Or, I have quite a larger library then yours – perhaps that’s the cause of the longer rendering times – seems to be plausible?

I also noticed your total songs and total albums where the same.

Iris

[sonichouse]

@Iris wrote:

Hi Steve,

After I wrote the last post I visited your blog and then your stats page. Wow, your rendering times are fast. I’m using DynDNS, perhaps they’ve introduced a bit of a delay in their free service. 🙄 Or, I have quite a larger library then yours – perhaps that’s the cause of the longer rendering times – seems to be plausible?

I think sqlite3 is much faster !!!!

@Iris wrote:

I also noticed your total songs and total albums where the same.

Doh – 1.22 fix now available 🙄

[Author]

Posts