High Score Server

From Blue Mars Developer Guidebook

Jump to: navigation, search
There are security restrictions on this article

Contents

Overview

In this example, we show how to use the Web functions to interact with a high score server. A typical setup will involve a database, server-side scripts running on a web server that update and retrieve from the database, and client interaction with those scripts over the Internet.

There are plenty of popular toolsets for setting up a server, but for this example we'll just assume the use of PHP for scripting and MySQL as the database implementation. On the client-side, of course we're interested in interacting with the server using the Lua Web functions and possibly also viewing the scores using a regular web browser. Developers familiar with web server development may want to focus on the Lua examples, but we'll go step-by-step.

Simple Example

Let's start with a really simple example - a score server that allows us to submit new player/score entries and retrieve all those scores. For now, we'll assume the client is just a web browser.

Image:HighScoreBrowser.png

From the web browser, we can submit a new player/score entry to the web server, which in turn stores it in the database. And from the web browser, we can request a display of all the scores. Note in the diagram we use bidirectional arrows for the addscore and display requests - although at a conceptual high level addscore is submitting information and display is retrieving it, by the nature of interaction on the web, we are always sending a request (URL) and receiving a page, even if the page is something like "Successful login" or "404 Page Not Found".

Database

Based on our requirements, the database contains a table with a column for the player name and a column for the player score. As viewed from PHPMyAdmin, it would look like this.

Image:Highscoredb.JPG

Note we also have a column for an auto-incrementing key, just so we can uniquely identify each row, but it's not something that we care about right now.

Server Scripts

So we have two operations to implement. We could mingle all our operations in one server-side script (and thus one URL) but for simplicity, readability, modularity, etc. we'll implement each operation in it's own script.

Each script will start with a database login, so we set that up in a script by itself, which name login.php' and include at the beginning of all our other scripts:

<?php
$server = mysql_connect('myhost','mylogin','mypassword') or die('Could not connect: ' . mysql_error());
mysql_select_db('mydatabase') or die('Could not select database');
?>

Substitute your own host, login, and passwords, of course. (As a bonus, keeping this info in a separate script allows us to keep the login info out of the all the other scripts if you're allowing others to examine them).

Now we'll start with our first fully-functional script, the one that adds a new player/score entry, which we'll name addscore.php:

<?php
require_once("login.php");
// Strings must be escaped to prevent SQL injection attack.
$name = mysql_real_escape_string($_GET['name'], $server);
$score = mysql_real_escape_string($_GET['score'], $server);
$query = "insert into sample_scores values (NULL, '$name', '$score');";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>

The script expects the player name and score to be appended in the URL, i.e. as part of an HTTP GET request (you'll see parameters like that often in web search URL's, for example), like this:

http://mygameserver.myserver.com/addScore.php?name=Joe&score=100

And then after some processing to make sure the supplied values don't contain malicious SQL, the script executes the SQL query that adds a new row with the player/score info. After submitting several new player/score entries, if we examine the database (here we use the browse feature in PHPMyAdmin), we'll see something like this

Image:highscorebrowse.JPG

Note the script doesn't output anything, so if we type in the URL above in a web browser, we'll get a blank page back. Not to worry! We should probably add a nice confirmation message.

Entering test data in a URL can get old after a while, especially since the parameters may need to be URLencoded if they include characters like spaces. So we should put together an HTML form that takes and submits the name and score parameters, and allow the browser to URLencode the parameters properly.

<html>
<body>
<FORM ACTION="http://workedon.com/addscore.php" METHOD=GET>
<P>Submit a score</p><br>
Player<input type="text" name="name"><br>
Score<input type="text" name="score"><br>
<input type="submit" value="send">
</FORM>
</body>
</html>

And then we have a nice test page:

File:Scoreform.png

Now let's just go ahead to our score-display script, which will definitely output something (that's it's purpose, after all). We'll call this script display.php:

<?php
require_once("login.php");
$query = "SELECT * FROM `sample_scores`";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results = mysql_num_rows($result); 
for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo $row['name'] . "\t" . $row['score'] . "\n";
  }
?>

Now if we execute this script view a web browser by going to its URL, http://workedon.com/display.php we'll see something like this:

Image:highscorebasic.JPG

Formatting in HTML

The result from display.php was pretty hard on the eyes. If we're going to view it in a web browser, we should really format it in HTML.

To address this, we'll create a separate display script. First, we want to use the same query we had in the plain-text display, so let's put that in a separate script we can include:

<?php
require_once("login.php");
if (isset($_GET['limit'])) {
  $limit = mysql_real_escape_string($_GET['limit'], $server);
  $query = "SELECT * FROM `sample_scores` ORDER BY score DESC LIMIT $limit";
 } else {
  $query = "SELECT * FROM `sample_scores` ORDER BY score DESC";
 }
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results = mysql_num_rows($result); 
?>

