CodeIgniter Active Record multiple "where" and "or" statements [duplicate]

Question

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?

Staysee · Accepted Answer · 2013-09-20 21:47:50Z

12

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

answered Sep 20, 2013 at 21:47

Staysee

1,9075 gold badges23 silver badges31 bronze badges

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

2 Comments

TonyQ Over a year ago

$keywords might be sql injection , need to escape first... ex. $this->db->escape_like_str($search)

mickmackusa Nov 6 at 4:17

CodeIgniter has the like() method. More relevant are the group_start(), or_like(), and group_end() methods.

Vickel · Accepted Answer · 2014-12-04 12:15:49Z

1

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

edited Dec 4, 2014 at 12:15

answered Dec 4, 2014 at 11:55

Vickel

7,9806 gold badges40 silver badges63 bronze badges

2 Comments

Avant la lettre Over a year ago

The above does not group the LIKE conditions – and thus, it is not equal to the accepted solution by @Staysee. Unfortunately, the parenthesis (that tell MySQL to nest OR within AND) cannot be expressed in Active Record statements.

mickmackusa Nov 6 at 4:20

$this->db->group_start()->or_like(['school.description' => $keyword, 'class.description' => $keyword, 'student.description' => $keyword])->group_end()

... then you can use get_where() for the normal comparisons.

Collectives™ on Stack Overflow

CodeIgniter Active Record multiple "where" and "or" statements [duplicate]

2 Answers 2

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Linked

Related