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?

Paul M · Accepted Answer · 2017-02-25 15:40:36Z

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.

answered Feb 25, 2017 at 15:40

Paul M

4515 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AminoAcid Over a year ago

Thanks a lot! I'm still getting used to Laravel so I still have to learn about how to use models in my project.

Hiren Gohel Over a year ago

@Paul: I tried your scenario, but i got

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

I can't be able to select multiple values in select('') if i use group by, any idea how to fix it??

AminoAcid · Accepted Answer · 2017-02-26 03:53:13Z

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();

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related