1

im using codeigniter 2.0.2 and this is from its userguide

$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

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

my question is once this executed how do you find its executed correctly or not?

hakre
  • 193,403
  • 52
  • 435
  • 836
Gihan Lasita
  • 2,955
  • 13
  • 48
  • 65

4 Answers4

4

The update function returns a value:

$result = $this->db->update('mytable', $data);

Check that value for either being TRUE (success) or FALSE (failure). update runs query internally and then returns the return value of query (Ref):

The query() function returns a database result object when "read" type queries are run, which you can use to show your results. When "write" type queries are run it simply returns TRUE or FALSE depending on success or failure.

hakre
  • 193,403
  • 52
  • 435
  • 836
  • i tried this with my query "$result = $this->db->update( 'users', $data, array('UserName' => $_POST["UserName"]) );" if the user name not exist on table it still returns success – Gihan Lasita Aug 22 '11 at 15:41
  • Well you should always use your brain when you code. If you want to insert instead of update, then you should do. Otherwise you might want to configure your db that it errors out if you try to update a non-existing row. Or you want to do update, insert if not exists: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html , http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – hakre Aug 22 '11 at 15:58
2

Use

$this->db->affected_rows()

to see how many rows have been affected on write type queries (update, insert, etc...)

http://codeigniter.com/user_guide/database/helpers.html

jondavidjohn
  • 61,812
  • 21
  • 118
  • 158
  • ive tried this also lets say im trying to update a password and im entering same password as before without change then affected rows returns 0 :( – Gihan Lasita Aug 22 '11 at 15:43
  • Keep your brain switched on when coding. If you don't udpate, then the update didn't affect any row. The function works as announced, don't be a fool, use it as a tool. – hakre Aug 22 '11 at 15:58
0

When developing CodeIgniter model methods, I find that I consistently return desirable values depending on the type of database write that is executed. It is often important to differentiate between a query that has run successfully versus a query that has actually changed a record.

For an update or delete query, I'll return the number of affected rows -- this will be most helpful to controller methods that call it. If you are performing logging (to keep track of change history), then ONLY log something if there is a change to the row; otherwise you are unnecessarily bloating your change history logs.

public function update(int $id, array $newData) :int
{
    $oldData = $this->db->get_where('mytable', ['id' => $id])->row_array();

    if ($this->db->update('mytable', $newData, ['id' => $id])) {
        $affectedRows = $this->db->affected_rows();
        if ($affectedRows) {
            $this->Log->mytableUpdate($id, $newData, $oldData);
        }
        return $affectedRows;
    }
    return 0;
}

For insert queries, I always return the auto-incremented id of the newly inserted row via insert_id().

If using the PDO driver with PostgreSQL, or using the Interbase driver, this function requires a $name parameter, which specifies the appropriate sequence to check for the insert id.

public function insert(array $newData) :int
{
    if ($this->db->insert('mytable', $newData)) {
        $newId = $this->db->insert_id(); // or insert_id('mytable')
        $this->Log->mytableInsert($newId, $newData);
        return $newId;
    }
    return 0;
}

Having consistent return types in your model methods will make your project easier to develop and maintain. The script that calls these model methods will be able to quickly assess the outcome by making a "falsey" check.

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
0

Both answers was valid. You just have to use each one depending on the case. If you are just checking if the query was executed use this method:

$result = $this->db->update('mytable', $data);

if you really want the number of rows affected the second method is better:

$this->db->affected_rows()

however I always use the second method. The update query is a good example why. A query can be successful and still there was nothing updated on the database because the value that you were trying to update was actually equal to the value you are sending in the query.

This would be a false positive. And the affected rows would be 0. I hope it helped =)

Marco Monteiro
  • 39
  • 5