Order by Domain Extension Name using CodeIgniter Active Record Class

Question

$extension = “SUBSTRING_INDEX(domain_name, ‘.’, -1)”;
$this->db->order_by($extension, “asc”); 

It says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘asc LIMIT 50’ at line 44

But its working when I didn’t used the $this->db->order_by Active Record Class such as this one: $this->db->query(“SELECT * FROM domain ORDER BY SUBSTRING_INDEX(domain_name, ‘.’, -1)”);

Anyone please help me. Thanks.

Answer 1

I believe you'd need to extend the database active record library like this: http://codeigniter.com/wiki/Extending_Database_Drivers/

Add another argument like $escape=null to the order_by, and use it to prevent _protect_identifiers from running in your function. This would let you use an unescaped order by in the same way that select and where can be used now.

Answer 2

Since: $extension = “SUBSTRING_INDEX(domain_name, ‘.’, -1)”; $this->db->order_by($extension, “asc”);

Result to: SUBSTRING_INDEX(domain_name, `'`.`'`, `-1)`

I edit: system/database/drivers/mysql/mysql_driver.php by changing: var $_escape_char = '`'; to var $_escape_char = '';