Laravel SQL Query Keeps Giving SQLSTATE[42000]: Syntax error or access violation: 1064

Question

I have this SQL query in my controller at laravel

$distinct_course = DB::table('student')
    ->select(DB::raw('count(*) as grad_count, `student_course`, MONTH(`student_date_ended`)'))
    ->where('student_course', '=', 'Basic Computer')
    ->whereYear('student_date_ended', '=', '2015')
    ->groupby(DB::raw('MONTH(`student_date_ended`'))
    ->get(); 

Which is based on this SQL query I made to work first before converting it to Laravel

select count(*) as grad_count, `student_course`, MONTH(`student_date_ended`) from `student` where `student_course` = "Basic Computer" and year(`student_date_ended`) = 2015 group by MONTH(`student_date_ended`)

But for some reason I always get this error.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 (SQL: select count(*) as grad_count, student_course, MONTH(student_date_ended) from student where student_course = Basic Computer and year(student_date_ended) = 2015 group by MONTH(student_date_ended)

Am I doing something wrong here that I'm not aware of?

Answer 1

As an alternative, in all my projects I use the following way of DB interactions:

$Query = 'select count(*) as grad_count, `student_course`, 
  MONTH(`student_date_ended`) 
  from `student` 
  where `student_course` = ? 
  and year(`student_date_ended`) = ? 
  group by MONTH(`student_date_ended`)';

$GradStudents = DB::select($Query, [ $StudenCourse, $StudentDateEnded]);

This way I can go from confirmed working MySQL statement to Laravel without worrying of call not working after conversion.

Note: Instead of keeping your db calls in the controller, I would them to a model folder. This way you will maintain the MVC design pattern.

Answer 2

With Paul's help I managed to get it working with this code.

$Query = 'select count(*) as grad_count, `student_course`, 
              MONTH(`student_date_ended`) as Month 
              from `student` 
              where `student_course` = ? 
              and year(`student_date_ended`) = ? 
              group by MONTH(`student_date_ended`), student_course';

  $StudentCourse = 'Basic Computer';
  $StudentDateEnded = 2015; 

  $distinct_course = DB::select($Query, [ $StudentCourse, $StudentDateEnded]);

It would seem that part of the problem was I also needed to add the 'student_course' column in the group by so I tried adding it to my original Laravel code and also managed to get it work with this manner.

 $distinct_course = DB::table('student')
    ->where('student_course', '=', 'Basic Computer')
    ->whereYear('student_date_ended', '=', '2015')
     ->select(DB::raw('count(*) as grad_count, `student_course`, MONTH(`student_date_ended`) as Month'))
    ->groupby(DB::raw('MONTH(`student_date_ended`), student_course'))
    ->get();