It could be a simple query to the db to select from the most voted photos in the previous month, the one with higher score.
But if it is going to be displayed for an entire month without changing, it's a waste of db queries to me =) Just my 2 cents.
Actually, I am. I just haven't had time to work on it. For a 'site to be someday worked on' I'd like to do a "Hot Rod of the Month" type thing (restoring classics is a big thing in the area where I'm living now) so it's something I'm definitely interested in (and will be doing) but just haven't had a chance.
Off the type of my head I was thinking along the same lines you were..... create a new table and/or field somewhere to store the value and then once a month run a cron job to pull a pic based upon X values and then store that image's ID in the new field. I'd be hesitant about trying to tackle copying the image to a second category because then you start getting your hands dirty, so to speak, in that you would need to physically copy the file to a different users folder (or copy it within the same folder with a different name) in order to get it to show up in a different category because right now Gallery doesn't support having the same image appear in multiple categories. I think it'd be cleaner to just grab the current value. Although it would be pretty cool to be able to track past winners, etc. OK, I've got to think about this a bit more.
Well, another way you could do it would be to add a field to each image and then duplicate the newest replies and newest images to display all images with photoofmonth=1 and get the month from the dateline of the image to display it as a row in the image bits.
Still with the cron set up, just instead of copying or moving it, it would change the value or photoofmonth (of whatever name you give it)
Hmm... What about serializing the info in the datastore table, and then creating an array to store the month/year and imageid that would look something like this:
'01_04' => imageid,
'02_04' => imageid2,
'03_04' => imageid3
That way you could keep track of past winners as well. Something like that could prevent having to even create a cron script as well... Just some simple code to tell it to update if it can't find an image that corresponds with the current month from that array.
Brian: Ah, good idea! That way the same query that grabs the value would also be the updater so the first time the query is ran for the new month it'll grab the new value for the month, update the datastore, and then continue and then everybody else after that will just grab the newest value. The wheels are spinning.....
Shon: Essentially he said that we could do what we want (the picking of a 'pic of the month', the storing of it's value, the historizing of it, and the grabbing the current value) all from within the same PHP file instead of having seperate components. Would be a lot cleaner.... should be a 'doable' project.
This turns out to be more problematic than I thought. The sql may be beyond my skill level. Anyone here able to take a crack at it?
We need a slice of the posts table given by date rate, joined with the rate info.
Now it's like we need another query on that result to determine the highest rated. Nested selects are beyond me (but I can learn if someone here is patient).
I also wonder what the results might look like. We have some photos that are favourites. Due to new members, these may show up as pic of the month again and again. Ever been in a burger joint where the employee of the month is twelve photos of the same face? The criteria may need to be tweaked somehow. Exclude past winners? Only query photos posted in the given month?
In playing with it, I also notice we have "Worst photo". High number of votes but very low score.
I can do it in code with loops, but it wouldn't be terribly efficient. But then maybe with all these extra consideration, that kind of sift and sort is required.
Certainly want to see results for variable periods: photo of the day, week, month, year. Might not use them all but might be surprised...
This turns out to be more problematic than I thought. The sql may be beyond my skill level. Anyone here able to take a crack at it? ....
I'll be working on my own version but not for at least two weeks. I'm totally tied up with projects at work (that actually pay the bills ). Taking Brian's suggestion into account, though, it shouldn't be that bad for one of the usual SQL guys to come up with something in a rush if it's really needed that quickly.