Notice we we added some stuff to the query. Since now we're concerned about presentation, let's list the score in descending order (this is a high score server, after all), so we add the ORDER BY and DESC qualifiers to return the rows in descending order of the score column.

And chances are, we want to see something like the top 10 or top 50 scores, not every single score since people started playing the game, so we allow an optional limit parameter in the URL, and if it's supplied, use that with a LIMIT qualifier to specify the number of rows returned.

For the part that really differs from the plain-text display script, we just perform the same row fetches but wrap the output with HTML table tags.

<?php
require_once("displayquery.php");
echo "<table>";
echo "<tr><th>Player</th><th>Score</th></tr>";
for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo "<tr><td>" . $row['name'] . "</td><td>" . $row['score'] . "</td></tr>\n";
  }
echo "</table>";
?>

And when we view the result in a browser, say at http://workedon.com/displayhtml.php

we get a much nicer presentation.

Image:Highscorebrowsehtml.JPG

If we do a View Source in the browser, we can see just a little HTML table formatting makes a big difference:

<table><tr><th>Player</th><th>Score</th></tr><tr><td>Golfer10008</td><td>1002</td></tr>
<tr><td>Golfer10008</td><td>1001</td></tr>
<tr><td>Golfer10123</td><td>904</td></tr>
<tr><td>Golfer10008</td><td>904</td></tr>
<tr><td>Golfer10008</td><td>902</td></tr>

</table>

Lua

Now, as Emeril would say, let's kick it up a notch. With the setup we have so far, in Blue Mars we can use the Web functions to submit a high score and also bring up a web page to view the scores.

Image:HighScoreLuaBrowser.png

In practice, the two operations may or may not happen together, but for the sake of example, let's say we submit a new score and then want to view the results immediately after. We use ARSubmitGet, a convenience function that URLEncodes the parameters before calling DownloadFile to send a new score to this server with the proper URL and ShowPageInBrowser to bring up the web page.

ARTestLeaderboard.AddScore =
{
	OnBeginState = function(self)
			  local params = {name=self.player,
                                          score=self.score};
                          ARSubmitGet(self.addscoreURL,self.resultsfile,params);

	end,
	OnUpdate = function(self,delta)
		      if Game.IsDownloading() then
			 -- should display progress with Game.GetDownloadProgress()
		      else
                        self:GotoState("Display");
		      end
		   end,
}

ARTestLeaderboard.Display =
{
	OnBeginState = function(self)
                  Game.ShowPageInBrowser(self.displayURL);
	end,
}

POST vs. GET

When you type in a URL in a web browser and go to that page, the corresponding HTTP request is known as a GET request. With GET requests, as we've seen, you append any appropriate arguments to the URL and the server-side code will know how to extract them. This is often how queries parameters are specified with a URL.

An alternative way to supply arguments is the POST request. We created a form that specified the GET method, but forms often are used to submit POST requests, which pass the parameters in the body of the HTTP request. A rule of thumb is that if the parameters are not just used to perform a query but rather information that is supplied to change or add data, then it belongs in a POST request. (GET is for getting data, POST is for posting data, get it?) It is also somewhat more secure to use POST. Logins are typically performed with POST - it doesn't look very secure if you can perform a login just by typing in the URL and including the user/password in the URL (and it's way too visible).

So we should change our addscore operation to use POST. The server-side code that extracts the parameter will have to be changed accordingly. In PHP, this would involve retrieving the arguments from the POST array instead of the GET array.

<?php
require_once("login.php");
$name = mysql_real_escape_string($_POST['name'], $server);
$score = mysql_real_escape_string($_POST['score'], $server);
$query = "insert into sample_scores values (NULL, '$name', '$score');";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>

Our HTML form just needs to have the "GET" method changed to "POST".

<html>
<body>
<FORM ACTION="http://mygameserver.com/addscore.php" METHOD=POST>
<P>Submit a score</p><br>
Player<input type="text" name="name"><br>
Score<input type="text" name="score"><br>
<input type="submit" value="send">
</FORM>
</body>
</html>

The Lua code just requires replacing ARSubmitGet with ARSubmitPost, which calls Game.DownloadFile so that the HTTP request is sent as a POST request instead of a GET request and includes the supplied "post" data in the request body.

ARTestLeaderboard.AddScore =
{
	OnBeginState = function(self)
			  local params = "name="..self.player"&score="..self.score;
			  Game.DownloadFile(self.posturl,self.resultfile,{post=params});
	end,
 

As with the our previous script that used GET, the player and score arguments are separated with ampersands, but we don't need the beginning question mark.

Formatting in XML

The next obvious step is to retrieve the display results in Lua instead of of relying on a Web browser. Then we can pass the data to a Flash movie or display it in some other custom fashion.

Image:HighScoreLua.png

We could parse the result of our original plain-text display format, much like we did in the Molecule Viewer example, but that gets tedious fast, especially if we start getting more complex sets of results. Conveniently, CryEngine2 supports a simplified XML format - if we have our server script return the data in XML, we can use ARLoadXML to load it into a Lua table without having to write a whole bunch of file-opening/reading/parsing code.

Here's another PHP script, similar to the HTML one but instead wraps the data with XML tags.

<?php
require_once("displayquery.php");
echo "<leaderboard>\n";
echo "<scores>\n";
for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo "<score player=\"" . $row['name'] . "\" score=\"" . $row['score'] . "\" />\n";
  }
