John Davidson

Message:

Assalammualaikum..

I have database like this:

name | subjects | score 

B    | math     | 70 

B    | math     | 80 

B    | scien    | 90 

A    | math     | 80 

And I have query like this:

$query = mysqli_query($db,"SELECT  name, subject, score 

( 

 CASE subject WHEN @curSubject 

 THEN @curRow := @curRow +1  

 ELSE @curRow :=1 AND @curSubject:= subject END 

)  AS rank 

FROM table GROUP BY name,score ORDER BY name, score desc");

If I Select on SQL output from that query just like this:

name | subjects | score | rank 

B    | math     | 80    | 1 

B    | math     | 70    | 2 

B    | scien    | 90    | 1 

A    | math     | 80    | 1 

My problem is, when I will echo using looping php code like this:

while ($array = mysqli_fetch_array($query)){
echo $array['rank']'
';
}

output:

rank  | rank (not like this)

1     | 1 

1     | 2 

1     | 1 

1     | 1 

How can the value rank be echoed? Thank you
Wassalammualaikum..

---

My Answer:
To echo the rank value correctly in PHP, you can modify your query to include an additional variable to keep track of the rank within each group. Here's an updated version of your query:

php
$query = mysqli_query($db, "SELECT name, subject, score,
@curRank := IF(@curSubject = subject, @curRank + 1, 1) AS rank,
@curSubject := subject
FROM table
JOIN (SELECT @curRank := 0, @curSubject := '') r
ORDER BY name, score DESC");


With this query, the `@curRank` variable is used to calculate the rank within each group of subjects. Then, you can echo the rank value in your PHP code like this:

php
while ($array = mysqli_fetch_array($query)){
echo $array['rank'] . '
';
}


This should output the correct rank values for each row in the result set.