CODEWITHSUNDEEP
phpmysqlmysqli
M. Lak
M. Lak

Reputation: 911

How to calculate rank based on total marks

I have 3 tables i want to display the 3 table data in single table based on primary key, foreign key the result came perfectly! But i need to calculate rank based on the total marks from my second table.

result screenshot: enter image description here

Please anyone tell me the query to calculate rank

I used the following mysql query

if(isset($_POST['submit']))
{
$result = mysqli_query($con,"
SELECT s.student_name
     , s.contact_number
     , m.total
     , m.rank
     , p.father_name 
  FROM student_details s 
  JOIN mark m
    ON s.student_id = m.student_id 
  JOIN parents_details p
    ON p.student_id = s.student_id 
 WHERE s.student_name = '".$_POST['student_name']."' 
");

echo "<table border='1' align='center' cellpadding='15' bgcolor='#FFFFFF'>
<tr>
<th>NAME</th>
<th>CONTACT NUMBER</th>
<th>TOTAL MARK</th>
<th>RANK</th>
<th>FATHER NAME</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['student_name'] . "</td>";
  echo "<td>" . $row['contact_number'] . "</td>";
  echo "<td>" . $row['total'] . "</td>";
  echo "<td>" . $row['rank'] . "</td>";
   echo "<td>" . $row['father_name'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);

}?>

enter image description here enter image description here

enter image description here

Upvotes: 0

Views: 1088

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SELECT * FROM 
(
    SELECT @rank := @rank+1 finalrank,ZZ.* FROM
    (
        SELECT student_details.student_name, 
        student_details.contact_number, mark.total, 
        mark.rank, parents_details.father_name 
        FROM student_details 
        INNER JOIN mark ON student_details.student_id=mark.student_id 
        INNER JOIN parents_details ON parents_details.student_id=student_details.student_id ,(SELECT @rank:=0)z
        ORDER BY mark.total desc
    )ZZ
)ZZZ 
WHERE ZZZ.student_name = '".$_POST['student_name']."'

Just try above query.

Here I had used SELECT @rank:=0 and @rank := @rank+1.

Upvotes: 1

Related Questions