Best way to keep track of section popularity?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Best way to keep track of section popularity?

Post 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
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SELECT * FROM games_table ORDER BY gamepop DESC LIMIT 5

I think..

[edit: beat me to it.. sorta]
Last edited by feyd on Sat May 29, 2004 10:02 pm, edited 1 time in total.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post 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.
Last edited by jonas on Sat May 29, 2004 10:08 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
Last edited by feyd on Sat May 29, 2004 10:58 pm, edited 1 time in total.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

my edit does the average without resetting a counter...
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

Hmm I see what you mean.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post 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.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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.
Post Reply