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 query builder documentation and can't find anything that would allow me to do this.
Add a comment
|
4 Answers
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));
1 Comment
mickmackusa
When you pass a string-type value to
query()'s parameters array, the value injected into the ? is going to be automatically quoted as a value, right? Does that render CONCAT(y, ' ', x) LIKE '%'foo'%'?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.
1 Comment
mickmackusa
It is not universally portable to hardcode the identifier quoting.
something like this should work:
$this->db->where("CONCAT(y, ' ', x) LIKE '%value%'");
$this->db->get(x);
2 Comments
Damien Pirsy
This wouldn't work, the expression would be escaped with backticks
mickmackusa
This expression would only be safe to use if the value was static (as written in this answer); otherwise a dynamic value would not be escaped and could either break the query or provide inaccurate results.
This is old but...
You can try this:
$this->db->like('CONCAT(field_name," ",field_name_b)',$this->db->escape_like_str('value'));
1 Comment
mickmackusa
like() is already designed to call escape_like_str() internally.