Result position Generation

[Royal]

<?php
include_once('config.php');
$session_indexx=$_SESSION['index_number'];
$session_termm=$_SESSION['term'];
$session_classs=$_SESSION['class'];
$session_yearr = $_SESSION['year'];
 // This is the query to generate the individual student class Position from the grand total.
$sql = "SELECT rank
        FROM (
            SELECT ord.id
                , @seq := @seq+1 as seq
                , @rank := CASE WHEN ord.grand_total = @prev
                                THEN @rank
                                ELSE @seq
                                END as rank
                , @prev := ord.grand_total as grand_total
            FROM (
                SELECT id
                    , g.grand_total
                FROM student_exam_result g
                ORDER BY grand_total DESC
                LIMIT 18446744073709551615
            ) ord
            JOIN (SELECT @seq:=0, @rank:=0,@prev:=0) init
        ) ranked
        JOIN student_exam_result g ON g.id = ranked.id
        WHERE class = ?
            AND index_number = ?
            AND term = ?
            AND year = ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ssss', $session_classs, $session_indexx, $session_termm, $session_yearr);
$stmt->execute();
$stmt->bind_result($position);
$stmt->fetch();


?>

Hello guys, please help with issues from the above code, the issue is when i have multipe data on the database table for the exam results the student positions becomes incorrect, the code is supposed to calculate student results based on their grand_total and accepts the index_number,term, class and term as html inputs which is used to seect the students from the same class. The image below shows my database table

 

from the table above, EKE DANIEL in JSS1 A with grand total of 979 is supposed to come second in class but the code throws 5 as shown in the resut below

Please how do i solve this

[Strider64]

To address this issue, you can try adding an additional WHERE clause to the SQL query to filter the results based on the input class, as follows:

$sql = "SELECT rank
        FROM (
            SELECT ord.id
                , seq := seq+1 as seq
                , rank := CASE WHEN ord.grand_total = prev
                                THEN rank
                                ELSE seq
                                END as rank
                , prev := ord.grand_total as grand_total
            FROM (
                SELECT id
                    , g.grand_total
                FROM student_exam_result g
                WHERE g.class = ?
                ORDER BY grand_total DESC
                LIMIT 18446744073709551615
            ) ord
            JOIN (SELECT seq:=0, rank:=0,prev:=0) init
        ) ranked
        JOIN student_exam_result g ON g.id = ranked.id
        WHERE class = ?
            AND index_number = ?
            AND term = ?
            AND year = ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param('sssss', $session_classs, $session_classs, $session_indexx, $session_termm, $session_yearr);
$stmt->execute();
$stmt->bind_result($position);
$stmt->fetch();

In this updated query, the WHERE g.class = ? clause has been added to the subquery to ensure that only students from the input class are included in the calculation of their positions. Additionally, the bind_param statement has been updated to include an additional s parameter to match the added input parameter.

Note that this solution assumes that the class column in the student_exam_result table exactly matches the input session_classs value. If there are any discrepancies between the input value and the column values, you may need to adjust the query accordingly.

Edited March 27, 2023 by Strider64

[Royal]

Hello @Strider64 it threw the below error

[requinix]

Try adding back the @s that you had before.