+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: $this->db->order_by($orderby); trying to escape my $orderby = 'concat(firstname,lastname) asc' make it stop... (/showthread.php?tid=22904)
$this->db->order_by($orderby); trying to escape my $orderby = 'concat(firstname,lastname) asc' make it stop... - El Forum - 09-23-2009
[eluser]dmyers[/eluser]
My MySQL order by clause contains
$orderby = 'concat(firstname,lastname) asc'
because I want to sort by the combined first and last names (which works in MySQL) but, when I try to add it with CI active record via $this->db->order_by($orderby) it doesn't work because CI tries to escape it to ORDER BY CONCAT(firstname, `lastname)` asc which of course throws a MySQL error. How can I add it and not have CI try to escape it? Is there a secret "don't escape" parameter?
I suppose a change as such needs to be done in the core files.
$this->db->order_by($orderby); trying to escape my $orderby = 'concat(firstname,lastname) asc' make it stop... - El Forum - 09-23-2009
[eluser]Flemming[/eluser]
one way around it would be to add the concat to your select:
Code:
$this->db->select('*, concat(firstname,lastname) as sort_order',FALSE);then use the alias sort_order in your order_by
?
$this->db->order_by($orderby); trying to escape my $orderby = 'concat(firstname,lastname) asc' make it stop... - El Forum - 09-23-2009
[eluser]dmyers[/eluser]
Nice idea! using your sql skills! but since this is generated via ORM / CRUD this would need to be a virtual field or I would need to add some kind of "magic" field(s) to the model (hum....)
What I did for now is prepend this right to the top of the core file DB_active_rec.php around line 885 ya hacking the core isn't good but I would consider this a bug in the core source.
Then I just add "true" to the function to "not" try to escape the input.$this->db->order_by($orderby,true);
/**
* Sets the ORDER BY value
*
* @access public
* @param string
* @param string direction: asc or desc
* @return object
*/
function order_by($orderby, $direction = '')
{
if ($direction === true) {
// doesn't handle CI "caching"
$this->ar_orderby[] = $orderby;
return $this;
}
$this->db->order_by($orderby); trying to escape my $orderby = 'concat(firstname,lastname) asc' make it stop... - El Forum - 09-23-2009
[eluser]dmyers[/eluser]
Can I move this to the BUG section of CI so how or does CI need to do that?