With this issue, I would really like to avoid modifying the core files if possible, so a workaround would be extremely helpful.
REGEX works with the 'where' query builder class easy enough, but using the word boundaries breaks it by adding an extra space.
function search($searchQuery){
$sq = '[[:<:]]'.strtolower($searchQuery).'[[:>:]]';
$this->db->select('column');
$this->db->from('table');
$this->db->where('LOWER(otherColumn) REGEXP', $sq);
echo $this->db->get_compiled_select();
}
search('billy');
Expected result:
SELECT
columnFROMtableWHERE LOWER(otherColumn) REGEXP '[[:<:]]billy[[:>:]]';
Actual result:
SELECT
columnFROMtableWHERE LOWER(otherColumn) REGEXP [[: < :]]billy[[:>:]]';
This is such a specific issue with a specific stack and I haven't found anybody running into the same issue. I've tried escaping the colons, the <'s, the square brackets, nothing seems to work. Or it adds additional backslashes.
Really any help or direction would be helpful at this point.
Thanks in advance.
function search($searchQuery){
$sq = $this->db->escape('[[:<:]]'.strtolower($searchQuery).'[[:>:]]');
$this->db->select('column');
$this->db->from('table');
$this->db->where('LOWER(otherColumn) REGEXP ', $sq, false);
echo $this->db->get_compiled_select();
}
search('billy');
For Codeigniter v 3.1.6 and MySQL v5.6
Just because Codeigniter adds space before and after < when compiling the query, I do not know why but it does. And I have escaped the value for variable $sq to mitigate injection Attack or identical. Hence, added third parameter to where function as false to prevent it from re-escaping and adding spaces.
I have not gone through other versions of codeigniter. I hope this will work.
For more information on MySQL REGEXP visit this link: MySQL 5.6 Reference Manual - Regular Expressions