2

I have a bit of a strange problem that has been baffling me. All I am trying to do is run a query on a database table but for some reason, CodeIgniter is putting apostrophes into the query which is subsequently breaking the page.

My code looks like this:

$this->db->select("SUBSTRING(body,5)"); 
$this->db->order_by("date", "desc");
$this->data['query'] = $this->db->get_where('blog-entries', array('status' => 'P'), 3);

But I get an error on this page:

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 'FROM (`blog-entries`) WHERE `status` = 'P' ORDER BY `date` desc LIMIT 3' at line 2

The query is actually being run as:

SELECT SUBSTRING(body, `5)` FROM (`blog-entries`) WHERE `status` = 'P' ORDER BY `date` desc LIMIT 3

As you can see for some reason apostrophes have been added around the number 5 within the substring. If I remove the substring then everything works and if I remove the apostrophes and run the query directly on my db it also works.

Has any got any ideas as to why this may be happening or have a solution?

Your help would be greatly appreciated.

Many thanks,

G.

gok-nine
  • 195
  • 3
  • 15
  • It's actually back-ticks being added, not apostrophes. Just to clarify. – Michael Apr 28 '12 at 11:29
  • Does adding `FALSE` as a second parameter, like this `$this->db->select("SUBSTRING(body,5)", FALSE); ` fix it? It should stop CodeIgniter adding backticks. – jleft Apr 28 '12 at 11:31
  • that's why I HATE these silly query builders, making simple queries into screen-high blocks of spaghetti – Your Common Sense Apr 28 '12 at 11:34
  • 1
    To be fair, most of them also have an option to just manually type out the entire query without using a builder. You have a choice. – Michael Apr 28 '12 at 11:40

1 Answers1

5

Use this:

$this->db->select("SUBSTRING(body,5)", FALSE);

As a default, Codeigniter tries to add back-ticks where it thinks is relevant. Sometimes it adds them where it shouldn't. Passing FALSE as the second parameter prevents it from doing this.

Michael
  • 11,912
  • 6
  • 49
  • 64
  • Thanks you very much for your reply. This does get rid of the database error, however, it doesnt appear to run the query as I get error on the page where I am writing out the fields from the query: – gok-nine Apr 28 '12 at 12:06
  • I get the "Message: Undefined property" error message. I am going to try and grab the 'body' field and substring it directly outputting the field from the controller rather then the whole query. – gok-nine Apr 28 '12 at 12:08
  • Actually, I think I realise whats happening. I just need to add the other fields into the SELECT! – gok-nine Apr 28 '12 at 12:12
  • I ended up going down the route of defining the entire query rather than using the query builder. Thanks very much for your help guys! – gok-nine Apr 28 '12 at 12:17