Storing image gallery stats - MySQL structure?

spacehog

I've got a photo website which uses PHP to dynamically create the galleries based on remote folder content; jpeg modified dates etc. There are various galleries of jpegs which are directly created from the various folders full of pics on the server.

I am currently logging each photo viewed into a MySQL database by simply adding a new row to a table for each photo viewed, with columns for date/time, gallery keyword and image number. This was originally for my own curiosity; I wanted to know how much my site was looked at.

I get an average of somewhere around 70 photos viewed a day, hence 70 new rows added to the table.

I would like to make a section for the site for 'most popular photos' from the previous week or so. I realise that the current structure is a little cumbersome. I'd really appreciate any advice on how best to structure the MySQL table to store the stats, to be easily queried by PHP to get the information I need.

I'm quite savvy in both MySQL and PHP, so a plain english description is fine if it's easier!

Thanks a million to anyone who can help - this is turning my brain in knots!

Mark

the site:
http://www.something-about-mark.co.uk/album/albumlist.php

Roger_Ramjet

Your structure sounds fine to me; one row per access would still be correct even if it were 1000s per day, just disk storage after all. What you seem to be having problems with is querying the data to derive the stats.

Try something like this


$query1 = "SELECT image_number, count FROM (SELECT image_number, count(image_number) as count FROM viewings WHERE datediff(date_viewed, now()) < 8 GROUP BY image_number)  ORDER BY count DESC LIMIT 10";

This query will first find and count all viewing for each image viewed in the last week "datediff(date_viewed, now()) < 8" and then order them in descending order and return the image numbers and counts for the top 10.

onion2k

Originally posted by Roger Ramjet

$query1 = "SELECT image_number, count FROM (SELECT image_number, count(image_number) as count FROM viewings WHERE datediff(date_viewed, now()) < 8 GROUP BY image_number)  ORDER BY count DESC LIMIT 10";

[/B]

Lovely. Now write it so that it'll work in a version of MySQL that hosting companies might have deployed. Subselects are a new feature to MySQL 4.1 .. most hosts are still on 3.23.xx.

select count(image_stat_id) as total from image_stats where date_sub(now() inteval 7 day) group by image_id order by total limit 10

So you're counting the number of unique rows (views basically) on each image in the past 7 days, and grouping the count by the image_id, then ordering them by said count, and limiting it to 10 results.

Roger_Ramjet

Don't know about most hosts. Would not use anything less than 4.1 myself, but then I run our company's site that include personal data which the UK Data Protection Act requires me to take every reasonable precaution to protect. Using outmoded versions of software with know security holes (like register globals on) would not be considered 'reasonable'. And using a RDBMS that does not support sub-queries is just plain stupid.

Basically, if you are too cheap to pay £100 per annum (our host's price with 'unlimited disk space') for quality ISP hosting then that is your problem. If you are running it at home then upgrade, it's Open Source and therefore free after all.

Roger_Ramjet

Spacehog, my last post was for onion2k, with whom I seem to bang heads quite often around here. If you can't use sub-queries (eg yours is a hobby site on a free web host) then you create a temporary table with the first query and select from that with a second query. less efficient, but a whole darn sight easier than the alternative which is a single query of arcane complexity, and one that I'm not even going to attempt.

onion2k

Originally posted by Roger Ramjet
Don't know about most hosts. Would not use anything less than 4.1 myself, but then I run our company's site that include personal data which the UK Data Protection Act requires me to take every reasonable precaution to protect. Using outmoded versions of software with know security holes (like register globals on) would not be considered 'reasonable'. And using a RDBMS that does not support sub-queries is just plain stupid.

Basically, if you are too cheap to pay £100 per annum (our host's price with 'unlimited disk space') for quality ISP hosting then that is your problem. If you are running it at home then upgrade, it's Open Source and therefore free after all.

One of my clients pays over £30,000 a year for hosting, and gets mysql 3.23.xx. Mysql 4 is simply too new too have been proven in a secure environment. I wouldn't move over to it until its been around for at least 18 months. Plus, the sites they're running have been around for a few years, and without investing considerable time in testing with the latest version theres no telling what might break.

Roger_Ramjet

Tired when I wrote my blurb, mixing versions and applications: register globals and ver 4.1 are PHP, and I still would not use anything less.

As to MySQL ver 4, OK it's new, but I'll bet they're using Microsoft software that's less than 3 years old ie still in Beta Test. In fact, all MS software is in beta test untill service pack 4, which comes out just before the software stops being supported usually.

I've been writing SQL for 20 years and for most of that I've been using nested queries. Can't imagine coding a database that needs £20,000 of hosting (how many terrabytes of data is that?) without them. MySQL great for simple web and eCommerce apps, not for backend data-warehousing and core business applications, though it's getting there.

onion2k

Originally posted by Roger Ramjet
As to MySQL ver 4, OK it's new, but I'll bet they're using Microsoft software that's less than 3 years old ie still in Beta Test. In fact, all MS software is in beta test untill service pack 4, which comes out just before the software stops being supported usually.

I've been writing SQL for 20 years and for most of that I've been using nested queries. Can't imagine coding a database that needs £20,000 of hosting (how many terrabytes of data is that?) without them. MySQL great for simple web and eCommerce apps, not for backend data-warehousing and core business applications, though it's getting there.

The server is running w2k server (much to my annoyance..) and its all service packed up, so no, they're not running beta software.

The reason their hosting costs as much as it does has nothing to do with the quantity of data (its less than 2GB in fact). The fact is the site has to available all the time. Literally 100% uptime. You'd not believe the amount of redundancy thats built into the whole thing. Upgrading to new versions of software without very extensive testing simply doesn't happen. In fact, I doubt they'll ever use Mysql 4.

And for someone whos been writing SQL for 20 years I'm suprised you describe a slightly longer query to avoid a subselect as 'arcane'. I've been doing SQL for about 5 years and I wrote it in one line (see my previous post).

spacehog

Thanks for the response guys.

I'm on 3.23.49. So no, I can't use sub-selects. It's not a free host, I'm paying about £30pa for it. Can't afford much more. I am, however, going to migrate to a service asap which at least spends time upgrading once in a while instead of apparently sitting around drinking tea. I digress.

So - I'd like to try the sort of query suggested above. The only problem is that there are two fields identifying the image - an 'albumkey' (keyword denoting the album) and the image_id therein. i.e. there may be lots of images with the id '5' for example, each from a different album.

Onion2k - how would I alter your query to deal with this structure, and still show the top 10 images across the board? Or would it be more sensible for me to concat the two fields to judge the top 10, then split again to allow me to generate the relevant html?

onion2k

You just need to group them so that individual images are bunched together..

select count(image_stat_id) as total from image_stats where date_sub(now() inteval 7 day) group by album_id, image_id order by total limit 10

spacehog

got ya!

brilliant, gonna give this a try.

spacehog

Onion - can't see where you're using my date field in your date_sub command so I'm inserting roger's datediff command in its place.

spacehog

Here's my query:
"select count(viewtime) as total from samstats where datediff(viewtime, now()) < 8 group by albumkey, picnum order by total limit 10"

But it doesn't return any data. I've used the viewtime in the count() because the table doesn't have an id column and i figured the viewtime would work because it's still a unique value.

Why no data, anyone?

Thanks

spacehog

Sorted. Couldn't use datediff, 'cos that's new in 4.1 too.

here's my final query:

SELECT albumkey, picnum, count(id) AS total FROM samstats WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= viewtime GROUP BY albumkey,picnum ORDER BY total DESC limit 10

Thanks for your help guys!