-2

I want to ask, how to create a rank from a table of scores that I had from database? For example, the table of database has student_id, course_id, scores. I have create a php code to get these scores and accummulate it as Final Score. Then, I need to create who get the first rank, second, and more. Here's my sample code:

 $q = mysql_query("SELECT * FROM tbstudent");
while($r = mysql_fetch_array($q))
{
   $q2 = mysql_query("SELECT * FROM tbscores WHERE student_id = '".$r['student_id']."'");
   $total = 0;
   while($r2 = mysql_fetch_array($q2))
   {
      $total += $r2['scores'];
   }
   echo "<tr><td>$total</td><td>/* for rank*/</td></tr>";
}

So, I have get the total scores, but how to get the rank for these student? For example my result right now is (name, scores, rank):

  1. Ryan - 235
 2. Jack - 450
 3. Dave - 140
 4. Levi - 330
 5. Kens - 350

And what I want to is like these:

  1. Dave - 235 - 4
 2. Jack - 450 - 1
 3. Levi - 140 - 5
 4. Kens - 330 - 3
 5. Ryan - 350 - 2

Don't give me answer to get it from database using AVERAGE, SUM, etc, because I have another calculation for the scores. So anyone know how to create it with PHP?

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Cross Vander
  • 2,077
  • 2
  • 19
  • 33
  • 2
    Please don't tell us what can and cannot provide by way of an answer. Also I don't understand how rank relates to scores, but I suppose we should expect such a state of affairs – Strawberry Nov 30 '15 at 00:18
  • And [do not use mysql_](http://stackoverflow.com/a/12860046/870729). Use PDO or mysqli instead. – random_user_name Nov 30 '15 at 00:31
  • @strawberry rank related to scores, the highest the scores, the lowest the rank. As You could see from my example, Dave got rank 4 because his score 235, and Jack get first rank because he got the highest score. – Cross Vander Nov 30 '15 at 00:52
  • @cale_b As I write above, it's my sample code, don't tell me about PDO etc, because I already use that at my real program. I just want to simplify the code so anyone can (or i hope) understand my code – Cross Vander Nov 30 '15 at 00:53
  • this can be done in PHP only, or entirely in mysql. The choice is yours – Drew Nov 30 '15 at 00:53
  • Ryan's score is higher than kens' but he got a higher rank – Strawberry Nov 30 '15 at 00:58
  • I **will** tell you, and **everyone else who sees this question** that using mysql is dangerous and not suggested. You are very bossy for someone who is asking for help. – random_user_name Nov 30 '15 at 01:00
  • By which cale_b means PHP's MySQL api. Anyway, the code is a mess. But that's another story. – Strawberry Nov 30 '15 at 01:13
  • @Drew How to do that? – Cross Vander Nov 30 '15 at 02:00
  • @Strawberry Sorry, Typos. – Cross Vander Nov 30 '15 at 02:00
  • @cale_b sorry to hurt You, but as I wrote above, I just want to simplify but many people like You just comment about PDO, mysqli, bad code, not a good code.. But they're only comment it, not answering it. – Cross Vander Nov 30 '15 at 02:01
  • 1
    if I write it, then I have to explain it, and you have to maintain it, and it takes an hour. Seems there are enough examples out there for mysql-only querying for ranking. So I would suggest go with your comfort zone. If that is PHP-only below, I would steer that way. But any serious effort at research and this question isn't even here :P – Drew Nov 30 '15 at 02:11
  • @Drew it's Okay, just give me a logic (using array or what), and I will try to create it.. Thanks before – Cross Vander Nov 30 '15 at 02:14
  • http://tinyurl.com/zzfatbx -- a google on the keywords stackoverflow mysql ranking variables – Drew Nov 30 '15 at 02:15
  • 1
    @Drew that's the problem.. I googled a wrong keyword... Thank You very much Drew! Have a nice day – Cross Vander Nov 30 '15 at 02:22

2 Answers2

2

This creates an array which stores the totals, then sorts them and displays them in a list.

The array is indexed by student id.

$rankingArray = [];
$q = mysql_query("SELECT * FROM tbstudent");
while($r = mysql_fetch_array($q))
{
   $q2 = mysql_query("SELECT * FROM tbscores WHERE student_id = '".$r['student_id']."'");
   $total = 0;
   while($r2 = mysql_fetch_array($q2))
   {
      $total += $r2['scores'];
   }
   $rankingArray[$r['student_id']] = $total;
   echo "<tr><td>$total</td><td>/* for rank*/</td></tr>";
}

asort($rankingArray);
$rankNumber = 1;
foreach ($rankingArray as $id => $rank) {
    echo $rankNumber.' '.$id.' '.$rank.PHP_EOL;
    $rankNumber++;
}

It would be better to use MySQL to handle this and have PHP just display the results.

user2182349
  • 9,569
  • 3
  • 29
  • 41
0

It's better to use MySQL ORDER BY function

$q = mysql_query("SELECT * FROM tbstudent tst LEFT JOIN tbscores tsc ON tst.id = tsc.student_id ORDER BY tsc.score DESC");
while($r = mysql_fetch_array($q))
{
 $q2 = mysql_query("SELECT * FROM tbscores WHERE student_id = '".$r['student_id']."'");
 $total = 0;
 while($r2 = mysql_fetch_array($q2))
 {
  $total += $r2['scores'];
}
echo "<tr><td>$total</td><td>/* for rank*/</td></tr>";
}
EchoGlobal.tech
  • 694
  • 4
  • 8