CodeIgniter Forums

Full Version: MySQLi delete in multiply tables
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
yeah it needs to be updated but at the bottom you can see it: http://www.codeigniter.com/user_guide/da...er::delete
(05-18-2015, 12:47 AM)Narf Wrote: [ -> ]It's not a bug, you have to pass the WHERE clause to delete() when you want to delete from multiple tables ...

This definitely needs to be clarified in the documentation, as this would also be the case with $limit/$this->db->limit(), unless you set the last argument ($reset_data) to FALSE.

Of course, if you set the last argument to FALSE, you have to take care to call $this->db->reset_query() as needed before performing other database operations.

At first, I thought it would be relatively simple to "fix" the behavior of the delete() method for arrays, but there are a lot of potential issues hiding in there with those last three arguments.

[Edit]
...and just to make the last point clear, if you do set the last argument to FALSE while passing the $where and/or $limit clauses, it will call where() and limit() before calling _delete() on each table in the array, even though the where and limit clauses aren't reset from the calls for the previous table(s).
(05-18-2015, 07:39 AM)CroNiX Wrote: [ -> ]@narf maybe the userguide needs updating then because what he had is exactly what it shows:
http://www.codeigniter.com/user_guide/da...eting-data

Code:
$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);

(05-20-2015, 08:16 AM)mwhitney Wrote: [ -> ]
(05-18-2015, 12:47 AM)Narf Wrote: [ -> ]It's not a bug, you have to pass the WHERE clause to delete() when you want to delete from multiple tables ...

This definitely needs to be clarified in the documentation, as this would also be the case with $limit/$this->db->limit(), unless you set the last argument ($reset_data) to FALSE.

Of course, if you set the last argument to FALSE, you have to take care to call $this->db->reset_query() as needed before performing other database operations.

At first, I thought it would be relatively simple to "fix" the behavior of the delete() method for arrays, but there are a lot of potential issues hiding in there with those last three arguments.

[Edit]
...and just to make the last point clear, if you do set the last argument to FALSE while passing the $where and/or $limit clauses, it will call where() and limit() before calling _delete() on each table in the array, even though the where and limit clauses aren't reset from the calls for the previous table(s).

I didn't realize there was such an example in the documentation ... that may indeed make it a bug. The potential issues that you see, together with other factors (like not making it easy to delete from 10 tables by accident) are why I wouldn't consider this to be a good feature. Will have to look into it again, I guess.
(05-20-2015, 03:20 PM)Narf Wrote: [ -> ]I didn't realize there was such an example in the documentation ... that may indeed make it a bug. The potential issues that you see, together with other factors (like not making it easy to delete from 10 tables by accident) are why I wouldn't consider this to be a good feature. Will have to look into it again, I guess.

I probably never would have seen it in the docs, nor even imagined this was a feature of the delete() method, before seeing this thread. The basic idea of the feature is scary enough that I would avoid it, but it would appear that implementing the feature is an even bigger nightmare.
Cascading deletion in several tables is what I can imagine, i.e. you have a parent table and children tables and you don't want to leave orphaned records. This cascading deletion could be implemented as database triggers, or trough PHP code - as so called "observers".

In this light I can not see sense in the mentioned code example within the documentation. Maybe it should be simply removed.

Your code is absolutely right, it should have delete the tables.
While, you can delete them one by one taking the delete query in an 'IF' condition.
(05-20-2015, 03:20 PM)Narf Wrote: [ -> ]I didn't realize there was such an example in the documentation ... that may indeed make it a bug. The potential issues that you see, together with other factors (like not making it easy to delete from 10 tables by accident) are why I wouldn't consider this to be a good feature. Will have to look into it again, I guess.

What is the final determination on this?  Are the docs wrong or is this a bug?  

For the same example, deleting an `id` from multiple tables, what should the code look like instead?
(07-01-2015, 02:10 PM)sparky672 Wrote: [ -> ]
(05-20-2015, 03:20 PM)Narf Wrote: [ -> ]I didn't realize there was such an example in the documentation ... that may indeed make it a bug. The potential issues that you see, together with other factors (like not making it easy to delete from 10 tables by accident) are why I wouldn't consider this to be a good feature. Will have to look into it again, I guess.

What is the final determination on this?  Are the docs wrong or is this a bug?  

For the same example, deleting an `id` from multiple tables, what should the code look like instead?

I am not happy about having to commit this, but it's obviously designed to do that and it's a BC break, so ... https://github.com/bcit-ci/CodeIgniter/c...d3dc82ee58

As for how to delete the same ID from multiple tables ... assuming you're not using foreign keys with ON DELETE CASCADE, just calling delete() in a foreach loop is just fine. And no, I don't care that it would be 3-4 lines instead of 1 - it guarantees that multiple deletes is what you want, and you didn't unknowingly pass an array or the wrong WHERE condition to delete().
(07-02-2015, 01:51 AM)Narf Wrote: [ -> ]I am not happy about having to commit this, but it's obviously designed to do that and it's a BC break, so ... https://github.com/bcit-ci/CodeIgniter/c...d3dc82ee58

As for how to delete the same ID from multiple tables ... assuming you're not using foreign keys with ON DELETE CASCADE, just calling delete() in a foreach loop is just fine. And no, I don't care that it would be 3-4 lines instead of 1 - it guarantees that multiple deletes is what you want, and you didn't unknowingly pass an array or the wrong WHERE condition to delete().

Yes exactly, 4 lines vs 1 line... not a big deal.  My poorly worded question was intended to be more about what the documentation is supposed to say.  Thank-you for the fix and clarification.
Pages: 1 2