Stats on your gallery

edasque

Any interest in this and more ?

http://www.frenchguys.com/gallery/index.php?p=stats

sbillard

Nice. You could put it up in the WIKI in the hacks section http://www.zenphoto.org/trac/wiki/ZenphotoHacks

acrylian

edasque: I suppose you maybe use the google graphics api for that?

edasque

I sure am. I'll pretty up the code a bit, it's really pretty simple.

function pie_graph($col,$title)
{
$sql = "SELECT count( `".$col."` ) , ".$col." FROM `images` GROUP BY ".$col." ORDER BY count( `".$col."` ) DESC LIMIT 5";
$result = mysql_query($sql);
while($r = mysql_fetch_array($result)) {

$current_Label = $r[1];

if (empty($current_Label)) { $Label .= 'Unknown|';
} else {
$Label .= $r[1].'|'; }
$Values .= $r[0].',';

}
$Label=trim($Label,"|");
$Values=trim($Values,",");

echo '<img src="http://chart.apis.google.com/chart?chts=FFFFFF&chtt='.$title.'&cht=p3&chco=537F12&chd=t:'.$Values.'&chs=650x150&chl='.$Label.'&chf=bg,s,2E3233">';

}

sbillard

Have you thought of adding other stats? I think one on hitcounts would be really nice.

edasque

You mean, how many images have a given hitcount in a pie chart ? What exactly are you thinking about ?

I'd like to do total hitcount over time but that's not easy unless I use a crontab and do my own accounting.

sbillard

I meant the distribution of hits over images and/or albums

edasque

Like, how many images have 60+ hits, how many 40+, ... ?

sbillard

Yes, that but also maybe a pie chart of all images with more than some threshold of hits. that shows each album's slice of the pie.

timo

That's fantastic. I just added this to my gallery http://t413.com/gallery/ and I am very impressed.
I really would like a line graph or histogram of when pictures were taken, showing how many per week, day, or month. I think that would be even more impressive. Google analytics can show how many people visit my site and when but it would be neat to see how many pictures were taken and when. The mysql database already has date information, although I'm not familiar enough with how to use it for this.
Good job and thanks for such an awesome new feature.

edasque

Yes, I was thinking of doing that, it wouldn't be that difficult but it would be best to provide a UI so one could zoom in and look at different periods. Now that I think of it, it's quite an UI challenge I'll try to whip out something but I don't know PHP very well (or at all).

I wouldn't mind an excuse to build a sparkline too

timo

Hey, I've been working with the function you created because I knew the data it was returning to be inaccurate. I looked through http://code.google.com/apis/chart/ and found that the type of encoding you used (text encoding) only accepts values from 0 to 100. On my gallery I've got 14,459 photos, 10,998 of which were taken by my XTi. When graphed any values over 100 were kept at 100, making the graph absolutely wrong. I fixed that in my the revised function below by turning each value into a percentage. Also, the "unknown" label was not working for my data, and by changing the `$sql =` line to match code I generated with phpmyadmin I got it to work. Now it doesn't show that I use flash on a quarter of all photos but rather less than one percent.

`

``function pie_graph($col,$title)

{

$sql = "SELECT count( * ) AS `Rows` , ".$col." FROM `images` GROUP BY ".$col." ORDER BY ".$col." LIMIT 0, 30";

$result = mysql_query($sql);

while($r = mysql_fetch_array($result)) {

$current_Label = $r[1];

if (empty($current_Label)) { $Label .= 'Unknown|';

} else {

$Label .= $r[1].'|'; }

$Values .= ( ($r[0])/100 ).',';

}

$Label=trim($Label,"|");

$Values=trim($Values,",");

echo '';

}`

timo

Oops, that's not the one with the percentage, that just divides the result by 100. This is the right function:
`

function pie_graph($col,$title)

{

$photosArray = query_single_row("SELECT count(*) FROM ".prefix('images'));

$photosNumber = array_shift($photosArray); //get total number of images

$sql = "SELECT count( * ) AS Rows , ".$col." FROM images GROUP BY ".$col." ORDER BY ".$col." LIMIT 0, 30";

$result = mysql_query($sql);

while($r = mysql_fetch_array($result)) {

$current_Label = $r[1];

if (empty($current_Label)) { $Label .= 'Unknown|';

} else {

$Label .= $r[1].'|'; }

$Values .= round( (($r[0])/$photosNumber*100),2).',';

}

$Label=trim($Label,"|");

$Values=trim($Values,",");

echo '';

}

`

edasque

Yeah, I found that out when I was working on the bar graph. Glad you were able to fix it. These are just hacks and deserve a well written set of functions that are more generic and probably use the extended encoding.

timo

I'm interested to see your timeline bar graph function, I'd like to work on it myself but don't really have a good enough handle on mysql to know where to start.
The text encoding you used for generating the graphs is almost ideal for the pie_graph() function you wrote, extended encoding is not any better for this purpose.
I think this statistics graph idea deserves a prominent spot on the hacks page and will show up in themes before long.

edasque

Ok, so several things:

1) My query for the timeline thus far is:
SELECT COUNT( DATE( date ) ) , DATE( date ) FROM `images` WHERE date IS NOT NULL GROUP BY DATE( date ) ORDER BY DATE( date ) DESC LIMIT 62

It retrieves the last 62 days with photos (that's all you can plot with google charts unless you want to plot by month or by another slice of time than day and then you have to do some arithmetic).

2) The encoding doesn't really matter as long as you normalize prior to plotting, as you mentioned in your post. That's an early mistake I had made.

3) I have to be smarter in my pie charts. Though I take most photos with the same cameras across the years (Canon Digital Elph 100, EOS D30, S30, EOS 1D, S50, EOS 5D, G9) I have at times borrowed cameras to make a shot and as a results there is too much noise. So I really need to display of those above 5%, Unknown (I think Photoshop did not maintain EXIF data early on) and a group of Miscellaneous for all known cameras under 5% usage.

I think the same must apply to ISO, F/STOP, Focal Length, Focal Length Equivalent. It might be also useful to display focal length for SLR cameras only as the Focal Length or FL equivalent for compacts is just not as interesting.

4) For the timeline chart, there is a lot more to do than the SQL query. We have to decide on the unit (days won't work unless you plot the last 62 days only where you might actually plot any value or few since you might not have shot much in the last 2 months). So let's pick months for example. You have to use the SQL results and figure out how many photos were taken that month. Of course, you can modify the query so it doesn't group by day but by months.

5) At some point, we might want to move away from google charts though it's easy to use and free (as in free beer) so we can put links, display data when mousing over (what albums contain photos taken that month. It'd be nice to know that that big bump in February was your friend's wedding for example.

A lot more work, indeed.

edasque

The month query for the bar graph could look like this :

SELECT COUNT( DATE_FORMAT( date, '%x%m' ) ) , DATE_FORMAT( date, '%x%m' )
FROM `images`
WHERE date IS NOT NULL
GROUP BY DATE_FORMAT( date, '%x%m' )
ORDER BY DATE_FORMAT( date, '%x%m' ) DESC
LIMIT 62