Delete the data in multiple tables

[ashalatha]

How to delete Data from multiple tables.
For example Iam having category id in one data base table .in another data base table iam having this id if i delete category id it should display a message as first delete in that table if the id is there in that database then nor then it can delete.

[jKidz]

You can use ON DELETE CASCADE option in mysql
By using this, on deleting the record from parent table removes the records from child tables.

Please check this article for more details and for examples
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

[Bin_2]

i would be better if u submit ur table structure here so that suggestion would be more clearer..
u can select row from second table with that id and if number of rows is 'greater than 0' or 'not 0' then u can echo or print the message u wish..

[Isaac_4]

Be really careful when you use On DELETE CASCADE - if you do not set it up exactly correct you will end up removing records that you didn't intend to. As Bin_2 says, it would be better to post the schema of your tables and then we can tell you how to figure out what order you need to delete things in.

Basically, you need to find the table that has only outgoing references to other tables, delete the rows from that table, then move on to each table that it referenced in order until you find everything to remove.

For example, look at this schema:

                    ***********
                    * Contact *
                    * ID      *
                    * ClientID*
                    ***********
                         |n
                         |1
                    ***********
                    * Client  *
                    * ID      *
                    ***********
                         |1
                         |n
                    ***********
                    *Invoices *
                    *ID       *
                    *ClientID *
                    ***********

In this table structure, if you want to delete a particular Client record with ID = 5, you need to delete everything from Contact where ClientID = 5, then from Invoices where ClientID = 5, and finally from Client where ID = 5.

DELETE FROM Client WHERE ClientID = 5;
DELETE FROM Invoices WHERE ClientID = 5;
DELETE FROM Client WHERE ID = 5;

A foreign key cannot ever refer to a deleted record (well, not true if you don't use constraints, but you should pretend it is true anyway and never delete a record if there are references to it).

If you have recursive schema, like this for instance:

                        ***************
                        *Page         *
                        *ID           *-|1
                        *ParentPageID *-|n
                        ***************

If you want to delete all children of a parent page and the parent page as well, then you must delete parents and children in the same query:

DELETE FROM Page WHERE ID = 10 OR ParentPageID = 10;