echo "</scores>\n";
echo "</leaderboard>\n";
?>

If we type the script URL in a web browser - http://workedon.com/displayxml.php?limit=5 - we get something like this (you may have to do a View Source in the browser to see, since the browser may just display a blank page if it doesn't know how to interpret the content).

<leaderboard>
<scores>
<score player="" score="0" />
<score player="" score="0" />
<score player="Player0" score="122" />
<score player="Player0" score="163" />
<score player="Player0" score="131" />
</scores>
</leaderboard>

Now that we have our data in XML, we have some flexibility in choosing presentations. Some possibilities:

Golf

Now we'll move on to a more complex example, the golf game. For a golf game, we want to track player scores per hole and player scores per game, and then retrieve or derive information like top scores per hole, top scores per game, player average per hole or game, player history....

Database

We start designing the database by roughly sketching out the tables we'll need and their relationship.

Image:Golftablerelationship.png

Clearly, we need a a table for players, a table for games, which will refer to the course sections played on, each section is on a course and has a number of holes, we need a table of player scores per hole for each game and a table of player total score per game.

Now let's fill out the tables.

golf_section

Each golf game is played on a section of a golf course, each with a variable number of holes. So we have a column for the course name, section name, and number of holes, plus an auto-incrementing ID column that acts as the primary key.

Image:Golfsectiontable.JPG

If we have some per-course information and per-hole information we might want to create separate course and hole tables, but this seems good enough for now and it's convenient to have just one table since this is a table that we want to pre-populate with our golf course/section/hole information.

Image:Golfsectiontablefilled.JPG

golf_player

The remaining tables will start out empty - they will be added to every time a golf game is finished and the golf supervisor sends the game information to the golf score server.

