PHP Leaderboard - Display Rank in HTML table

[Tux]

Hi all,

 

I am currently working on a Trophy Leaderboard for the PS3 Forums.

 

I have most of it done but am stuck on something. All the data is stored in a MYSQL database, the one thing that is not stored is a rank as this is constantly changing when users add trophies.

 

I am looking for away with php to number the html table accordingly, so the first row would be 1, second 2 ect.

At the moment it displays correctly, so highest rank in the first row ect. by sorting it on the two fields of importance, I just need to get those rows numbered which works fine with count++, so that kinda worked except for 2 parts:

 

1. The table will be split over several pages, with the count++ the moment you hit page 2 the first entry is listed as number 1 again which is incorrect, if the second page would list 21 - 40 the first entry would of course have 21 as rank depending on point 2 below.

 

2. With the count++ it doesn't take in account that multiple users can have the same rank, so if TR_TOTAL_TROPHIES and TR_TROPHY_LEVEL are identical for 2 or more users they both need to have the same rank.

 

This is what i have so far, sorry if for some of you it looks like bad coding, i'm a novice at this and it's done through Dreamweaver:

 

   1.
      <?php require_once('Connections/ps3.php'); ?>
   2.
      <?php
   3.
      if (!function_exists("GetSQLValueString")) {
   4.
      function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
   5.
      {
   6.
        if (PHP_VERSION < 6) {
   7.
          $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
   8.
        }
   9.
       
  10.
        $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
  11.
       
  12.
        switch ($theType) {
  13.
          case "text":
  14.
            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  15.
            break;    
  16.
          case "long":
  17.
          case "int":
  18.
            $theValue = ($theValue != "") ? intval($theValue) : "NULL";
  19.
            break;
  20.
          case "double":
  21.
            $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
  22.
            break;
  23.
          case "date":
  24.
            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  25.
            break;
  26.
          case "defined":
  27.
            $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  28.
            break;
  29.
        }
  30.
        return $theValue;
  31.
      }
  32.
      }
  33.
       
  34.
      $currentPage = $_SERVER["PHP_SELF"];
  35.
       
  36.
      $maxRows_LbLeaderboard = 10;
  37.
      $pageNum_LbLeaderboard = 0;
  38.
      if (isset($_GET['pageNum_LbLeaderboard'])) {
  39.
        $pageNum_LbLeaderboard = $_GET['pageNum_LbLeaderboard'];
  40.
      }
  41.
      $startRow_LbLeaderboard = $pageNum_LbLeaderboard * $maxRows_LbLeaderboard;
  42.
       
  43.
      mysql_select_db($database_ps3, $ps3);
  44.
      $query_LbLeaderboard = "SELECT * FROM trophies ORDER BY TR_TROPHY_LEVEL DESC, TR_TOTAL_TROPHIES DESC";
  45.
      $query_limit_LbLeaderboard = sprintf("%s LIMIT %d, %d", $query_LbLeaderboard, $startRow_LbLeaderboard, $maxRows_LbLeaderboard);
  46.
      $LbLeaderboard = mysql_query($query_limit_LbLeaderboard, $ps3) or die(mysql_error());
  47.
      $row_LbLeaderboard = mysql_fetch_assoc($LbLeaderboard);
  48.
       
  49.
      if (isset($_GET['totalRows_LbLeaderboard'])) {
  50.
        $totalRows_LbLeaderboard = $_GET['totalRows_LbLeaderboard'];
  51.
      } else {
  52.
        $all_LbLeaderboard = mysql_query($query_LbLeaderboard);
  53.
        $totalRows_LbLeaderboard = mysql_num_rows($all_LbLeaderboard);
  54.
      }
  55.
      $totalPages_LbLeaderboard = ceil($totalRows_LbLeaderboard/$maxRows_LbLeaderboard)-1;
  56.
       
  57.
      $queryString_LbLeaderboard = "";
  58.
      if (!empty($_SERVER['QUERY_STRING'])) {
  59.
        $params = explode("&", $_SERVER['QUERY_STRING']);
  60.
        $newParams = array();
  61.
        foreach ($params as $param) {
  62.
          if (stristr($param, "pageNum_LbLeaderboard") == false &&
  63.
              stristr($param, "totalRows_LbLeaderboard") == false) {
  64.
            array_push($newParams, $param);
  65.
          }
  66.
        }
  67.
        if (count($newParams) != 0) {
  68.
          $queryString_LbLeaderboard = "&" . htmlentities(implode("&", $newParams));
  69.
        }
  70.
      }
  71.
      $queryString_LbLeaderboard = sprintf("&totalRows_LbLeaderboard=%d%s", $totalRows_LbLeaderboard, $queryString_LbLeaderboard);
  72.
      ?> 

 

This is the HTML table displaying the data:

 

<?php do { ?>

      <tr align="center" class="trophiesTD">

        <td>RANK TO DISPLAY HERE</td>

        <td><?php echo $row_LbLeaderboard['TR_PSNID']; ?></td>

        <td><?php echo $row_LbLeaderboard['TR_BRONZE_TROPHIES']; ?></td>

        <td><?php echo $row_LbLeaderboard['TR_SILVER_TROPHIES']; ?></td>

        <td><?php echo $row_LbLeaderboard['TR_GOLD_TROPHIES']; ?></td>

        <td><?php echo $row_LbLeaderboard['TR_PLATINUM_TROPHIES']; ?></td>

        <td><?php echo $row_LbLeaderboard['TR_TOTAL_TROPHIES']; ?></td>

        <td><?php echo $row_LbLeaderboard['TR_TROPHY_LEVEL']; ?></td>

      </tr>

      <?php } while ($row_LbLeaderboard = mysql_fetch_assoc($LbLeaderboard)); ?> 

 

 

Could someone help me with this please, i have no clue how to do this.

 

Thanks in advance

[Tux]

Could any help with this please?

 

:'(