Delete from two tables help needed

[mattyd]

Hi all,

I've got two tables in a mysql (4.0.0) database which are called 'gallery' and 'pictures'. Gallery simply holds the name and id of the galllery and pictures hold other information but each row has a gallery id (gid).

I need to delete a gallery from the gallery table, but at the same time, delete any rows from the pictures table that have that gallerys id.

I tried doing this:
----------------------------------------------
$query = "DELETE FROM gallery, pictures WHERE gallery.gid=$gid && pictures.gid=$gid";
$result = mysql_db_query($DBName, $query, $connection) or die ("Error in query: $query. " . mysql_error());
-------------------------------------------------------

But got this error:

-------------------------------------------------
You have an error in your SQL syntax near ' pictures WHERE gallery.gid=15 && pictures.gid=15' at line 1
---------------------------------------------------------

Is what I'm trying to do possible.?

I first tried splitting it up into two different queries, deleting from the gallery table first, then deleting from the pictures table, but it would only delete the entry in the gallery table and the pictures table was unaffected.

Here is how I tried it:

---------------------------------------------------------------

$query = "DELETE FROM gallery WHERE gid=$gid";
$result = mysql_db_query($DBName, $query, $connection) or die ("Error in query: $query. " . mysql_error());
$query2 = "DELETE FROM pictures WHERE gid=$gid";
$result2 = mysql_db_query($DBName, $query, $connection) or die ("Error in query: $query. " . mysql_error());

----------------------------------------------------------

.....but as I said,...it didn't work :(

Can any help me / point me in the right direction....please :)

Matt

[raf]

Welcome here

Should be
$result2 = mysql_db_query($DBName, $query2, $connection) or die ("Error in query: $query. " . mysql_error());

Now you just executed the first query twice (whith no result the second time)

[mattyd]

I just noticed that myself as I was looking at my code again....DOH!

It must be possible using just one query though isn't it?

Matt

[raf]

I don't think so. I could be completely wrong, but that 1 query is actualy an inner join (it's the older syntax). This join will creat a view and the other command are executed on that view and not on the underlying tabels. But i may be completely off. I'd need to try it out.

Normally you should use "on delete cascade", but this could mean you need to recreate the tabels (i think you should make the effort to rebuild them and then use an "insert into" to move all records). More info
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html