Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CodeIgniter Active Record multiple "where" and "or" statements

I have the following Active Record query.

//Example 1
public function info($school, $class, $student, $keyword)
{
    $this->db->where('school.id', $school);
    $this->db->where('class.id', $class);
    $this->db->where('student.id', $student);

    $this->db->or_where('school.description', $keyword);
    $this->db->or_where('class.description', $keyword);
    $this->db->or_where('student.description', $keyword); 

    return $this->db->get('info')->result();
}

I want to group the bottom 3 "or_where" statements so they are included with the top 3 "where" statements. The solution I came up with was this...

//Example 2
public function info($school, $class, $student, $keyword) 
{
    $this->db->where('school.description', $keyword);
    $this->db->where('school.id', $school);
    $this->db->where('class.id', $class);
    $this->db->where('student.id', $student);

    $this->db->or_where('class.description', $keyword);
    $this->db->where('school.id', $school);
    $this->db->where('class.id', $class);
    $this->db->where('student.id', $student);

    $this->db->or_where('student.description', $keyword); 
    $this->db->where('school.id', $school);
    $this->db->where('class.id', $class);
    $this->db->where('student.id', $student);

    return $this->db->get('info')->result();
}

This works fine, but is there a way to do this without repeating code?

like image 298
Staysee Avatar asked Sep 20 '13 21:09

Staysee


2 Answers

I found a solution!

public function info($school, $class, $student, $keyword)
{
    $this->db->where('school.id', $school);
    $this->db->where('class.id', $class);
    $this->db->where('student.id', $student);

    $this->db->where("(school.description LIKE '$keywords' OR class.description LIKE '$keywords' OR student.description LIKE '$keywords')");

    return $this->db->get('info')->result();
}
like image 70
Staysee Avatar answered Nov 04 '22 06:11

Staysee


I just stumbled into this and want to add following solution, which uses CI active record $this->db->like() syntax:

public function info($school, $class, $student, $keyword)
{

    $this->db->where('school.id', $school);
    $this->db->where('class.id', $class);
    $this->db->where('student.id', $student);

    $this->db->like('school.description', $keyword);
    $this->db->or_like('class.description', $keyword);
    $this->db->or_like('student.description', $keyword);

    return $this->db->get('info')->result();
}
like image 42
Vickel Avatar answered Nov 04 '22 07:11

Vickel