several MYSQL statements in one

Question

I have a place ('place') where I can have some boxes ('box'). In each box, I can have items ('item'). I can also have items in the place outside of a box. I want to delete a place, and by so, to delete each boxes in the place, including items in each boxes as well as the items in the place but outside any box. I got it working with the following separated statements:

1: select the place

SELECT placeid, placename 
FROM place 
WHERE userid = (SELECT id 
                FROM user 
                WHERE username = 'username' AND password ='password') 
  AND placename = 'placename'

2: select the boxes in that place

SELECT boxid, boxname 
FROM box 
WHERE placeid = 'id'

3a: for each box, delete the items in it

DELETE FROM item 
WHERE boxid = 'boxid'

3b: then delete the box

DELETE FROM box 
WHERE id = 'boxid'

4: delete the items in the place not in a box

DELETE FROM item 
WHERE placeid = 'placeid' AND boxid = 0

5: delete the place

DELETE FROM place 
WHERE id = placeid

This is working but of course this is slow. The error management is also not that nice to work with.

Is there a way of doing all this with fewer SQL statements or even only one?

I got of course the same question for deleting a box in one statement!

edit: mysql with php

Thanks a lot.

Answer 1

Because item and box both have a placeid (per the info in your question) you can simplify this task a bit, but you still need 3 DELETE statements:

SELECT p.placeid
INTO #PlacesToDelete
FROM place p 
INNER JOIN [user] u ON p.userid = u.id
WHERE u.username = 'username'
    AND u.[password] = 'password'
    AND p.placename = 'placename'

DELETE i
FROM item i
INNER JOIN #PlacesToDelete ptd ON i.placeid = ptd.placeid

DELETE b
FROM box b
INNER JOIN #PlacesToDelete ptd ON b.placeid = ptd.placeid

DELETE p
FROM place p
INNER JOIN #PlacesToDelete ptd ON p.placeid = ptd.placeid

Or, if this is related to MySQL, you should be able to further simplify and use a single statement:

DELETE p, b, i
FROM place p 
INNER JOIN [user] u ON p.userid = u.id
INNER JOIN box b ON b.placeid = p.placeid
INNER JOIN item i ON i.placeid = p.placeid
WHERE u.username = 'username'
    AND u.[password] = 'password'
    AND p.placename = 'placename'

Edit: Based on the comment if an item is in a box, placeid is equal to 0 because it belongs to a box, not a place, here's an updated query that will delete everything you want:

DELETE i1, i2, b, p
FROM place p 
INNER JOIN [user] u ON p.userid = u.id
INNER JOIN box b ON b.placeid = p.placeid
INNER JOIN item i1 ON i1.placeid = p.placeid
INNER JOIN item i2 ON i2.boxid = b.boxid
WHERE u.username = 'username'
    AND u.[password] = 'password'
    AND p.placename = 'placename'

Answer 2

You should try ON DELETE CASCADE, it would look like this:

ALTER TABLE box ADD CONSTRAINT fk_box_place_id 
                FOREIGN KEY (placeid) 
                REFERENCES place(id) 
                ON DELETE CASCADE;

ALTER TABLE item ADD CONSTRAINT fk_item_place_id 
                 FOREIGN KEY (placeid) 
                 REFERENCES place(id) 
                 ON DELETE CASCADE;

I don't think you need any other cascades, since placeid is available in all tables and will cover items both in and out of boxes, but maybe I'm overlooking something.

Once set up, all you do is delete the place you want to delete and the rest is handled for you:

DELETE 
FROM place
WHERE placeID = 'deletedPlaceID';