Each player has a name and a unique ID supplied by Blue Mars, so we have those two columns in a player table, called golf_player, plus a player handicap (we'll assume for now that the golf game will be supplying or modifying the handicap). The player ID is the primary key of this table but not auto-incrementing, since we'll be supplying it from Blue Mars, and the handicap defaults to 0 (the golf game might start off by reading the handicap and adjusting it at game's end).

Image:Golfplayertable.JPG

golf_game

Each game is played on one course section, so for the game table we have an auto-incrementing ID column to identity the game (assuming the client will not be passing a game ID) and a column of section ID's to specify the corresponding course section that game is played on.

Image:Golfgametablestructure.JPG

golf_score

With our golf game, we want to each player's scores per hole in each game. So in our golf_score table we have columns for an auto-incrementing primary key, a player ID, a game ID, the hole, and the score for that hole.

Image:Golfscoretable.JPG

golf_totalscore

Finally, we have a total score table, called golf_totalscore, which lists each player's total game score for each game he or she has played.

Image:Golftotalscoretable.JPG

It has an auto-incrementing ID column as the primary key, the player ID, the game ID, and the player's total score for that game. If we didn't want to store the game scores explicitly we don't need this table (we could derive the total game scores by adding up the hole scores and figure out which players played in which games from the golf_scores table

Golf Server Scripts

Instead of just getting a single score, our server now has to process the total final results for a game. It is common now to submit (POST) complex data over HTTP in XML format, but we can stick with GET/POST variables and maybe that will be simpler (maybe). In any case, we can define the format that can be used with GET/POST to summarize game results to use GET/POST variables like this:

  • COURSE=course
  • SECTION=section
  • PLAYERS=id,name,id,name...
  • SCORES=id,hole,score,id,hole,score...
  • TOTALSCORES=id,score,id,score...
  • MODE=singleplayer/multiplayer

Note the choice of a comma is a list separator is arbitrary - it can be anything, except of course if we use GET we need to make sure it's something we can include in a URL.

The following PHP script processes this input with the following steps:

  1. retrieve all the GET variables supplied in the HTTP request (we start with GET as with the simple example so we can test it easily by typing in a URL in a web browser
  2. for the PLAYERS, SCORES and TOTALSCORES lists, we call the PHP function explode to convert them into arrays (recognizing the use of a comma as a separator)
  3. go through the list of players, either add or update them to the golf_player table
  4. using the supplied course and section name, retrieve the corresponding section ID from golf_section table
  5. add a new game to the golf_game table with the section ID - notice we lock the database so we can perform the next step before someone else adds a game
  6. retrieve the newly-generated game ID using the PHP function mysql_insert_id
  7. go through the SCORES list and add each score to the golf_score table along with the game ID
  8. go through the TOTALSCORES list and add each score to the golf_totalscores table along with the game ID
<?php
require_once("login.php");

// change to _GET to _POST when everything is working
$mode = mysql_real_escape_string($_GET['mode'], $server);
$course = mysql_real_escape_string($_GET['course'], $server);
$section = mysql_real_escape_string($_GET['section'], $server);
$players = mysql_real_escape_string($_GET['players'], $server);
$scores = mysql_real_escape_string($_GET['scores'], $server);
$totalscores = mysql_real_escape_string($_GET['totalscores'], $server);

$playerlist = explode(',',$players);
$scorelist = explode(',',$scores);
$totalscorelist = explode(',',$totalscores);

// add/update players
$i=0;
while ($i<count($playerlist)) {
  $player=$playerlist[$i++];
  $name=$playerlist[$i++];
  $query = "select * from golf_player where id='$player'";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  if (mysql_num_rows($result)==0) {
    $query = "insert into golf_player (id,name) values ($player, '$name')";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  } else {
    $query = "update golf_player set name='$name' where id=$player";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  }
 }

// get the section ID
$query = "select id from golf_section where name=\"$section\" and course=\"$course\"";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row=mysql_fetch_array($result);
$sectionid=$row['id'];

// lock game table
$query="lock tables golf_game WRITE";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// add new game
$query="insert into golf_game (mode,section) values ('$mode',$sectionid)";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$game = mysql_insert_id(); // get auto-generated game ID from last insert

// unlock game table
$query="unlock tables";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// add scores
$i=0;
while ($i<count($scorelist)) {
  $player=$scorelist[$i++];
  $hole=$scorelist[$i++];
  $score=$scorelist[$i++];
  $query = "insert into golf_score (player,hole,score,game) values ($player, $hole, $score, $game);";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
}

// add total scores
$i=0;
while ($i<count($totalscorelist)) {
  $player=$totalscorelist[$i++];
  $score=$totalscorelist[$i++];
  $query = "insert into golf_totalscore (player,score,game) values ($player, $score, $game);";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
}
?>

Now, if we submit several golf game results by entering URL's like this in a web browser:

http://workedon.com/addgolfgame.php?section=section2&course=course2&mode=singleplayer&scores=1,1,25,2,3,30&players=1,ted,2,fred&totalscores=1,4,2,19

and then check the contents of our database tables (here using PHPMyAdmin), we'll see them filled out like this:

golf_player

Image:golfplayertablefilled.JPG

golf_game

Image:golfgametablefilled.JPG

golf_score

Image:golfscoretablefilled.JPG

golf_totalscores

Image:golftotalscoretablefilled.JPG

Golf Results in HTML

Now for presentation. We'll start with some web page views for easy testing.

High Scores

Chances are that while at the beginning or end of a golf game we'll want to see comparable high scores, i.e. scores for this particular section and for this particular game mode (of course we could list scores for all modes and all section, but leave that as an exercise for the reader).

  1. course, section, game mode and limit (number of scores we want to display) from the HTTP GET/POST arguments
<?php
require_once("login.php");

// display high scores for a particular course/section/mode

// change to _GET to _POST when everything is working
$mode = mysql_real_escape_string($_GET['mode'], $server);
$course = mysql_real_escape_string($_GET['course'], $server);
$section = mysql_real_escape_string($_GET['section'], $server);
$limit = mysql_real_escape_string($_GET['limit'], $server);

echo "<h1>High Scores for course $course, section $section in $mode mode</h1>";

// get the section ID and number of holes
$query = "SELECT id,holes FROM golf_section WHERE name=\"$section\" AND course=\"$course\"";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row=mysql_fetch_array($result);
$sectionid=$row['id'];
$holes=$row['holes'];

// show the top scores for this section
echo "<h2>Top $limit total scores</h2>";
$query = "SELECT name,score,time FROM golf_player,golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid AND golf_player.id=golf_totalscore.player AND golf_game.mode='$mode' 
           ORDER BY score LIMIT $limit";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results=mysql_num_rows($result);

echo "<table border=true>";
echo "<tr><th>Player</th><th>Score</th><th>Date</th></tr>";
for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo "<tr><td>" . $row['name'] . "</td><td>" . $row['score'] . "</td><td>" . $row['time'] . "</td></tr>\n";
  }
echo "</table>";

// show average score for this section
$query = "SELECT AVG(score),COUNT(score) FROM golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results=mysql_num_rows($result);
$row = mysql_fetch_array($result);
$avg = $row['AVG(score)'];
$count=$row['COUNT(score)'];
echo "Average of $count scores: $avg";

// show the top scores for each hole

for ($h=1; $h<= $holes; $h++) {
  echo "<h2>Top $limit scores for hole $h</h2>";
  echo "<table border=true>";
  echo "<tr><th>Player</th><th>Score</th><th>Date</th></tr>";
  $query="SELECT name,score,time FROM golf_player,golf_score,golf_game WHERE golf_game.section=$sectionid AND golf_score.hole=$h AND golf_score.player = golf_player.id AND golf_score.game = golf_game.id 
           ORDER BY score LIMIT $limit"; 
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);
  for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo "<tr><td>" . $row['name'] . "</td><td>" . $row['score'] . "</td><td>" . $row['time'] . "</td></tr>\n";
  }
  echo "</table>";

  // show average score for this hole
  $query = "SELECT AVG(score), COUNT(score) FROM golf_score,golf_game WHERE golf_score.game=golf_game.id AND golf_game.section=$sectionid AND golf_score.hole=$h";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);
  $row = mysql_fetch_array($result);
  $avg = $row['AVG(score)'];
  $count=$row['COUNT(score)'];
  echo "Average of $count scores: $avg";
 }

