Multiple Where statements not working.

I'm passing multiple id's to a controller and trying to have it query the database. I have 3 tables that are linked in the query classsemester somascourses instructors

This mysql statement will retrieve the information from the database correctly. SELECT * FROM classsemester, somascourses, instructors WHERE classsemester.id = somascourses.SemesterID AND somascourses.Instructor_id = instructors.id AND classsemester.id = 2 AND somascourses.id = 2

Here's the function in the edit controller...

Since all the tables have a primary key of id.... I had to change the primary key for somascourses.id to course_id for it to show up in cc($course)l=; I need the to be able to put the starting table of classsemesters by the $id.. ID for that table and then pull the somascourses by a specific is using $courseid. Because I trying to pull a specific course from somascourses to edit.

 public function edit($id, $courseid)
    {
        
$course = DB::table('classsemester')
            ->join('somascourses', 'classsemester.id', '=', 'somascourses.SemesterID')    
            ->select('*','somascourses.id as course_id')
            ->join('Instructors', 'somascourses.Instructor_id', '=', 'Instructors.id')
            ->where('classsemester.id', '=', $id)
            ->orwhere('somascourses.SemesterID','=', $id)
            ->where('course_id', '=', $courseid)
            ->get();

}

How do I write this so, it will pull the same information as this mysql statement. SELECT * FROM classsemester, somascourses, instructors WHERE classsemester.id = somascourses.SemesterID AND somascourses.Instructor_id = instructors.id AND classsemester.id = 2 AND somascourses.id = 2

cmdobueno

7 years ago

Level 18

You must group them


$course = DB::table('classemester')
    ->join('somascourses', 'classsemester.id', '=', 'somascourses.SemesterID')    
    ->select('*','somascourses.id as course_id')
    ->join('Instructors', 'somascourses.Instructor_id', '=', 'Instructors.id')
    ->where(function($query) use($id){
        $query->where('classsemester.id', '=', $id)
                        ->orwhere('somascourses.SemesterID','=', $id)
    })
    ->where("course_id","=",$courseid)
    ->get();

Maybe this will help. It provides a grouped where clause.

jhutto

7 years ago

Level 2

Sorry... It looks right but keep getting

syntax error, unexpected '}'

I've tried multiple variations.

Cronix

7 years ago

Best Answer

Level 67

missing semicolon after ->orWhere() to close off the statement

->where(function($query) use($id){
    $query->where('classsemester.id', '=', $id)
        ->orwhere('somascourses.SemesterID','=', $id); // add semicolon
})

BTW docs for this: https://laravel.com/docs/5.6/queries#parameter-grouping

Please or to participate in this conversation.