Page 1 of 1
Best way to keep track of section popularity?
Posted: Sat May 29, 2004 9:53 pm
by jonas
So, I am making a game database and I want to keep track of which games are hot and have it constantly change automatically.
So far, I have a variable in my games table that is called gamepop and everytime someone visits the page it adds 1 to that variable for that game.
Now on my main page I want to have a Top Five Most Popular Games list that will update itself from the database.
Is it wise to do it as I have or is there a better way because I'm thinking that a game could be really popular and then nothing will ever beat it but I kinda want it to fluctuate properly.
Secondly, what's the best way to limit the database output to only the top 5 listings?
Thanks!
Matt
Posted: Sat May 29, 2004 9:59 pm
by Illusionist
I think your method of doing it is fine. Its not really the popularity of the game, its just what the users want to see most often. I guess.
As for you limiting question, add this to the end of your select statement:
"ORDER BY gamepop ASC LIMIT 0,5"
Will get the Top 5 in ascending order!
Posted: Sat May 29, 2004 10:01 pm
by feyd
SELECT * FROM games_table ORDER BY gamepop DESC LIMIT 5
I think..
[edit: beat me to it.. sorta]
Posted: Sat May 29, 2004 10:02 pm
by jonas
Very good!
But, the one thing I was thinking is that say Final Fantasy 12 comes out and immediately hits 10,000 'hits' and shoots to #1... then a week later it's not getting as many hits but it still has that 10,000 it will stay at #1 for a long time.
Don't you think it would be better to have a period say where the variable can loose value?
I have no idea how to code this, but I'm just thinking it out in my head
Oh and to manually adjust their gamepops back to zero would be insane since my database will have thousands of titles. (I have big plans, heh)
I need to automate a good way of getting the gamepop to loose value and gain value.
Posted: Sat May 29, 2004 10:06 pm
by feyd
you could have daily and/or weekly and/or monthly and/or yearly and/or all time top 5's pretty easily I'd think..
Posted: Sat May 29, 2004 10:09 pm
by jonas
I plan on having just a Top 5 for all games and then a top 5 for each system. Very similar to gamefaqs.com
Posted: Sat May 29, 2004 10:44 pm
by feyd
hmm.. I think doing daily avg would work better overall. This way, FFXII can have it's 10k hits in a day or whatever.. then quickly fall.. or not.. depending on how popular the other games are..
I'd store the games' database creation timestamp, and the total number of hits since then. When you query for the top 5, have sql do:
total hits / ( now - create timestamp)
and order by that..
[edit:here's a query to do it.. I think]
SELECT *,(hits / (NOW() - create_date)) avghits FROM game_table ORDER BY avghits
Posted: Sat May 29, 2004 10:49 pm
by jonas
A daily average wouldn't have the same effect though.
If every day it resets, it would probably fluctuate too much. I want it to honestly tell people which games are currently hot. What people are into.
I find this way people can get into new games they might not have thought of playing because they are really popular and usually that means the game is good (not always, but im still)
For the start I might just try the regular way I described first and then see how that is. If I need the variable to decrease over time (say 5% a day) then I guess I can add that modification later.
Posted: Sat May 29, 2004 11:02 pm
by feyd
my edit does the average without resetting a counter...
Posted: Sat May 29, 2004 11:03 pm
by jonas
Hmm I see what you mean.
Posted: Fri Jun 04, 2004 1:28 pm
by jonas
I need more opinions on this subject.
I'm looking to keep top 5 lists almost exactly like how
http://www.gamefaqs.com has their top 10s. Actually exactly like that.
The thing I need to know is how to keep some games from being overly popular so that nothing can ever beat it.
I don't want to do daily averages as Feyd suggested, I'd like to have a proper tally over time.
Posted: Fri Jun 04, 2004 10:35 pm
by d3ad1ysp0rk
clear hits each day, then check to see if the hits it got in a day were larger than any of the current top numbers.