?>

http://workedon.com/showgolfscores.php?course=course2&section=section2&mode=singleplayer&limit=3

Image:Golfhighscorebrowserview.JPG

Player Stats in HTML

In addition to showing the history of scores for the course section being played, the golf game may want to display the current player's general stats.

<?php
require_once("login.php");

// display high scores for a particular course/section/mode

// change to _GET to _POST when everything is working
$limit = mysql_real_escape_string($_GET['limit'], $server);
$player = mysql_real_escape_string($_GET['player'], $server);

// get the section ID and number of holes
$query = "SELECT name FROM golf_player WHERE id=$player";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row=mysql_fetch_array($result);
$name=$row['name'];

echo "<h1>Golf stats for player $name</h1>";

$query = "SELECT * FROM golf_section";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results=mysql_num_rows($result);
for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    $sectionid=$row['id'];
    $course=$row['course'];
    $section=$row['name'];
    $holes=$row['holes'];
    echo "<h2>Top $limit scores for Course $course Section $section </h2>";
    playerstatsforsection($sectionid,$holes,$player,$limit);
  }

function playerstatsforsection($sectionid,$holes,$player,$limit) {
  $query = "SELECT score,time FROM golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid AND golf_totalscore.player=$player 
             ORDER BY score LIMIT $limit";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);

  echo "<table border=true>";
  echo "<tr><th>Score</th><th>Date</th></tr>";
  for($i = 0; $i < $num_results; $i++)
    {
      $row = mysql_fetch_array($result);
      echo "<tr><td>" . $row['score'] . "</td><td>" . $row['time'] . "</td></tr>\n";
    }
  echo "</table>";

  // show average score for this section
  $query = "SELECT AVG(score),COUNT(score) FROM golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid AND golf_totalscore.player=$player";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);
  $row = mysql_fetch_array($result);
  $avg = $row['AVG(score)'];
  $count=$row['COUNT(score)'];
  echo "Average of $count scores: $avg";

// show the top scores for each hole

  for ($h=1; $h<= $holes; $h++) {
    echo "<h2>Top $limit scores for hole $h</h2>";
    echo "<table border=true>";
    echo "<tr><th>Score</th><th>Date</th></tr>";
    $query="SELECT score,time FROM golf_score,golf_game WHERE golf_game.id=golf_score.game AND golf_game.section=$sectionid AND golf_score.hole=$h AND golf_score.player=$player 
             ORDER BY score LIMIT $limit"; 
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    $num_results=mysql_num_rows($result);
    for($i = 0; $i < $num_results; $i++)
      {
	$row = mysql_fetch_array($result);
	echo "<tr><td>" . $row['score'] . "</td><td>" . $row['time'] . "</td></tr>\n";
      }
      echo "</table>";
    
    // show average score for this hole
    $query = "SELECT AVG(score), COUNT(score) FROM golf_score,golf_game WHERE golf_score.game=golf_game.id AND golf_game.section=$sectionid AND golf_score.hole=$h AND golf_score.player=$player";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    $num_results=mysql_num_rows($result);
    $row = mysql_fetch_array($result);
    $avg = $row['AVG(score)'];
    $count=$row['COUNT(score)'];
    echo "Average of $count scores: $avg";
    }
}

?>



http://workedon.com/showgolfplayer.php?player=1&limit=3

Image:Golfplayerstatsbrowserview.JPG

Golf Results in XML

XML High Score

Here's an XML version of the high score PHP script.

<?php
require_once("login.php");

// display high scores for a particular course/section/mode in xml

$mode = mysql_real_escape_string($_GET['mode'], $server);
$course = mysql_real_escape_string($_GET['course'], $server);
$section = mysql_real_escape_string($_GET['section'], $server);
$limit = mysql_real_escape_string($_GET['limit'], $server);

echo "<golf_scores>\n";
echo "<modes>\n";
echo "<mode name=\"$mode\">\n";

echo "<sections>\n";

// get the section ID and number of holes
$query = "SELECT id,holes FROM golf_section WHERE name=\"$section\" AND course=\"$course\"";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row=mysql_fetch_array($result);
$sectionid=$row['id'];
$holes=$row['holes'];

