Sum rows in php and rank

i am creating a student information system in php and the database looks as follows

**studentId** **Math** **English** **Science** **className**
1                 80        66         85       3p1
2                 75        83         84       3p1
3                 70        88         90       3p1
4                 50        82         50       5p3
5                 88        77         77       3p1
6                 92        97         96       3p1

i want to rank only the members of the className 3P1 in using php. the sql statement below will do the ranking fine in phpmyadmin when the following are included

SET @myclassName:=0;
SET @myRank:= 0;
SET @myNextRank:= 0;
SET @myTotals:= 0;   

However when i convert it to php and use it in php the ranks generated are inconsistent. everytime i reload the page the ranks also change. how do i include the

SET @myclassName:=0;
SET @myRank:= 0;
SET @myNextRank:= 0;
SET @myTotals:= 0;

in the query in php.

<?php 

$StudentMarkz = "SELECT X.*,
                        @myRank:=CASE WHEN @myclassName <> className
                                           THEN @myNextRank:=1
                                      WHEN @myTotals <> totals
                                           THEN @myRank:=@myNextRank
                                      ELSE @myRank
                                 END AS Rank,
                        @myclassName:=className AS myclassName,
         开发者_高级运维               @myTotals:=totals AS myTotals,
                        @myNextRank:=@myNextRank+1 AS myNextRank
                   FROM ( SELECT studentmarkstable.studentId,surname, firstName,
                                 Math,
                                 English,
                                 Science,
                                 SocialStudies,
                                 studentmarkstable.className,
                                 Math + English + Science + SocialStudies AS totals
                            FROM studentmarkstable, student 
                           WHERE student.studentId=studentmarkstable.studentId 
                             AND studentmarkstable.className='3P1' 
                           ORDER BY className ASC,
                                    6 DESC
                        ) X";
$marksQuery = mysql_query($StudentMarkz) or die (mysql_error());

?>

if i use $marksQuery = mysqli_multi_query($StudentMarkz) or die (mysql_error()); i get an error.

thank you in advance

OK. here's a solution for MySQL, as that's probably the most commonly used database among PHP scripters:

SET @myClass:=0;
SET @myRank:= 0;
SET @myTotals:= 0;
SELECT X.*,
        @myRank:=CASE WHEN @myClass <> class 
                           THEN 1
                      WHEN @myTotals <> totals 
                           THEN @myRank+1
                      ELSE @myRank
                 END AS Rank,
        @myClass:=class AS myClass,
        @myTotals:=totals AS myTotals
  FROM ( SELECT studentId,
                Math,
                English,
                Science,
                Class,
                Math + English + Science AS totals
           FROM studentGrades
          ORDER BY 5 ASC,
                   6 DESC
       ) X

gives:

studentId  Math  English  Science  Class  totals  Rank  myClass  myTotals
2          75    83       84       3p1    242     1     3p1      242
5          88    77       77       3p1    242     1     3p1      242
1          80    66       85       3p1    231     2     3p1      231
6          92    97       96       5p2    285     1     5p2      285
3          70    88       90       5p2    248     2     5p2      248
4          50    82       50       5p2    182     3     5p2      182

with no gaps in the ranks

EDIT

If you want subsequent student rank positions to be maintained after several students with the same rank (e.g. 1,2,2,4 rather than 1,2,2,3), then use the following:

SET @myClass:=0;
SET @myRank:= 0;
SET @myNextRank:= 0;
SET @myTotals:= 0;
SELECT X.*,
        @myRank:=CASE WHEN @myClass <> class 
                           THEN @myNextRank:=1
                      WHEN @myTotals <> totals 
                           THEN @myRank:=@myNextRank
                      ELSE @myRank
                 END AS Rank,
        @myClass:=class AS myClass,
        @myTotals:=totals AS myTotals,
        @myNextRank:=@myNextRank+1 AS myNextRank
  FROM ( SELECT studentId,
                Math,
                English,
                Science,
                Class,
                Math + English + Science AS totals
           FROM studentGrades
          ORDER BY 5 ASC,
                   6 DESC
       ) X

gives

studentId  Math  English  Science  Class  totals  Rank  myClass  myTotals  myNextRank
2          75    83       84       3p1    242     1     3p1      242       2
5          88    77       77       3p1    242     1     3p1      242       3
1          80    66       85       3p1    231     3     3p1      231       4
6          92    97       96       5p2    285     1     5p2      285       2
3          70    88       90       5p2    248     2     5p2      248       3
4          50    82       50       5p2    182     3     5p2      182       4

with gaps in the ranks

This is the PHP way (of course you might use the mysql way too).

$sql = "SELECT studentId, Math, Engish, Science FROM table";
$query = mysql_query($sql) or die("Request not successfull!");

$results = array();

while ($result = mysql_fetch_array($adressen_query)){   
  $results[ $result['studentId'] ] = $result['Math'] + $result['Engish'] + $result['Science'];
}
// results now contains all studentIds as Keys and all added result values as value.

// sort descending
$results = arsort( $results );

Other approach:

You could add a new field "All" to your table and sum up all results there. (when writing your results to the table you could also sum up the 'All'-field) Then you can acces your data using the query and "ORDER BY"

SELECT 'All' FROM table ORDER BY 'All' DESC;