CodeIgniter Active Record, basic update give error

Question

Hey,

I'm new to CodeIgniter and I get an error I cannot understand.

This is the code that give the error:

$data = array('adr' => $address);

$this->db->where('id', $id);
$this->db->update('domains', $data);

The error is:

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 '://www.example.com WHERE id = '10'' at line 1

This is the query:

UPDATE `domains` SET `adr` = http://www.example.com WHERE `id` = '10'

If I change this to

UPDATE `domains` SET `adr` = 'http://www.example.com' WHERE `id` = '10'

it works. Why is CodeIgniter creating this erroneous query?

Answer 1

Try escaping the single quotes in the $address variable before you call the update method.

Answer 2

Generally the CodeIgniter will automatically surround the value of $address with a single quote. I do not know why did you get this error message?

Answer 3

I have the same problem and codeigniter do not add single qoutes to where clause.

When you enter integer value, sql do not give error but when you put string value (as a variable) to where clause, it gives error. But when you add single quotes to query and run it on phpmyadmin, it works.

So the solution is adding (string) statement to your variable: as in this (string)$id

I wrote before to add single quotes to variable as '$id', but this will not going to work (I'm new to codeigniter&php, thanks to commenter Mitchell McKenna, I checked out what I wrote before)

Answer 4

Curious, see if it works when you escape the string use $this->db->escape()

$data = array('adr' => $this->db->escape($address));
$this->db->where('id', $id);
$this->db->update('domains', $data);