// show stats for this section
$query = "SELECT AVG(score),COUNT(score) FROM golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results=mysql_num_rows($result);
$row = mysql_fetch_array($result);
$avg = $row['AVG(score)'];
$count=$row['COUNT(score)'];
echo "<section  course=\"$course\" name=\"$section\" id=\"$sectionid\" limit=\"$limit\" average=\"$avg\" total=\"$count\">\n";

// show the top scores for this section
$query = "SELECT name,score,time FROM golf_player,golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid AND golf_player.id=golf_totalscore.player AND golf_game.mode='$mode' ORDER BY score LIMIT $limit";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results=mysql_num_rows($result);

echo "<scores>\n";

for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo "<score name=\"" . $row['name'] . "\" score=\"" . $row['score'] . "\" date=\"" . $row['time'] . "\"/>\n";
  }

echo "</scores>\n";


// show the top scores for each hole

echo "<holes>\n";

for ($h=1; $h<= $holes; $h++) {

  // show stats for this hole
  $query = "SELECT AVG(score), COUNT(score) FROM golf_score,golf_game WHERE golf_score.game=golf_game.id AND golf_game.section=$sectionid AND golf_score.hole=$h";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);
  $row = mysql_fetch_array($result);
  $avg = $row['AVG(score)'];
  $count=$row['COUNT(score)'];
  echo "<hole number=\"$h\" limit=\"$limit\" average=\"$avg\" total=\"$count\">\n";


  $query="SELECT name,score,time FROM golf_player,golf_score,golf_game WHERE golf_game.section=$sectionid AND golf_score.hole=$h AND golf_score.player = golf_player.id AND golf_score.game = golf_game.id ORDER BY score LIMIT $limit"; 
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);
echo "<scores>\n";
  for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    echo "<score name=\"" . $row['name'] . "\" score=\"" . $row['score'] . "\" date=\"" . $row['time'] . "\"/>\n";
  }
echo "</scores>\n";
  echo "</hole>\n";
 }
echo "</holes>\n";

echo "</modes>\n";
echo "</mode>\n";
echo "</section>\n";
echo "</sections>\n";
echo "</golf_scores>\n";
?>

Notice that although the PHP script is somewhat constrained - we expect it to be called for just one section and one game mode - we try to keep the XML format general enough to accomodate different queries, perhaps including multiple game modes or multiple sections. We don't want to have to define a new format for every single variation of otherwise similar queries, especially since we'll have to create a definition file for each one (see below). On the other hand, we want the format to be reasonably tailored to the query so it can easily be interpreted. For example, we don't want to dump the entire contents of the database into the results and force the client code to rearrange it as desired. In this case, we make some assumptions like multiple modes may be expressed in the XML data but the sections results are separated under the modes and not mingled.

XML Player Stats

As mentioned above, we could try to cram the player stats into the same XML format, considering it also has some score results, but it's potentially a different enough arrangement let's just use a new format.

<?php
require_once("login.php");

// display high scores for a particular course/section/mode

// change to _GET to _POST when everything is working
$limit = mysql_real_escape_string($_GET['limit'], $server);
$player = mysql_real_escape_string($_GET['player'], $server);

// get the section ID and number of holes
$query = "SELECT name,handicap FROM golf_player WHERE id=$player";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row=mysql_fetch_array($result);
$name=$row['name'];
$handicap=$row['handicap'];

echo "<golf_player id=\"$player\" name=\"$name\" handicap=\"$handicap\">\n";

echo "<sections>\n";
$query = "SELECT * FROM golf_section";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$num_results=mysql_num_rows($result);
for($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    $sectionid=$row['id'];
    $course=$row['course'];
    $section=$row['name'];
    $holes=$row['holes'];
    playerstatsforsection($course, $section, $sectionid,$holes,$player,$limit);
  }
echo "</sections>\n";
echo "</golf_player>\n";

function playerstatsforsection($course, $section, $sectionid,$holes,$player,$limit) {

  // show average score for this section
  $query = "SELECT AVG(score),COUNT(score) FROM golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id and golf_game.section=$sectionid and golf_totalscore.player=$player";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);
  $row = mysql_fetch_array($result);
  $avg = $row['AVG(score)'];
  $count=$row['COUNT(score)'];
  echo "<section id=\"$sectionid\" name=\"$section\" course=\"$course\" limit=\"$limit\" average=\"$avg\" total=\"$count\">\n";

  // top game scores for this section
  echo "<scores>\n";
  $query = "SELECT score,time FROM golf_totalscore,golf_game WHERE golf_totalscore.game=golf_game.id AND golf_game.section=$sectionid AND golf_totalscore.player=$player ORDER BY score LIMIT $limit";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  $num_results=mysql_num_rows($result);

  for($i = 0; $i < $num_results; $i++)
    {
      $row = mysql_fetch_array($result);
      echo "<score score=\"" . $row['score'] . "\" date=\"" . $row['time'] . "\"/>\n";
    }
  echo "</scores>\n";

