Level 6
OMG. Turns out I did not convert my Variables into the parameters when changing to stored procedure. How embarrassing. Sorry.
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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.
Please or to participate in this conversation.