Page 1 of 1

Multi Delete Query Problem

Posted: Fri Jun 18, 2010 3:47 pm
by tito85
Hi,

I am trying to delete multiple data from my database at ones. I did this query but I am having a problem. The problem is that all genres, directors, comments, favorites and ratings have been deleted. However only the movie that have the ID from the $_GET['id'] has been deleted.

I want that all the records in the movies, genres, directors, comments, favorites and ratings tables that has a relation with the movie that has been deleted will be deleted will be deleted too.

Code: Select all

$delete = "DELETE movies, genres, directors, comments, favorites, ratings FROM movies, genres, directors, comments, favorites, ratings WHERE movies.MovieID = '" . $_GET['id'] . "' AND (movies.MovieID = genres.MovieID OR movies.MovieID = directors.MovieID OR movies.MovieID = comments.MovieID OR movies.MovieID = favorites.MovieID OR movies.MovieID = ratings.MovieID)";
      mysql_query($delete) or die(mysql_error());
Any Help please?

Re: Multi Delete Query Problem

Posted: Fri Jun 18, 2010 3:57 pm
by Eran
You can only delete from one table in a query.

Re: Multi Delete Query Problem

Posted: Fri Jun 18, 2010 4:07 pm
by tito85
pytrin wrote:You can only delete from one table in a query.
Hi,

I found that it is possible to do a multiple delete. Maybe I understood wrong...?

However can you suggest another way of how can I do this please?

Thanks

Re: Multi Delete Query Problem

Posted: Fri Jun 18, 2010 4:34 pm
by Eran
Use one query per table..

Re: Multi Delete Query Problem

Posted: Fri Jun 18, 2010 4:45 pm
by tito85
pytrin wrote:Use one query per table..
Thats what I did. But I am trying to improve the way of how these are deleted...

Re: Multi Delete Query Problem

Posted: Fri Jun 18, 2010 6:30 pm
by mikosiko
tito85 wrote:However only the movie that have the ID from the $_GET['id'] has been deleted.
and that is exactly what you are doing in this part of your delete statement.

Code: Select all

WHERE movies.MovieID = '" . $_GET['id']
tito85 wrote:I want that all the records in the movies, genres, directors, comments, favorites and ratings tables that has a relation with the movie that has been deleted will be deleted will be deleted too.
and that is not what your delete is doing?.... according to you:
tito85 wrote:The problem is that all genres, directors, comments, favorites and ratings have been deleted
?????? :?

to me the easy way to delete from different tables with clear relationships is implementing referential integrity rules at the data base level (ON DELETE CASCADE)... if you don't know about how to use it... just read the proper chapters in the mysql manual.