Clever Geek Handbook

Using WHERE CONCAT with active record in CodeIgniter

The raw request I am trying to connect is this:

SELECT * FROM x WHERE CONCAT(y, ' ', x) LIKE '%value%';

I have checked the AR docs and cannot find anything that would allow me to do this. I am not very good at exactly how it constructs these queries and was hoping someone could point me in the right direction. Thanks a bunch.

+3


source to share


4 answers


If you want to use the AR class, you need to pass FALSE as the third parameter to avoid auto-escaping the request. Now you can escape the argument yourself:

$value = $this->db->escape_like_str($unescaped);

$this->db->from('x');
$this->db->where("CONCAT(y, ' ', x) LIKE '%".$value."%'", NULL, FALSE);
$result = $this->db->get();

See item 4) in an Active Guide Recording Session. Citation:



   Custom string:

   You can write your own clauses manually:
   $where = "name='Joe' AND status='boss' OR status='active'";
   $this->db->where($where);
   $this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.
   $this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

An easier way, imho, is to run a "regular" query and use the binding:

$result = $this->db->query("CONCAT(y, ' ', x) LIKE '%?%'", array($value));
+8


source


something like this should work:



$this->db->where("CONCAT(y, ' ', x) LIKE '%value%'");

$this->db->get(x);
0


source


Or use the associative array method without using the third parameter:

$a = array(
    'CONCAT(`y`, " ", `x`)' => $value,
    'title' => $title,
    ...
);
...
$this->db->like($a);

The WHERE part of the query will be generated:
... WHERE CONCAT(`y`, " ", `x`) LIKE '%test value%' AND `title` LIKE '%test title%' AND ...


Obviously useful when using multiple search parameters.

0


source


It's old, but ...

You can try this:

$this->db->like('CONCAT(field_name," ",field_name_b)',$this->db->escape_like_str('value'));
0


source






More articles:


All Articles