0

I have 5 tables in my database : Category1 , Category2 , Category3 , Category4 , Category5

I have a user that might or might not be found on these tables. If he is found , i want to DELETE him from the table.

I could do that with 5 queries like this :

//search if the user is already subscribed in every one of the categories 
$stmt = $db->prepare("Select * FROM category1 WHERE deviceUDID = :deviceUDID"); 
$stmt->execute(array(':deviceUDID' => $deviceUDID));

if($rows_found = $stmt ->rowCount()) {    
  $stmt = $db -> prepare("DELETE FROM category1 WHERE deviceUDID =:deviceUDID");    
  $stmt->execute(array(':deviceUDID' => $deviceUDID));
}

Then for category2 :

//search if the user is already subscribed in every one of the categories 
$stmt = $db->prepare("Select * FROM category2 WHERE deviceUDID = :deviceUDID"); 
$stmt->execute(array(':deviceUDID' => $deviceUDID));

if($rows_found = $stmt ->rowCount()) {    
  $stmt = $db -> prepare("DELETE FROM category1 WHERE deviceUDID =:deviceUDID");    
  $stmt->execute(array(':deviceUDID' => $deviceUDID));
}

and so on for the rest of the categories..

Would it be possible to search in all these categories with 1 query to make it faster?

But i cant see the logic how something like that could happen.. Any ideas?

EDIT (BONUS PART)

Would be easier ,faster or more efficient to just make a DELETE query every time , even if the user is not there and let the query fail?

Or i should first check every table and if found make a DELETE query?

Which one could be more effective?

donparalias
  • 1,834
  • 16
  • 37
  • 60

4 Answers4

2

If you are worried about atomicity, enclosing the 5 deletes in a transaction covers that.

Alternatively, mysql should by now support foreign keys and cascaded deletes, so if you set up the foreign key relationship with your "user" table, you may have nothing more to do.

Szocske
  • 7,466
  • 2
  • 20
  • 24
  • I think thats exactly what i would like to do. But i am not sure how the "foreign" key would work. Could you provide me some more info with a simple example? As you said there is a table with users , where all the users are. And then 5 subscription tables , were a user might or might not be. If there i want him out. – donparalias May 03 '13 at 11:41
  • I prefer to nudge you in the right direction to accelerate your learning process. This looks like a good starting point, with a mysql example even: http://stackoverflow.com/questions/757181/basics-of-foreign-keys-in-mysql – Szocske May 03 '13 at 12:09
  • 1
    +1 for bringing up foreign keys. Multi-table delete is nice but the underlying goal is "delete stuff together with this user", so FK it is. – mabi May 03 '13 at 12:13
1

No. You could create a view that would tell you which categories a device was present in, but it would not be updateable.

create view allCategories as
 select 1 as category, *
 from   category1
 union
 select 2 as category, *
 from   category2
 union
 select 3 as category, *
 from   category3
 union
 select 4 as category, *
 from   category4
 union
 select 5 as category, *
 from   category5
paul
  • 21,653
  • 1
  • 53
  • 54
  • Paul what exactly would this return me? Can you give me an example? I think this with @Marcassin answer could work. – donparalias May 03 '13 at 11:41
  • I would just go with @Marcassin answer, I did not know that you could do multiple table deletes using mySql. – paul May 03 '13 at 11:48
  • but paul if the user is not in the table , is efficient to let the DELETE query fail ? Is it correct programatically? I am new to php/mysql and i am trying to be as efficient as possible. – donparalias May 03 '13 at 11:50
  • That would depend on how big the tables were, but I would imagine that a single query would be quicker than five separate ones. You'd also gain a bit of performance as you wouldn't need the five `select` queries either. – paul May 03 '13 at 11:55
1

DELETE clause allows many tables in one request. try out :

DELETE FROM category1 c1, category2 c2, categorie3 c3 WHERE c1.deviceUDID= :deviceUDID AND c2.deviceUDID= :deviceUDID AND c3.deviceUDID= :deviceUDID ...
Marcassin
  • 1,386
  • 1
  • 11
  • 21
  • Yes but i would like to DELETE , !IF! the user is subscribed. In your example you suppose that the user is actually there! What if he is not though? – donparalias May 03 '13 at 11:38
  • Then your delete operation would affect 0 rows which is not an error for SQL servers unless specified explicitly. – diegoperini May 03 '13 at 11:40
  • Hmm so what you are saying is always try to DELETE without checking if the user is there? Is this a good technique? Is it more effective to make 5 DELETES even if they fail , than Searching 5 tables and IF found DELETE ? – donparalias May 03 '13 at 11:43
  • Yep it's a bit faster than making 5 DELETE query. But in MULTIPLE DELETE, it work only if you have a rows corresponding in each col. You can create 5 DELETE query with a for loop in PHP, but you don't need to check if the rows exist before. DELETE just does nothing if no row with this deviceUDID exist. – Marcassin May 03 '13 at 12:09
  • it does not "fail", it affects 0 rows, which is fine. – Szocske May 03 '13 at 12:12
0

DELETE FROM category1 WHERE deviceUDID in (Select * FROM category2 WHERE deviceUDID = :deviceUDID) is what you are looking for.

diegoperini
  • 1,796
  • 21
  • 37
  • there are 5 categories could you fix your answer please? And can you explain why it would work? The if($rows_found = $stmt ->rowCount()) how would it work correctly? Thats my main problem... – donparalias May 03 '13 at 11:36