CODEWITHSUNDEEP
phparrayscodeigniteractiverecordsql-like
Mohammed Sufian
Mohammed Sufian

Reputation: 1781

CodeIgniter active record query to return rows where a column value is LIKE one of potentially multiple values

I would like to pass array to like clause of active record in CodeIgnter for that I have written the following:

$array_string = 'smart,intelligent,awesome';

$array_like = explode(',',$array_string);

and in model:

$this->db->like('topic',array($array_like));

but I am getting

Severity: Notice
Message: Array to string conversion

See my related question here: CodeIgniter active record query where a column value is LIKE a value in a dynamic list

Upvotes: 2

Views: 9193

Answers (3)

mickmackusa
mickmackusa

Reputation: 48031

The following insights are true and accurate for like(), not_like(), or_like(), and or_not_like().

It is misleading to say that like() cannot receive an array. Actually, like() can receive an associative array of column => value pairs. This is not very helpful for your task because you need to repeat a column one or more times. You'd need to use a hacky technique to make each key unique without affecting the column name for each element.

Ultimately, I recommend using an iterative technique to execute one or more like() method calls.

If you have any other WHERE clause conditions, you may need to write $this->db->group_start() before the loop structure and $this->db->group_end() after the loop structure.

  • array_reduce() -- This is pretty slick, in my opinion, because it adds the conditions to the active record and returns the active record instance for tail-end chainability! It doesn't even break the code if the input array is empty -- in this case, it just doesn't add a WHERE clause at all. The callback signature of array_reduce() can be fn(CI_DB_query_builder $qb, string $v): CI_DB_query_builder => ... if you prefer type declarations.

    return array_reduce(
    explode(',', 'smart,intelligent,awesome'),
    fn($qb, $v) => $qb->like('topic', $v),
    $this->db
)
->get('your_table')
->result();

  • array_walk() doesn't permit chaining onto the function itself.

    $values = explode(',', 'smart,intelligent,awesome');
array_walk(
    $values,
    fn($v) => $this->db->like('topic', $v)
);
return $this->db
    ->get('your_table')
    ->result();

  • foreach() doesn't need the array to be assigned as a variable.

    foreach (explode(',', 'smart,intelligent,awesome') as $v) {
    $this->db->like('topic', $v);
}
return $this->db
    ->get('your_table')
    ->result();

Again, the other _like methods can be used in these snippets. For instance, using or_like() will write OR between conditions. For example:

$this->db->group_start();
return array_reduce(
    explode(',', 'smart,intelligent,awesome'),
    fn($qb, $v) => $qb->or_like('topic', $v),
    $this->db
)
->group_end()
->get_where('your_table', ['status' => 1])
->result();

Rendered SQL:

SELECT *
FROM `your_table`
WHERE (
    `topic` LIKE '%smart%' ESCAPE '!'
    OR `topic` LIKE '%intelligent%' ESCAPE '!'
    OR `topic` LIKE '%awesome%' ESCAPE '!'
)
AND `status` = 1

Upvotes: 0

Ryan
Ryan

Reputation: 3582

You can't just pass an array to the like() function, it has to be a string. You need to apply a like clause for each of the keywords in the string.

You need to use or_like to match a record to either of the keywords, however, because if you just use like() each time, it will need to match all of the keywords due to the query being like LIKE "%smart" AND LIKE "%intelligent" etc. and that isn't what you require.

$array_string = "smart,intelligent,awesome";
$array_like = explode(',', $array_string);
foreach($array_like as $key => $value) {
    if($key == 0) {
        $this->db->like('topic', $value);
    } else {
        $this->db->or_like('topic', $value);
    }
}

Try this way to avoid your problem of the rest of the where statement being ignored.

$array_string = "smart,intelligent,awesome";
$array_like = explode(',', $array_string);

$like_statements = array();

foreach($array_like as $value) {
    $like_statements[] = "topic LIKE '%" . $value . "%'";
}

$like_string = "(" . implode(' OR ', $like_statements) . ")";

The value of $like_string will be (topic LIKE '%smart%' OR topic LIKE '%intelligent%' OR topic LIKE '%awesome%')

You can then use $like_string in the following way with ActiveRecord:

$this->db->where($like_string, FALSE);

Upvotes: 8

machineaddict
machineaddict

Reputation: 3236

Assuming you are using Codeigniter 2.1.4, as you can read in CodeIgniter active record documentation, you cannot pass the second argument as array in like function. You need to pass a string as argument.

$array_string = "smart,intelligent,awesome";
$array_like = explode(',', $array_string);

foreach ($array_like as $like)
{
    $this->db->like('topic', $like);
}

Upvotes: 0

Related Questions