Comparison in PHP SELECT statement with multi-row array as comparing variable

Question

I have a query to group by id, sum, and then rank. The issue is that the table contains all classes and doesn't have a column to define the class of each data.

Therefore, I have to use another table to get the id's that are in the same class as the id being ranked and then compare them.

Here is the code that I have already tried but results to errors:

$que = "SELECT * FROM registration WHERE CurrentClass = (SELECT CurrentClass FROM registration WHERE AdmNo = '$user_id')";
$statemen = $connect->prepare($que);
$statemen->execute();
$res = $statemen->fetchAll();

foreach($res as $rowww) { 

    $resu1 = mysqli_query($conn, "SELECT AdmNo, rank, total_score
    FROM (SELECT *  WHERE AdmNo = '".$rowww['AdmNo']."',  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank 
    FROM (SELECT * FROM 
    (SELECT AdmNo, SUM(Score) AS total_score 
    FROM `{$examination}`, 
    (SELECT @auto:=0, @marks:=0) as init 
    GROUP BY AdmNo) sub ORDER BY total_score DESC)t) as result
    WHERE AdmNo = '$user_id'"); 

    $row1 = mysqli_fetch_assoc($resu1); 
    $sum = $row1['total_score'];
    $position = $row1['rank'];
    $totalMarks = round($sum, 2);

}

To my understanding, the error

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in E:\xampp\htdocs\user...

Is because of the multi-row returned in the first query and that's why I need help with how I can solve this problem.

The required result are that the AdmNo's selected from registration are the only one that should be used in ranking.

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in E:\xampp\htdocs\user.. — sam, Aug 07 '19 at 09:19
Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Nov 11 '19 at 21:58

score -1 · Answer 1 · answered Aug 07 '19 at 09:28

Try this:

$query = "SELECT AdmNo, rank, total_score FROM (
    SELECT *  WHERE AdmNo = '" . $rowww['AdmNo'] . "',  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank FROM (
        SELECT * FROM (
            SELECT AdmNo, SUM(Score) AS total_score FROM `" . $examination . "`, (
                SELECT @auto:=0, @marks:=0
            ) as init GROUP BY AdmNo
        ) sub ORDER BY total_score DESC
    ) t
) as result WHERE AdmNo = '" . $user_id . "'";

$resu1 = mysqli_query($conn, $query);

I am still getting the same error. They are multiple errors for each $rowww['AdmNo'] — sam, Aug 07 '19 at 09:39

Comparison in PHP SELECT statement with multi-row array as comparing variable

1 Answers1