PHP - Laravel : Raw Sql Query showing Error in controller

Question

I'm building a laravel application where I have to generate a report, for that I have build a controller and in that I have written a query to generate that report but it's showing following Error.

FatalErrorException in RoomController.php : syntax error, unexpected 'R' (T_STRING)

The problem is with the R No which is not the database But to show in the report I have to use that with the row value which I concated. How do I resolve the problem?

public function ajax_view_schedule(Request $request)
    {
        $dept_id=$request->get(['dept_id']);
$schedule= DB::select(DB::raw('SELECT courses.code as c_code, courses.name as c_name,COALESCE( CONCAT('R. No',':',rooms.room_number,', ',days.name ,', ', allocate_rooms.start,' - ',allocate_rooms.end),"Not Scheduled Yet") AS Schedule 
FROM departments join courses on departments.id = courses.department_id
left join allocate_rooms on allocate_rooms.course_id=courses.id 
left join rooms on allocate_rooms.room_id=rooms.id
left join days on allocate_rooms.day_id=days.id WHERE departments.id='.$dept_id.''));
        return \Response::json($course);  
    }

Answer 1

the error is pointing to this part:

CONCAT('R. No',':',rooms.room_number,', ',days.name ,', ', allocate_rooms.start,' - ',allocate_rooms.end)

take a look at 'R. No

you cant just put string outside of the quotes and hope that php will understand what to do with it, if you want to put quotes inside quotes you need to escape them, Escaping quotation marks in PHP

Answer 2

Try to quote your PHP string with double quotes and single quotes in your SQL query.

So, your query string would look like:

$schedule= DB::select(DB::raw("SELECT courses.code as c_code, courses.name as c_name,COALESCE( CONCAT('R. No',':',rooms.room_number,', ',days.name ,', ', allocate_rooms.start,' - ',allocate_rooms.end),'Not Scheduled Yet') AS Schedule 
FROM departments join courses on departments.id = courses.department_id
left join allocate_rooms on allocate_rooms.course_id=courses.id 
left join rooms on allocate_rooms.room_id=rooms.id
left join days on allocate_rooms.day_id=days.id WHERE departments.id='.$dept_id.'"));