Welcome Guest, Not a member yet? Register   Sign In
Active Record order_by Problem [solved]

[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
$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
$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.

[eluser]Travis O[/eluser]
I figured out a solution. The issue was caused by the back ticks inserted into the query, as seen in the error. I solved this by adding
to remove the back ticks.

I was just made aware of this fantastic function
which renders the query, so you have a clue. This can come in handy

Theme © iAndrew 2016 - Forum software by © MyBB