Home several MYSQL statements in one
Reply: 0

several MYSQL statements in one

user1521
1#
user1521 Published in April 20, 2018, 10:50 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.450114 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO