Game Stat Tracker

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Game Stat Tracker

Post by frokkle »

I created a website for the MMPORPG, Runescape. The main purpose of the site is to look up the stats of a list of users daily and record the difference in said stats. One of the reason I'm putting this code up for critique is a) I've noticed a very substantial decrease in speed now that the list of users is ~150, so slow even that the code apparently doesn't get around to updating all of teh users. b) Looking at other's code I realize I don't always (or sometimes ever) implement the most effective procedure for doing something.

Code: Select all

 
$link = mysql_connect("host", "user", "pass");
mysql_connect("host", "user", "pass") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
$query = "SELECT * FROM lUsers";  //load all of the names of users to be tracked
$result = mysql_query($query) or die(mysql_error());
$i=0;
while($row = mysql_fetch_array($result)){ //loop through users
$perl[$i] = $row['lName']; //array containing names of the users
$i++;
}
mysql_close($link);
for ( $x = 0; $x < count($perl); $x++ ) { //loop through the users
$tUser = $perl[$x]; //temporary variable for the user
$page = sock( $tUser); //function that loads the stat data into a array
$skills = seed($page); //function that sorts all the data
for ( $i=0; $i<25; $i++ ) { //loop through skill columns
    $e=0;
    mysql_connect("host", "user", "pass") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    $query1 = "SELECT * FROM $tUser ORDER BY $tUser.`Time` ASC"; //select the most all data entries for computation for the user and sorts by time
    $result1 = mysql_query($query1) or die(mysql_error());
    while($row1 = mysql_fetch_array($result1)){ //place data
    $erl[$i][$e] = $row1[$names[$i]]; //places the data from each column in the users table into an array
    $e++;
    }
    mysql_close($link);
    }
    for ( $i=0; $i<25; $i++ ) {
    $peeve[$i] = explode(",",$erl[$i][count($erl[$i])-1]); //breaks the data string up into the actual components
    $neeve[$i] = explode(",",$skills[$i]); //explodes the data string in the new stat array
    $rank = $neeve[$i][0] - $peeve[$i][0]; //change in rank
    $xp = $neeve[$i][2] - $peeve[$i][2]; //change in expereince
    $xpp = round(($neeve[$i][2] - $peeve[$i][2]) / $peeve[0][2],5); //change in experience as % of total
    $psas = round($neeve[$i][2] / $neeve[0][2],5); //% of skill as all skills
    $sumt = $skills[$i].",".$rank.",".$xp.",".$xpp.",".$psas; //add all the data back into a data string
    $skills[$i] = $sumt;
    }
    $time = time();
    mysql_connect("host", "user", "pass") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
mysql_query("INSERT INTO $tUser 
(Overall, Attack, Defence, Strength, Hitpoints, Ranged, Prayer, Magic, Cooking, Woodcutting, Fletching, Fishing, Firemaking, Crafting, Smithing, Mining, Herblore, Agility, Thieving, Slayer, Farming, Runecraft, Hunter, Construction, Summoning, Time) VALUES('$skills[0]','$skills[1]','$skills[2]','$skills[3]','$skills[4]','$skills[5]','$skills[6]','$skills[7]','$skills[8]','$skills[9]','$skills[10]','$skills[11]','$skills[12]','$skills[13]','$skills[14]','$skills[15]','$skills[16]','$skills[17]','$skills[18]','$skills[19]','$skills[20]','$skills[21]','$skills[22]','$skills[23]','$skills[24]','$time') ") 
or die(mysql_error()); 
mysql_close($link);
}
Last edited by Benjamin on Wed May 06, 2009 8:15 pm, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
Gabriel
Forum Commoner
Posts: 41
Joined: Wed May 06, 2009 8:12 pm
Location: Las Vegas

Re: Game Stat Tracker

Post by Gabriel »

Is this all one script? Reading your code, you have multiple connections and disconnections to MySQL which is unnecessary. You can use the same connection to execute multiple queries. It also looks like you have a new table for every account. This would explain the slow response seeing how you're running several queries for every account. If you put all the data into one table and restructure the database a little, you should get a lot more speed.

If this script is run every time someone accesses your page, you may want to consider caching and only updating stats every half-hour or so. To add on to that, you could also use pagination to show only 25 or more players at a time. I'm only glancing, so no specifics.
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Re: Game Stat Tracker

Post by frokkle »

This page is run on a cron job once every 24 hours so no worries there.
As for reordering the data, in order to fit every users data on one table I can only think of two possible solutions. First off, each of the 25 skills that need to be calculated for each have seven values. So this means each entry will provide for 175 different values, which when viewed would need to be broken up.

1) Column 1 - UserName, Column 2,3,4.... Individual dates.
OR
2) Create a table for each date, Column 1 - User Name, Column2,3,4... Individual skill data.

