SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    eCommerce specialist hotnuts21's Avatar
    Join Date
    Apr 2002
    Location
    Aberystwyth, UK
    Posts
    1,355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Deletes.

    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
    PHP Code:
      $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
    PHP Code:
      $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() . '');
        } 
      } 
    Search & Rescue Aberystwyth Lifeboat
    CSS Tutorials/Guides

    Sitepoint signatures are not visible to Search Engines More info

  2. #2
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 .

  3. #3
    eCommerce specialist hotnuts21's Avatar
    Join Date
    Apr 2002
    Location
    Aberystwyth, UK
    Posts
    1,355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry im a bit of newb to all this, can you explain what you mean by portability? and whats wrong with mysql_query()?

    thnx
    Search & Rescue Aberystwyth Lifeboat
    CSS Tutorials/Guides

    Sitepoint signatures are not visible to Search Engines More info

  4. #4
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :)

  5. #5
    eCommerce specialist hotnuts21's Avatar
    Join Date
    Apr 2002
    Location
    Aberystwyth, UK
    Posts
    1,355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes a lot of sense and your the second person to reccomend working so that my code is 'portable'.

    Thnx for the input.
    Search & Rescue Aberystwyth Lifeboat
    CSS Tutorials/Guides

    Sitepoint signatures are not visible to Search Engines More info


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •