Codeigniter like, or_like doesn't work with where

Question

I found a problem in my codeigniter app. I've written a simple search query using 4 different like and or_like.

$q = $this->db->select('*')
->from('table')
->like('col', $search_string1, both)
->or_like('col', $search_string2, both)
->or_like('col', $search_string3,  both)
->or_like('col', $search_string4, both)
->get()->results

And it works as I wanted, but I decided to add new col in my table called "active" and I want to display only rows that got active = 1. So I tried to add where to my query, but it didn't worked as I expected.

$q = $this->db->select('*')
->from('table')
->where('active', 1)
->like('col', $search_string1, both)
->or_like('col', $search_string2, both)
->or_like('col', $search_string3,  both)
->or_like('col', $search_string4, both)
->get()->results

This query show also rows that got active set as 0. How can I fix that to display only rows with active = 1 in codeigniter?

Answer 1

Hello Can you please use following code

$q = $this->db->select('*')
->from('table')
->where('active', 1)
->where("(col LIKE '%".$search_string1."%' OR col LIKE '%".$search_string2."%' OR col LIKE '%".$search_string3."%' OR col LIKE '%".$search_string4."%')", NULL, FALSE)
->get()->results;

Thanks

Answer 2

For an explanation of @Roshan's code because the answer may not be readily apparent to folks learning SQL in CI. (I also fixed errors in the code.)

$q = $this->db
              // ->select('*') // you don't need select('*') if you want everything
              // ->from('table') // Don't need this either
              ->where('active', 1)
              ->where("(col LIKE '%".$search_string1."%' OR col LIKE '%".$search_string2."%' OR col LIKE '%".$search_string3."%' OR col LIKE '%".$search_string4."%')", NULL, FALSE)
              ->get('table') // add your table name here
              ->result();  // it's not "->results"

So your final query would look like this:

$q = $this->db->where('active', 1)
              ->where("(col LIKE '%".$search_string1."%' OR col LIKE '%".$search_string2."%' OR col LIKE '%".$search_string3."%' OR col LIKE '%".$search_string4."%')", NULL, FALSE)
              ->get('table')
              ->result(); 

Here's what the above is asking:

"get all results from 'table' 
    where active = 1 AND `col` = search_string1
 OR where active = 1 AND `col` = search_string2
 OR where active = 1 AND `col` = search_string3
 OR where active = 1 AND `col` = search_string4

 Then assign the result object to $q

A simpler way to view it but more complicated way to write the code (but maybe easier to understand):

$q = $this->db
            // where active=1 AND col LIKE %$search_string1%
             ->where('active', 1)
             ->like('col', $search_string1, 'both')

            // OR where active=1 AND col LIKE %$search_string2%
             ->or_where('active', 1)
             ->like('col', $search_string2, 'both')

             // OR where active=1 AND col LIKE %$search_string3%
             ->or_where('active', 1)
             ->like('col', $search_string3, 'both')

             // OR where active=1 AND col LIKE %$search_string4%
             ->or_where('active', 1)
             ->like('col', $search_string4, 'both')

            ->get('table')
            ->result();

There are times when you'll need to do it this way. For instance, if active may be several states. on, off, pending and you need to check that field for a specific state. Not necessary in your case as active is always 1, but if active could be more than one thing you want, you'll have to spell it out in your query builder.