Display users rank in leaderboard table php mysql?

Question

I have created a quiz page that stores a logged in users quiz result to a leaderboard.

I have a table called members with the columns user and quiz_score. I have a leaderboard printing out depending on the result of a quiz taken by the logged in user. I am having problems displaying the specific users position in the leaderboard on their profile page. I would also like to attach a medal (e.g jpeg image) if they are in the top three. here is the code I have so far for the leaderboard:

    <?php

require_once 'header.php';
    // Send variables for the MySQL database class.
    $database = mysql_connect('localhost', 'root', 'password') or die('Could not connect: ' . mysql_error());
    mysql_select_db('robinsnest') or die('Could not select database');

    $query = "SELECT * FROM `members` ORDER by `quiz_score` DESC LIMIT 10";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    $num_results = mysql_num_rows($result);  

    echo '<div class="container marketing">
        <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
          <table class="gradienttable">
  <tr>
    <th>Position</th>
    <th>User Name</th>      
    <th>Score</th>

  </tr>';

    for($i = 1; $i <= $num_results; $i++)
    {
         $row = mysql_fetch_array($result);

         echo "<tr>
    <td>".$i."</td>
    <td>".$row['user']."</td>       
    <td>".$row['quiz_score']."</td>

  </tr>";
    }

echo "</table>
        </div>
            </div>";
echo"<hr class='featurette-divider'>";
    echo '            <footer>
                <p class="pull-right"><a href="#">Back to top</a></p>
                <p>2015 Students-NCI, &middot; <a href="#">Privacy</a> &middot; <a href="#">Terms</a></p>
            </footer>';
?>
</body>
</html>

And the code I have for printing out the user score so far:

    <?php
require_once 'header.php';
$database = mysql_connect('localhost', 'root', 'password') or die('Could not connect: ' . mysql_error());
    mysql_select_db('robinsnest') or die('Could not select database');

    $query = "SELECT  user, quiz_score, FIND_IN_SET( quiz_score, (    
SELECT GROUP_CONCAT( quiz_score
ORDER BY quiz_score DESC ) 
FROM members )
) AS rank
FROM members
WHERE user =  '$user';";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    $num_results = mysql_num_rows($result);  

     echo "<p>".$query."</p>";
echo"<hr class='featurette-divider'>";

    echo '            <footer>
                <p class="pull-right"><a href="#">Back to top</a></p>
                <p>2015 Students-NCI, &middot; <a href="#">Privacy</a> &middot; <a href="#">Terms</a></p>
            </footer>';
?>
</body>
</html>

Is not printing out the position of the logged in user just the string of the sql query. I have tried out a few different things but have had no success.

Any help would be greatly appreciated.

Answer 1

Note:

• I removed your for loop() and instead use a while loop() for your query. I know you loop it by the number of results of the query, but it will only get the value of the first row. Maybe it is feasible if you assign the current number of the loop and attach it to the variable as array.
• I used a counter named $ranking that will increment on every loop (removed the $num_results, for you don't need it anymore).
• You are mixing PHP and HTML the wrong way. Enclosed PHP by ?> before proceeding with an HTML.
• Cleaned up some of your codes.
• What is the purpose of your second given code? If you are already trying to display the data in your first code? But I still try to display the data (if the query is correct) by fetching the data and using while loop.
• Make sure that your queries are correct (credentials of your connection, database name, table name, column name, etc.)

Your fixed code:

 <?php

   require_once 'header.php';
  // Send variables for the MySQL database class.
  $database = mysql_connect('localhost', 'root', 'password') or die('Could not connect: ' . mysql_error());
  mysql_select_db('robinsnest') or die('Could not select database');

  $query = "SELECT * FROM `members` ORDER by `quiz_score` DESC LIMIT 10";
  $result = mysql_query($query) or die('Query failed: ' . mysql_error());

  $num_results = mysql_num_rows($result);  
  $ranking = 1;

?>

  <div class="container marketing">
    <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
      <table class="gradienttable">
        <tr>
          <th>Position</th>
          <th>User Name</th>      
         <th>Score</th>
        </tr>
  <?php
         while($row = mysql_fetch_array($result)){
           ?>
           <tr>
             <td><?php echo $ranking; ?></td>
             <td><?php echo $row['user']; ?></td>       
             <td><?php echo $row['quiz_score']; ?></td>
           <?php
             $ranking = $ranking + 1; /* INCREMENT RANKING BY 1 */
           ?>
           </tr>
       <?php
         } /* END OF WHILE LOOP */
       ?>

      </table>
    </div>
  </div>

<hr class="featurette-divider">
  <footer>
    <p class="pull-right"><a href="#">Back to top</a></p>
    <p>2015 Students-NCI, &middot; <a href="#">Privacy</a> &middot; <a href="#">Terms</a></p>
  </footer>

</body>
</html>

Your second given code:

<?php
  require_once 'header.php';
  $database = mysql_connect('localhost', 'root', 'password') or die('Could not connect: ' . mysql_error());
  mysql_select_db('robinsnest') or die('Could not select database');

  $query = "SELECT  user, quiz_score, FIND_IN_SET( quiz_score, (    
  SELECT GROUP_CONCAT( quiz_score
  ORDER BY quiz_score DESC ) 
  FROM members )
  ) AS rank
  FROM members
  WHERE user =  '$user';";

    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    $num_results = mysql_num_rows($result);  

    while($row = mysql_fetch_array($result)){
?>    
      <p><?php echo $row['user']." - ".$row['quiz_score']; ?></p>
  <?php
    } /* END OF WHILE LOOP */
  ?> 

  <hr class="featurette-divider">

  <footer>
    <p class="pull-right"><a href="#">Back to top</a></p>
    <p>2015 Students-NCI, &middot; <a href="#">Privacy</a> &middot; <a href="#">Terms</a></p>
  </footer>';

</body>
</html>

Recommendation:

• You should be using prepared statement instead of deprecated mysql_* functions to prevent SQL injections.