Sum lines in php and rank

Question

I am creating a student information system in php and the database is 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 members of the Name 3P1 class when using php. the sql statement below will evaluate the rating 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 created series are incompatible. every time I reload the page, the rows also change. How to turn it on

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

in request 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 message.

early

Answer 1

OK here is a solution for MySQL, as it is probably the most frequently used database among PHP scripts:

 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 

no spaces in the ranks

EDIT

If you want follow-up student positions to be maintained after several students with the same rank (e.g. 1,2,2,4, not 1,2,2,3), 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 spaces in the ranks

Answer 2

This is the PHP path (of course you can use mysql 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 ); 

Another approach:

You can add a new “All” field to the table and summarize all the results. (when writing results to a table, you can also summarize "All'-field") Then you can access your data using a query and "ORDER BY"

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