// show the top scores for each hole
  echo "<holes>\n";
  for ($h=1; $h<= $holes; $h++) {
    // show average score for this hole
    $query = "SELECT AVG(score), COUNT(score) FROM golf_score,golf_game WHERE golf_score.game=golf_game.id and golf_game.section=$sectionid AND golf_score.hole=$h AND golf_score.player=$player";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    $num_results=mysql_num_rows($result);
    $row = mysql_fetch_array($result);
    $avg = $row['AVG(score)'];
    $count=$row['COUNT(score)'];
    echo "<hole number=\"$h\" limit=\"$limit\" average=\"$avg\" total=\"$count\">\n";

    echo "<scores>\n";
    $query="SELECT score,time FROM golf_score,golf_game WHERE golf_game.id=golf_score.game AND golf_game.section=$sectionid AND golf_score.hole=$h AND golf_score.player=$player ORDER BY score LIMIT $limit"; 
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    $num_results=mysql_num_rows($result);
    for($i = 0; $i < $num_results; $i++)
      {
	$row = mysql_fetch_array($result);
	echo "<score score=\"" . $row['score'] . "\" date=\"" . $row['time'] . "\"/>\n";
      }
    echo "</scores>\n";
  echo "</hole>\n";
    }
  echo "</holes>\n";
  echo "</section>\n";
}
?>

?>

http://workedon.com/showgolfplayerxml.php?player=1&limit=3

<golf_player id="1" name="ted" handicap="0">
<sections>
<section id="1" name="section1" course="course1" limit="3" average="" total="0">
<scores>
</scores>
<holes>
<hole number="1" limit="3" average="" total="0">
<scores>
</scores>
</hole>
</holes>
</section>
<section id="2" name="section2" course="course2" limit="3" average="4.0000" total="2">
<scores>
<score score="4" date="2009-09-17 20:33:03"/>
<score score="4" date="2009-09-17 20:33:17"/>
</scores>

<holes>
<hole number="1" limit="3" average="25.0000" total="8">
<scores>
<score score="25" date="2009-09-17 19:13:25"/>
<score score="25" date="2009-09-17 19:32:11"/>
<score score="25" date="2009-09-17 19:36:33"/>
</scores>
</hole>
<hole number="2" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="3" limit="3" average="" total="0">
<scores>
</scores>
</hole>
</holes>

</section>
<section id="3" name="section3" course="course3" limit="3" average="" total="0">
<scores>
</scores>
<holes>
<hole number="1" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="2" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="3" limit="3" average="" total="0">
<scores>
</scores>
</hole>

<hole number="4" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="5" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="6" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="7" limit="3" average="" total="0">
<scores>
</scores>
</hole>
<hole number="8" limit="3" average="" total="0">

<scores>
</scores>
</hole>
<hole number="9" limit="3" average="" total="0">
<scores>
</scores>
</hole>
</holes>
</section>
</sections>
</golf_player>

Lua

Image:GolfHighScore.png

Sending Golf Results in Lua

To send the results of a Golf game in Lua, we construct the result parameters in the same form as the hand-submitted example. We take the opportunity now to use POST arguments instead of GET (so instead of appending them to the URL we supply them with the post option in DownloadFile).

