Update MySQL Statement from CodeIgniter

Question

I'm having a problem with this query for some reason:

$this->db->query("UPDATE schools SET name = '$name', pop = '$pop', details = '$details', numteachers = '$numteachers', recess = '$recess', equipment = '$equipment' WHERE id = '$schoolid'");
echo $this->db->affected_rows(); 

For some reason affected_rows prints 0 but there's no errors with the query. When I look in the database the row id has not been affected. I escape all the inputs before the query like this:

$name = mysql_real_escape_string($_POST["schoolname"]);

With all of the inputs, an example query with real inputs looks like this before its executed:

UPDATE schools SET name = 'Jefferson County Public School', pop = '2,345', details = 'Lorem Ipsum is simply dummy text of the and typesetting industry.', numteachers = '2 Te', recess = '40 mins', equipment = 'Gym, Climbing Frame, Goal Posts, Track' WHERE id = '1'

All of the columns are already populated under row id 1, this is just new data. id is an int, primary key and auto_incrementing.

Any advice would help, thank you!

Answer 1

Are you sure that the resulting query is actually what's generated, or did you just insert some data? Check to make sure that your ID is actually the ID of the row that you want to update... it may be null, or something unexpected.

;)

Answer 2

Don't return affected rows to see if the query had an error or not. If you are updating a non-existent row you will get 0. If you update an existing row but don't actually change the data you will still get 0.

Return $this->db->update('table') to get a proper bool response (or $this->db->query() if you'd like to keep writing long winded SQL).