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

Ikhlak S. · Accepted Answer · 2016-05-15 09:48:25Z

1

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.'"));

answered May 15, 2016 at 9:48

Ikhlak S.

9,10411 gold badges60 silver badges80 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:52:41Z

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

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered May 15, 2016 at 9:28

Amir Bar

3,1253 gold badges34 silver badges50 bronze badges

Collectives™ on Stack Overflow

PHP - Laravel : Raw Sql Query showing Error in controller

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related