ARTestLeaderboard.AddGolfGame =
{
	OnBeginState = function(self)
			  self.playernum=self.playernum+1;
			  local params = {course="course1",
                                          section="section1",
                                          players="4,nancy cardigan,6,winnie the pooh",
                                          mode="multiplayer",
                                          totalscores="4,90,6,20",
                                          scores="4,1,10,6,3,11";
			  ARSubmitPost(self.Properties.addgolfgame,
					    System.AdjustFileName(self.Properties.addresults,0),params);

	end,
	OnUpdate = function(self,delta)
		      if Game.IsDownloading() then
			 ARPrintReset();
			 ARDownloadProgress();
		      else
			 self:PrintTest("Added new golf score with POST");
			 ARDownloadStats();
		      end
		   end,
}

After executing the script we can see the new players listed in golf_player table.

Image:Golfplayersposted.JPG

Reading Golf Scores in Lua

<?xml version="1.0"?>

<Definition root="golf_scores">

	<Array name="modes" elementName="mode">
	<Property name="name" type="string"/>
		<Array name="sections" elementName="section">
		<Property name="id" type="int"/>
		<Property name="course" type="string"/>
		<Property name="limit" type="int"/>
		<Property name="total" type="int"/>
		<Property name="name" type="string"/>
		<Property name="average" type="string"/>
			<Array name="scores" elementName="score" optional="1">
			<Property name="score" type="int"/>
			<Property name="date" type="string"/>
			<Property name="name" type="string"/>
			</Array>
			<Array name="holes" elementName="hole">
			<Property name="number" type="int"/>
			<Property name="limit" type="int"/>
			<Property name="total" type="int"/>
			<Property name="average" type="string"/>
				<Array name="scores" elementName="score" optional="1">
				<Property name="score" type="int"/>
				<Property name="date" type="string"/>
				<Property name="name" type="string"/>
				</Array>
			</Array>
		</Array>
	</Array>
</Definition>

ARTestLeaderboard.GetGolfScores =
{
	OnBeginState = function(self)
			  local params = {mode="singleplayer",
                                          section="section2",
                                          course="course2",
                                          limit=self.Properties.limit};
			  ARSubmitGet(self.Properties.golfscoresxml,self.Properties.xmlresults,params);

	end,
	OnUpdate = function(self,delta)
		      if Game.IsDownloading() then
			 ARPrintReset();
			 ARDownloadProgress();
		      else
			 self:PrintTest("Retrieved golf scores");
			 ARDownloadStats();
		      end
		   end,
}

ARTestLeaderboard.ReadGolfScores =
{
	OnBeginState = function(self)
			  self.xmltable = ARLoadXML(self.Properties.golfscoresdef, self.Properties.xmlresults);
		       end,
	OnUpdate = function(self,delta)
		      self:PrintTest("Showing golf scores ");
		      for i,mode in ipairs(self.xmltable.modes) do
			 ARPrint("Mode: "..mode.name);
			 for s,section in ipairs(mode.sections) do
				 ARPrint("Section "..section.name.." Course "..section.course.." average: "..section.average.." total: "..section.total.." top "..section.limit);
				 for s,score in ipairs(section.scores) do
				    ARPrint("Score: "..score.score.." Date: "..score.date.." player: "..score.name);
				 end
				 for h,hole in ipairs(section.holes) do
				    ARPrint("Hole: "..hole.number.." Average: "..hole.average.." total: "..hole.total.." top "..hole.limit);
				    for j,score in ipairs(section.scores) do
				       ARPrint("Score: "..score.score.." Date: "..score.date.." score: "..score.name);
				    end
				 end
			      end
		      end
		   end,
}

Image:Golfscoresluaview.JPG

Reading Golf Player Stats in Lua

<?xml version="1.0"?>

<Definition root="golf_player">

    <Property name="id" type="int" />
    <Property name="name" type="string" />
    <Property name="handicap" type="int" />
  
  <Array name="sections" elementName="section">
	<Property name="id" type="string"/>
	<Property name="course" type="string"/>
	<Property name="limit" type="int"/>
	<Property name="total" type="int"/>
	<Property name="name" type="string"/>
	<Property name="average" type="string"/>
	<Array name="scores" elementName="score">
	<Property name="score" type="int"/>
	<Property name="date" type="string"/>
	</Array>
	<Array name="holes" elementName="hole">
	<Property name="number" type="int"/>
	<Property name="limit" type="int"/>
	<Property name="total" type="int"/>
	<Property name="average" type="string"/>
	<Array name="scores" elementName="score">
	<Property name="score" type="int"/>
	<Property name="date" type="string"/>
	</Array>
	</Array>
  </Array>
</Definition>

The Lua code that downloads the XML data, loads it, and prints it.

ARTestLeaderboard.GetGolfPlayerStats =
{
	OnBeginState = function(self)
			  local params = "player=1&limit="..self.Properties.limit;
			  ARSubmitGet(self.Properties.golfplayerxml,self.Properties.xmlresults,params);

	end,
	OnUpdate = function(self,delta)
		      if Game.IsDownloading() then
			 ARPrintReset();
			 ARDownloadProgress();
		      else
			 self:PrintTest("Retrieved player stats");
			 ARDownloadStats();
		      end
		   end,
}

ARTestLeaderboard.ReadGolfPlayerStats =
{
	OnBeginState = function(self)
			  self.xmltable = ARLoadXML(self.Properties.playerdef, self.Properties.xmlresults);
		       end,
	OnUpdate = function(self,delta)
		      self:PrintTest("Showing stats for player "..self.xmltable.name.." id:"..self.xmltable.id.. " handicap: "..self.xmltable.handicap);
		      for i,section in ipairs(self.xmltable.sections) do
			 ARPrint("Section "..section.name.." Course "..section.course.." average: "..section.average.." total: "..section.total.." top "..section.limit);
			 for j,score in ipairs(section.scores) do
			    ARPrint("Score: "..score.score.." Date: "..score.date);
			 end
			 for h,hole in ipairs(section.holes) do
			    ARPrint("Hole: "..hole.number.." Average: "..hole.average.." total: "..hole.total.." top "..hole.limit);
			    for j,score in ipairs(section.scores) do
			       ARPrint("Score: "..score.score.." Date: "..score.date);
			    end
			 end
		      end
		   end,
}

Image:Golfplayerstatsluaview.JPG

Problems with this wiki page? Contact us either by: Support Email or Support Ticket System

Blue Mars Guidebook Privacy Policy
Blue Mars Guidebook Community Guidelines

Personal tools