Option 1 would condense all data for a user on that day into one entry, so this is the one I'm leaning towards. Option 2 would let the data be spread out a little more but require a new table everyday.

So I guess the question I'm asking is which of these would provide the best results if my goal is time?
User avatar
Gabriel
Forum Commoner
Posts: 41
Joined: Wed May 06, 2009 8:12 pm
Location: Las Vegas

Re: Game Stat Tracker

Post by Gabriel »

My idea would be to have a table with their skills and a timestamp of when those skills were calculated. That way, you can have them in all in one table. Calculate the difference between the earliest and latest skills and move on from there. Then in your cron, delete the old entries.

Edit: I guess that would be option 1.
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Re: Game Stat Tracker

Post by frokkle »

So far I'm thinking of this.

(Also, one aspect of my site is that it keeps the old data so you can see your gains over periods of time)

Table AllData
Username-|-Date 1-----------------|-Date 2-|...
Bob-------|v1a,...v1g;...;v25a,v25g |-.........-|...

With 7 values of a skill sepereated by a , and the 25 skill data strings seperated by a ;
User avatar
Gabriel
Forum Commoner
Posts: 41
Joined: Wed May 06, 2009 8:12 pm
Location: Las Vegas

Re: Game Stat Tracker

Post by Gabriel »

It would be more efficient to have only one skill value and a timestamp for each entry. This way, you could limit the retrieval to only two (or however many you want) recent skill retrievals, use MySQL's functions of sorting and retrieving to your advantage, and keep everything organized:
username|skill x|skill y|skill z|timestamp
Again, you'd have everything organized, skill differences and rankings could be easily calculated, and your database can be easily pruned of outdated data with a query as simple as:

Code: Select all

DELETE FROM skills WHERE timestamp < 'some date'
With having multiple skills in one column, you have to use more PHP code to parse everything, add new skill totals, remove old ones, etc. Plus, using this method can allow you to know the exact date and time the information was retrieved.
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Re: Game Stat Tracker

Post by frokkle »

When I access the data, I do so for two reasons.

A) To display gains per day for the past week per skill
B) To display gains from the last day for all skills.

So if I were to store the data the way you mentioned, I would need a way to retrieve the past 7 entries for X user. Any suggestions?
User avatar
Gabriel
Forum Commoner
Posts: 41
Joined: Wed May 06, 2009 8:12 pm
Location: Las Vegas

Re: Game Stat Tracker

Post by Gabriel »

You would use a query similar to:

Code: Select all

SELECT * FROM skills WHERE user = 'username' LIMIT 7
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Re: Game Stat Tracker

Post by frokkle »

would it need to be in ascending or descending?
User avatar
Gabriel
Forum Commoner
Posts: 41
Joined: Wed May 06, 2009 8:12 pm
Location: Las Vegas

Re: Game Stat Tracker

Post by Gabriel »

That all depends on what you want. If you're looking for the latest entries, you want to sort by timestamps in descending order.
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Re: Game Stat Tracker

Post by frokkle »

Back again, I used the format we discussed earlier and know I've realized through googling the problems others have is a lack of indexing. And so I was wondering what (a) this is and (b) the correct way to go about doing it.
User avatar
Gabriel
Forum Commoner
Posts: 41
Joined: Wed May 06, 2009 8:12 pm
Location: Las Vegas

Re: Game Stat Tracker

Post by Gabriel »

Yes, I almost forgot. You'll want to add an index to the timestamp field.

What an index does is tell MySQL to store this field in memory to retrieve it faster. So since your queries will be searching for entries from specific dates, MySQL will search through them faster than other fields and return results quicker. There's a lot to explain so I suggest reading this article which should help you understand indexes.
frokkle
Forum Newbie
Posts: 7
Joined: Wed May 06, 2009 7:13 pm

Re: Game Stat Tracker

Post by frokkle »

Actually, it searches for all entries for a user then sorts those by the timestamp, so wouldn't it be better to use on the name column?

I wanted to test the updating code (where it creates a new entry for each user for their gains from the previous day). To do this I loaded up the userlist with ~500 names to track. When I ran the updating code it didn't return anything when it should of returned "Done" upon completion and when I checked MySQL only a few of the accounts. So this brought to mind a few other questions.

1) What's the difference between a Primary and a Index on a row?

2) Since I expect my site to eventually be handling daily data entries for 10,000-20,000 users, is it still wise to attempt to store it all on one table, or perhaps to split each days data entries into it's own table?

Lastly, what exactly is the cause for "MySql has gone away"?
ldougherty
Forum Contributor
Posts: 103
Joined: Sun May 03, 2009 11:39 am

Re: Game Stat Tracker

Post by ldougherty »

"Lastly, what exactly is the cause for "MySql has gone away"?"

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

"The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection."
Post Reply