Thursday, May 17, 2012

Adding and Viewing High Scores Part 2

Introduction

The second part of this post talks about retrieving the high scores lists from the database and displaying it in a user-friendly way. If you haven't already, you should read part 1 of this post.

Part 2: Viewing High Scores

As described briefly in the first part, the high scores page contains two main parts. We'll start with the first.

User Best Five Scores

The user's best 5 scores are the first section.

As before, we need to get the user_id from the user table. This is achieved relatively easily by building the SQL in PHP in the following way:

if (!empty($_COOKIE['user'])) {
 $result = executeQuery
  ('select user_id from user where username ="' . $_COOKIE['user'] . '"');
}

// getting the user's id based on their cookie>username
if ($result) {
 while ($row = mysql_fetch_array($result)) {
  $user_id = $row['user_id'];
 }
}

Once the user_id is attained, an SQL query needs to be built to get the user's best 5 scores:

$scores = executeQuery
('select score from user_score where user_id = ' 
. $user_id . ' order by score desc limit 0, 5');

The above SQL query will return the top 5 in the correct order automatically. Next, we need to display it. I opted to build an HTML table using some clever PHP.

$count = 0;
if (mysql_num_rows($scores) > 0) {
 echo '<table id="highscores-table">';
 echo '<tr>';
 echo '<th>&nbsp;</th>';
 echo '<th>Score</th>';
 echo '</tr>';
 if ($scores) {
  while ($row = mysql_fetch_array($scores)) {
   echo '<tr>';
   echo '<td>';
   echo++$count;
   echo '</td><td>';
   echo $row['score'];
   echo '</td>';
   echo '</tr>';
  }
 }
 echo '</table>';
} else {
 echo "<em>You don't have any high scores yet.</em>";
}

The end result looks like this:

 

Global Top 10

Underneath the User Top 5, there is a Global Top 10 list, displaying the top 10 scores from all the users of the web application.

The SQL required to get the global top 10 is relatively straightforward, with one join required.

$global_high_scores = executeQuery
('select u.username, s.score from user u, user_score s where u.user_id = s.user_id order by s.score desc limit 0, 10');

Once again, I opted to employ some clever PHP to build the table dynamically. The code is below:

$ranking = 0;
while ($row = mysql_fetch_array($global_high_scores)) {
 $ranking++;
 echo '<tr>';
 echo '<td>';
 echo $ranking;
 echo '</td>';
 echo '<td>';
 echo $row['username'];
 echo '</td>';
 echo '<td>';
 echo $row['score'];
 echo '</td>';
 echo '</tr>';
}

The end result looks like this:

Conclusion and Source Code Download

That concludes the High Scores section of the web application. This post detailed some clever techniques on building good SQL statements, and HTML tables using PHP code.

You can download the source code I used in this post below.

No comments:

Post a Comment