[eluser]gloosemo[/eluser]
I have the following query
Code:
$table1 = "consump";
$table2 = "foodgroups";
$this->db->select("$table1.id as id, DATE_FORMAT(time, '%k:%i') AS formattime, CONCAT(food, ' (', brand, ')') AS concfood, ftype, name, CONCAT(c1, ' ', c2) AS consume, " . "comb, cal, fat, sat, trans, chol, sod, carb, fib, sug, pro", FALSE);
$this->db->from("$table1");
$this->db->join("$table2", "$table1.ftype = $table2.id");
$this->db->where("date", "$dateChange");
$this->db->order_by("DATE_FORMAT(time, %H)", 'asc');
$queryConsump = $this->db->get();
but when i attempt to run the query, i get the following error message:
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' at line 5
SELECT consump.id as id, DATE_FORMAT(time, '%k:%i') AS formattime, CONCAT(food, ' (', brand, ')') AS concfood, ftype, name, CONCAT(c1, ' ', c2) AS consume, comb, cal, fat, sat, trans, chol, sod, carb, fib, sug, pro FROM (`consump`) JOIN `foodgroups` ON `consump`.`ftype` = `foodgroups`.`id` WHERE `date` = '2011-10-27' ORDER BY DATE_FORMAT(time, `%H)` asc
It seems the active query is escaping the order_by clause in a very weird fashion placing a quote outside the brackets instead of inside where it should be, which is annoying because i need to DATE_FORMAT sql function. is there a way around this? Currently i'm accomplshing it manually without escaping anything which is dangerous...
thanks, G
