Print Rank from sum in PHP & MySql

Question

I have My Sql Database Table as below

 idno      Name        Subject         Score 

  1        Mahesh      English           55
  1        Mahesh      Maths             25
  1        Mahesh      Science           35
  2        Richards    English           65 
  2
  2 
  3
  3         
  3
  .................. Like ways so on till id number 12000

Now i will provide a form for the user and tell them to enter id number and submit then the output should be.

If User Enters idno : 3 and submit the form then the output should be

   IDNO        NAME         TOTAL SCORE       RANK
     1         MAHESH           95            2546 (Example)

and here i am using this code

   $id = mysql_real_escape_string($_POST['id']);
   $sum = "SELECT idno, SUM(score) AS tech
   FROM jbit 
   WHERE htno='$id'";
   $result1 = mysql_query($sum);
   echo "
   <center><table id='mytable' cellspacing='0'  border=3 align=center>
   <tr>
   <TH scope='col'>IDNO</TH>
   <TH scope='col'>NAME</TH>
   <TH scope='col'>TOTAL SCORE</TH>
   <TH scope='col'>RANK</TH>
   </tr><center>";
   while ($row = mysql_fetch_assoc($result1)){
echo "<tr>";
   echo "<td align=center>" . $row['idno']. "</td>";
   echo "<td align=center>" . $row['name']. "</td>";
   echo "<td align=center>" . $row['tech']. "</td>";
   echo "</tr>";

Here I am unable to calculate the rank and print the rank, how can I do this?

Based on Total Score i.e. SUM(Score) as Tech Rank shold be calculated & Printed

To print, try using window.print() javascript function. put this end of
tag of the page — Suriyamoorthy Baskaran, Oct 09 '12 at 13:00

score 1 · Answer 1 · answered Dec 22 '13 at 15:03

After investing a little bit of time on your issue, I have finally created and tested the following SQL query, it produces the same result as you requested and it also handles the ties very well.

SELECT idno, name, rank,total_score
FROM (SELECT *,  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank 
FROM (SELECT * FROM 
  (SELECT idno, name, SUM(score) AS total_score 
    FROM jbit, 
    (SELECT @auto:=0, @marks:=0) as init 
     GROUP BY name) sub ORDER BY total_score DESC)t) as result
WHERE idno ='1'

Hope this helps.

score 0 · Answer 2 · answered Oct 09 '12 at 12:25

0

SELECT idno, SUM(score) AS tech
       FROM jbit 
       WHERE htno='$id'"
       GROUP BY idno

answered Oct 09 '12 at 12:25

iLaYa ツ

3,941
3
32
48

but how to print the rank @LearneR – Nisha Reddy Oct 09 '12 at 12:26
Based on how it should be calculated rank? – iLaYa ツ Oct 09 '12 at 12:29
based on SUM as tech @LearneR – Nisha Reddy Oct 09 '12 at 12:30

Mahmoud Gamal · Answer 3 · 2012-10-09T13:25:47.197

0

Try this:

   SET @rownum = 0; 
   Select *,  (@rownum := @rownum+1) as RANK
   FROM
   (
      SELECT * 
      FROM
      (
         SELECT IDNO, NAME, SUM(score) AS TOTASCORE      
         FROM jbit 
         GROUP BY IDNO, NAME
       ) sub
       ORDER BY TOTASCORE DESC --The rank is calculated based on this field
   ) t
   WHERE IDNO = @IDNOParam

edited Oct 09 '12 at 13:25

answered Oct 09 '12 at 12:30

Mahmoud Gamal

78,257
17
139
164

based on Total Score rank should be decided & printed @Mahmoud Gamal – Nisha Reddy Oct 09 '12 at 12:32
@NishaReddy I am not sure whether the `ORDER BY` clause is allowed in the subqueries in mysql or not. But try it and let me know please. – Mahmoud Gamal Oct 09 '12 at 12:34
But How to print the particular rank of the user when user enter idno in form – Nisha Reddy Oct 09 '12 at 12:36
@NishaReddy Why the user enters the rank value? Isn't this value supposed to be calculated based on the total score for this user? Do you mean the user enters it in the form?? – Mahmoud Gamal Oct 09 '12 at 12:39
User will not enter the rank value, User enters the idno in form then that particular idno's rank should be printed as output based on Total score – Nisha Reddy Oct 09 '12 at 13:23
@NishaReddy then add `WHERE IDNO = @IDNOParam` to your query where `@IDNOParam` is what the user enters – Mahmoud Gamal Oct 09 '12 at 13:26
this is d erro after using this code Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/nhtsoft/public_html/engineershub/man1.php on line 86 – Nisha Reddy Oct 09 '12 at 13:40
sorry sorry nt that one.... Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/nhtsoft/public_html/engineershub/man1.php on line 100 this is d 100th line.. while ($row = mysql_fetch_assoc($result1)){ echo ""; echo "" . $row['htno']. ""; – Nisha Reddy Oct 09 '12 at 13:44
@NishaReddy - I want to see the actual sql error, Can you run that query directly in mysql or the phpadmin and show me the error? Thanks – Mahmoud Gamal Oct 09 '12 at 13:57

score 0 · Answer 4 · answered Aug 07 '14 at 06:12

I try to this code... may help other I am getting result from users and upload table for user profile pic and join it than after i am calculating user points and sorting on them. At last am checking and adding wo

set @row_num = 0;
set @calp =0;
select  if(@calp=(@calp:=user.cal_points), @row_num, @row_num := @row_num + 1) as row_number,user.* from 
(select user_skills.*,users.username,upload.file_name from user_skills join users on user_skills.user_id=users.id join upload on upload.upload_id=users.profile_pic order by user_skills.cal_points desc) as user
WHERE user.skill_name LIKE  '%ph%'






==========for both search =============
set @row_num = 0;
set @calp =0;

select temp.* from
(select  if(@calp=(@calp:=user.cal_points), @row_num, @row_num := @row_num + 1) as row_number,user.* from 
(select user_skills.*,users.username,upload.file_name from user_skills join users on user_skills.user_id=users.id join upload on upload.upload_id=users.profile_pic order by user_skills.cal_points desc) as user
WHERE user.skill_name LIKE  '%ph%') as temp
WHERE temp.username LIKE '%a%'

Print Rank from sum in PHP & MySql

4 Answers4

Linked