0
function examResult($student_id, $exam_id){
        global $conn;
        
        $stmt = $conn->prepare("SELECT SUM(CASE WHEN answers.answered_option=questions.correct_option THEN questions.marks ELSE 0 END) AS obtain, exams.exam_name, exams.total_marks, exams.pass_marks FROM exams INNER JOIN questions ON exams.id=questions.exam_id INNER JOIN answers ON answers.question_id=questions.id WHERE answers.student_id = ? AND exams.id = ? ");
        
        
        if($stmt){
            return 'ok';
            $stmt->bind_param('ii', $student_id, $exam_id);
            $stmt->execute();
            $result = $stmt->get_result();
            if($result){
                $row = $result->fetch_assoc();
                return $row;
            }
        } else{
            return false;
        }

    }

I was expecting 'ok' from the above function but it returning false. That means the $stmt is not executing. Also my both parameters $student_id & $exam_id are fine.

But if I execute it in my SQL console like below it works perfectly.

SELECT SUM(CASE WHEN answers.answered_option=questions.correct_option THEN questions.marks ELSE 0 END) AS obtain, exams.exam_name, exams.total_marks, exams.pass_marks FROM exams INNER JOIN questions ON exams.id=questions.exam_id INNER JOIN answers ON answers.question_id=questions.id WHERE answers.student_id = 18 AND exams.id = 24

enter image description here

Debarshi Das
  • 19
  • 1
  • 8
  • You would benefit from getting the actual error from the failure - for debugging have a look at https://stackoverflow.com/questions/17053466/how-to-display-errors-for-my-mysqli-query. But it's worth looking at how you handle error messages in general. – Nigel Ren Aug 19 '20 at 06:53
  • Do you have valid connection? – Sergiy T. Aug 19 '20 at 07:06
  • Thanks. I got the actual error. Solved it by adding "GROUP BY exams.id" at the end of the statement. – Debarshi Das Aug 19 '20 at 07:07

1 Answers1

0

Solved it b adding a GROUP BY statement at end.

 $stmt = $conn->prepare("SELECT SUM(CASE WHEN answers.answered_option=questions.correct_option THEN questions.marks ELSE 0 END) AS obtain, exams.exam_name, exams.total_marks, exams.pass_marks FROM exams INNER JOIN questions ON exams.id=questions.exam_id INNER JOIN answers ON answers.question_id=questions.id WHERE answers.student_id = ? AND exams.id = ? GROUP BY exams.id");
Debarshi Das
  • 19
  • 1
  • 8