[eluser]Travis O[/eluser]
I have created a special ORDER BY condition that sorts my data alphabetically ignoring "The" if it is in the title.
When I use
Code:
$this->db->query('SELECT * FROM lb_courses WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(id,".",2),".",-1) = '.$category_id.' ORDER BY IF(SUBSTRING(course_name,1,4) = "The ", SUBSTRING(course_name,5), course_name)');
everything works fine, but when I try to do the same using Active Record syntax
Code:
$this->db->where('SUBSTRING_INDEX(SUBSTRING_INDEX(id,".",2),".",-1)',$category_id)->order_by('IF(SUBSTRING(course_name,1,4) = "The ", SUBSTRING(course_name,5), course_name)')->get('courses');
, I get the following error,
Quote:A Database Error Occurred
Error Number: 1064
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 ' SUBSTRING(course_name, `5)`, `course_name)`' at line 4
SELECT * FROM (`lb_courses`) WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(id,".",2),".",-1) = '10' ORDER BY IF(SUBSTRING(course_name, `1`, `4)` = "The ", SUBSTRING(course_name, `5)`, `course_name)`
I'm fine with using "query", but I would prefer to use Active Record because it looks cleaner and it takes advantage of the prefix option in the database config.
Any insight would be helpful. Thank you.