CodeIgniter active record query with like() and or_like()

Question

The problem is that it displays all the rows from db instead of displaying only those rows which contain search keywords.

public function search($data)
{

    $name = $data["name"];
    $surname = $data["surname"];

    $this->db->select('*');
    $this->db->from('workers');
    $this->db->like('name', '%' . $name . '%');
    $this->db->or_like('surname', '%' . $surname . '%');

    $query = $this->db->get();

Foulard

Answer 1

You wrote CI like query wrong way.Look the documentation how to write it properly

Your query should be like this.No need to add %. CI add them if you do not pass 3rd parameter of like function.

$this->db->select('*');
$this->db->from('workers');
$this->db->like('name', $name);
$this->db->or_like('surname', $surname);

$query = $this->db->get();

Better do with some valid data like this.

$this->db->select('*');
$this->db->from('workers');
if($name)
{
   $this->db->or_like('name', $name);
}
if($surname)
{
    $this->db->or_like('surname', $surname);
}
$query = $this->db->get();

Sociopath

Answer 2

Shaiful is correct, your like statements are written incorrectly but I'd just like to extend onto this that you have to be careful with or_ clauses in Active Record. In Codeigniter these do not use brackets, so the following is fine:

$this->db->select('*');
$this->db->like('name', $name);
$this->db->or_like('surname', $surname);
$query = $this->db->get('workers');

It produces:

SELECT * FROM `workers` WHERE `name` LIKE `%$name%` OR `surname` LIKE `%$surname%`;

But if you extend the query at a later date, the following will not work correctly:

$this->db->select('*');
$this->db->where('id', $id);
$this->db->like('name', $name);
$this->db->or_like('surname', $surname);
$query = $this->db->get('workers');

This produces:

SELECT * FROM `workers` WHERE `id`=`$id` AND `name` LIKE `%$name%` OR `surname` LIKE `%$surname%`;

The problem with the second example is that the WHERE clause becomes optional due to the OR later in the SQL.

Codeigniter 3 has brought in the ability to use brackets and I'd recommend the following approach if there is a chance your query will become more complicated later on.

So a safer approach in my opinion when using or_ clauses would be:

$query = $this->db->select('*')->from('workers')
        ->group_start()
                ->like('name', $name)
                ->or_like('surname', $surname)
        ->group_end()
->get();

In your current example though, there is no issue with what Shaiful has given and it is completely Codeigniter 2 compatible.

Octavo