PHP SQLSTATE[42000] syntax error or access violation: 1064 in query

Question

I'm running a query but when i run it, it returns a

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' count(distinct exams.user_id) as count_user_mhs from `users` where `users.type`' at line 1 (SQL: select `universities`.`name`, count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0, count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1, count(exams.subject_id) as count_subject, sum(exams.score) as count_score, TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time from `exams` inner join `universities` on `universities`.`id` = `exams`.`university_id` inner join `users` on `users`.`id` = `exams`.`user_id` where exists (select * from `universities` where `exams`.`university_id` = `universities`.`id`) group by `user_id` order by `count_score` desc, `count_time` asc)

$exams = Exam::join('universities', 'universities.id', '=', 'exams.university_id')
                ->join('users', 'users.id', '=', 'exams.user_id')
                ->select('universities.name',DB::raw('count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0'),DB::raw('count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1'),DB::raw('count(exams.subject_id) as count_subject'),DB::raw('sum(exams.score) as count_score'),DB::raw('TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time'));

Can anyone help? I am guessing, it has something to do with the DB::raw expression. But I don't know exactly what is wrong.

1) this is not a warning, this is an error. 2) you did not copy the whole error message, so we do not even know what may have gone wrong. 3) You have not shared the sql query laravel created from your code, which would also help to identify the error. But those subqueries with the counts in the raw sql part do look wrong. 4) It would also help if you described what exactly you are trying to achieve here. Because it is one thing that we can tell where you made a mistake, it is another think to help you to create a working solution. — Shadow, Jun 05 '17 at 08:12
[Further reading](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064). — tadman, Jun 05 '17 at 08:13
A minimal [improvement](https://meta.stackoverflow.com/a/291370/1783163) of your writing quality would help your question a lot. — peterh, Jun 07 '17 at 17:26
Possible duplicate of [SQLSTATE\[42000\]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO](https://stackoverflow.com/questions/4544051/sqlstate42000-syntax-error-or-access-violation-1064-you-have-an-error-in-you) — castis, Jun 07 '17 at 17:28

score 0 · Answer 1 · answered Jun 07 '17 at 17:57

Your generated query looks like this:

select
    `universities`.`name`,
    count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0,
    count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1,
    count(exams.subject_id) as count_subject,
    sum(exams.score) as count_score,
    TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time
from
    `exams`
        inner join
    `universities` on `universities`.`id` = `exams`.`university_id`
        inner join
    `users` on `users`.`id` = `exams`.`user_id`
where
    exists (select * from `universities` where `exams`.`university_id` = `universities`.`id`)
group by
    `user_id`
order by
    `count_score` desc,
    `count_time` asc

The problem is on lines 3 and 4 which are generated by the DB::raw code.

To fix this you need to update your Eloquent query like this:

<?php
$exams = Exam::join('universities', 'universities.id', '=', 'exams.university_id')
    ->join('users', 'users.id', '=', 'exams.user_id')
    ->select(
        'universities.name',
        DB::raw('(select count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0)'),
        DB::raw('(select count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1)'),
        DB::raw('count(exams.subject_id) as count_subject'),DB::raw('sum(exams.score) as count_score'),
        DB::raw('TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time')
    );

That said... Executing the SELECT COUNT subqueries in the main SELECT statement is not a good idea as they will be executed for each row selected by the main query.

PHP SQLSTATE[42000] syntax error or access violation: 1064 in query

1 Answers1