Good After noon all. I am having an issue running a stored procedure in a Controller: Laravel 5.8 Mariadb: 10.4.8
Here is the stored procedure:
DELIMITER $$ CREATE DEFINER=`dbuser`@`10.6.12.18` PROCEDURE `GetStudentsToGrade`( IN course_ID INT, IN assignmentID INT ) BEGIN SELECT c.id as courseID, c.name, count(sess.id) as TotalSessions, y.userID, y.firstname, y.lastname, y.NoAttended, y.SessionID, y.updated_at, z.grade, z.updated_at FROM courses c JOIN attendances a on c.id = a.course_id left join attendancesessions sess on a.id = sess.attendance_id LEFT JOIN ( SELECT c.id as courseID, u.id as userID, u.firstname, u.lastname, sess.id as SessionID, count(al.status) as NoAttended, al.updated_at FROM attendancelogs as al join attendancesessions sess on al.attendancesession_id = sess.id join attendances a on sess.attendance_id = a.id join courses c on a.course_id = c.id join users u on al.user_id = u.id WHERE c.id = @courseID AND al.status = 1 GROUP BY userID, courseID ) as y on c.id = y.courseID LEFT JOIN ( SELECT users.id, users.firstname, users.lastname, assign_grades.grade, assign_grades.updated_at, assign_grades.assignment_id FROM assign_grades LEFT JOIN users on assign_grades.user_id = users.id JOIN assignments on assign_grades.assignment_id = assignments.id WHERE assignments.id = @assignmentID ) as z on y.userID = z.id WHERE courseID = @courseID GROUP BY y.userID, c.id HAVING TotalSessions = y.NoAttended; END$$ DELIMITER ;
If I run this in MySQL Workbench I get the result I expect.
CALL GetStudentsToGrade(5, 97);
Now in the controller:
$studentsToGrade = DB::select(DB::raw("call GetStudentsToGrade('$course->id', '$assignment->id')")); dd($studentsToGrade, $course->id, $assignment->id);
The dump show:
 5 97
I also tried:
$studentsToGrade = DB::select(DB::raw("call GetStudentsToGrade(5, 97)"));
With the same results. I cannot seem to figure out why this is not working,
Any help would be greatly appreciated.