Multiple Deletes.

[hotnuts21]

What do you guys think is the best way to delete lots of data from a database, if its spread across many tables?

Would you use

  $del = mysql_query("DELETE table1, table2, table3 
  FROM table1, table2, table3 
  WHERE table1.user_id ='"  . $user_id . "' AND table2.user_id ='" . $user_id  . "'AND table3.user_id ='" . $user_id ."'"); 

or would you use this

  $del1 = mysql_query("DELETE FROM table1 WHERE user_id='" . mysql_real_escape_string($user_id)."'");
    $del2 = mysql_query("DELETE FROM table2 WHERE user_id='" . mysql_real_escape_string($user_id)."'");
    $del3 = mysql_query("DELETE FROM table3 WHERE user_id='" . mysql_real_escape_string($user_id)."'");
     if ($del1 and $del2 and $del3) {
      echo ('success category deleted<br />' . mysql_affected_rows() . mysql_error() .' affected<br />');
       }else{
             die('error deleting' . mysql_error() . '');
    } 
  } 

[dbevfat]

I guess it doesn't matter really. If you make sure that both queries have always the same effect, I guess you should go with the former. Dunno about multiple delete query on other databases, but for portability the latter is probably better. But since you use calls directly to mysql_query(), I guess portability is not on your mind, so I'd suggest the first query .

[hotnuts21]

Sorry im a bit of newb to all this, can you explain what you mean by portability? and whats wrong with mysql_query()?

thnx

[dbevfat]

Well, what I meant by portability is that if you write your code carefully enough, you can switch to a database other than MySQL with a minimum fuss.

For example, if one day you decide you want to use PostgreSQL or MSSQL instead of MySQL, you'd have to change all your mysql_* function calls to pg_* or mssql_*. To take that of your back, you can use a so called "database abstraction layer" that provides you with classes and functions that are not database-dependant.

Then, instead of using mysql_query(), which is MySQL specific, you could use something else, which would NOT be specific to ANY database, and just "tell" it to work on MySQL or any database. With using a database abstraction layer, switching from one database to another is a matter of minutes (in theory, in practice it takes longer, but it's faster then to search&replace all mysql_* functions).

If you're interested, these layers are: ADODB, Creole, PEAR:DB, PEAR:MDB2, ... to name a few.

Regards

edit: had to disable smilies, because of that PEAR":D"B :)

[hotnuts21]

That makes a lot of sense and your the second person to reccomend working so that my code is 'portable'.

Thnx for the input.