Multi Delete Query Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Multi Delete Query Problem

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multi Delete Query Problem

Post by Eran »

You can only delete from one table in a query.
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Multi Delete Query Problem

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multi Delete Query Problem

Post by Eran »

Use one query per table..
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Multi Delete Query Problem

Post 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...
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Multi Delete Query Problem

Post 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.
Post Reply