Update SQL with Where AND Where Conditions in codeigniter

Question

This is my code:

public function remove_employee( $emp_no, $now ) {
    $this->db->delete('employees', array('emp_no' => $emp_no));
    $this->db->flush_cache();

    $this->db->start_cache();
    $this->db->where('emp_no', $emp_no);
    $this->db->where('to_date', '9999-01-01');
    $this->db->update('dept_emp', array('to_date' => $now));
    $this->db->stop_cache();
    $this->db->flush_cache();   

    $this->db->start_cache();
    $this->db->where('emp_no', $emp_no);
    $this->db->where('to_date', '9999-01-01');
    $this->db->update('salaries', array('to_date' => $now));
    $this->db->stop_cache();
    $this->db->flush_cache();   

    $this->db->start_cache();
    $this->db->where('emp_no', $emp_no);
    $this->db->where('to_date', '9999-01-01');
    $this->db->update('titles', array('to_date' => $now));
    $this->db->stop_cache();
    $this->db->flush_cache();   

    return;
} //END REMOVE EMPLOYEE

When I run this code it deleted my records. I don't understand why.

I want it to: UPDATE TABLE WHERE CONDITION_1 AND CONDITION_2 = B

PS:

**$now** is todays date (i.e. 2012-12-25)
**$emp_no** is a unique employee number i.e. 500122

score 1 · Accepted Answer · edited May 23 '17 at 12:08

Possible way to debug this would be to comment out the $this->db->delete().

Try changing these lines:

$this->db->delete('employees', array('emp_no' => $emp_no));
$this->db->flush_cache();

To:

//$this->db->delete('employees', array('emp_no' => $emp_no));
//$this->db->flush_cache();

Then try it and see if your records are still there. If your update was successful and your records are still in the tables, you might have a Foreign Key using Delete Cascade. Which means if you delete the record from the employees table you will also be deleting the records from dept_emp, salaries and titles.

CodeIgniter Active Record Class: http://ellislab.com/codeigniter/user-guide/database/active_record.html

InnoDB Foreign Key: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Check Foreign Keys: How do I see all foreign keys to a table or column?

Thanks your spot on. It was these lines which were messing it up. When the foreign key is deleted, it deletes with it all associated records that uses that foreign key. Thank you for your help. :D — D.Hussain, Dec 27 '12 at 00:34
@user1306764 No problem glad I could help, please remember to click the check mark if you feel my answer was an acceptable solution. — PhearOfRayne, Dec 27 '12 at 00:36

Update SQL with Where AND Where Conditions in codeigniter

1 Answers1