CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
Sturm
Sturm

Reputation: 4285

Using WHERE CONCAT with Active Record in CodeIgniter

The raw query I'm trying to get plugged in here is:

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

I've checked through the AR docs and can't find anything that would allow me to do this. I'm not very familiar with how exactly it's constructing these queries, and was hoping someone could point me in the right direction. Thanks a bunch.

Upvotes: 3

Views: 8322

Answers (4)

Eder Ribeiro
Eder Ribeiro

Reputation: 664

This is old but...

You can try this:

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

Upvotes: 0

m79lkm
m79lkm

Reputation: 3070

something like this should work:

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

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

Upvotes: 0

Apostle
Apostle

Reputation: 482

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

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

Will be generated WHERE part of the query:
... WHERE CONCAT(`y`, " ", `x`) LIKE '%test value%' AND `title` LIKE '%test title%' AND ...
Obviously useful when using more than one search parameters.

Upvotes: 0

Damien Pirsy
Damien Pirsy

Reputation: 25435

If you want to use the AR class you need to pass FALSE as third parameter to avoid the query being escaped automatically. You are now left to escaping the argument by 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();

Refer to point 4) in the Active Record session of the manual. Quoting:

   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, whould be to run a "regular" query and take advantage of binding:

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

Upvotes: 9

Related Questions