Merge Multiple Queries

[jbonnett]

I'm trying to merge all these queries into one statement

        $q = "DELETE FROM ".TBL_USERS." WHERE username = '$session->username'";
  $database->query($q);
  $q = "DELETE FROM ".TBL_BANNED_USERS." WHERE username = '$session->username'";
        $database->query($q);
  $q = "DELETE FROM ".TBL_MAIL." WHERE UserTo = '$session->username'";
        $database->query($q);
  $q = "DELETE FROM ".TBL_MAIL." WHERE UserFrom = '$session->username'";
        $database->query($q);
  $q = "DELETE FROM ".TBL_FRIENDS." WHERE person_id = '$session->username'";
        $database->query($q);
  $q = "DELETE FROM ".TBL_FRIENDS." WHERE friend_id = '$session->username'";
        $database->query($q);
  $q = "DELETE FROM ".TBL_FORUM." WHERE author = '$session->username'";
        $database->query($q);

I have no idea how to do this I have tried Google and no joy please help

 

- Jamie

[requinix]

MySQL's DELETE has a multi-table syntax, but I would still recommend you keep it as it is now. You could do away with the temporary $q and just execute the queries directly though - cuts the number of lines in half.

Edited December 23, 2012 by requinix

[Barand]

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join, as described in Section 13.2.9.2, “JOIN Syntax”.

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

[jbonnett]

MySQL's DELETE has a multi-table syntax, but I would still recommend you keep it as it is now. You could do away with the temporary $q and just execute the queries directly though - cuts the number of lines in half.

 

 

The only problem is I'm trying to create an if statement like

if(!database->query($q)){
          die(mysql_error());
}else{
          echo"blagh";
}

 

And I can't do this with multiple queries unless I create multiple if statement which I can't do with what I''m trying to accomplish.

I also tried something along the lines of

  $q = "DELETE FROM ".TBL_USERS." WHERE username = '$session->username'";
  $q .= "DELETE FROM ".TBL_BANNED_USERS." WHERE username = '$session->username'";
  $q .= "DELETE FROM ".TBL_MAIL." WHERE UserTo = '$session->username'";
  $q .= "DELETE FROM ".TBL_MAIL." WHERE UserFrom = '$session->username'";
  $q .= "DELETE FROM ".TBL_FRIENDS." WHERE person_id = '$session->username'";
  $q .= "DELETE FROM ".TBL_FRIENDS." WHERE friend_id = '$session->username'";
  $q .= "DELETE FROM ".TBL_FORUM." WHERE author = '$session->username'";
                $database->query($q);

Although this technique does not work...

 

And Barand I'll try you sugestion now

- Jamie

[fenway]

I suppose you *could* try to JOIN them all -- seems too scary to bother.

[DavidAM]

If you are using mysqli you can use the mysqli.multi-query function.

 

In any case, I would create a PHP function; start a TRANSACTION; execute the DELETEs one-by-one checking for success or failure after each; if ALL succeeded, I would COMMIT the transaction, if any fail, I would ROLLBACK. I have never, and would never, try to delete from multiple tables in one statement. I'm just too paranoid that way.

 

Another option is to create a STORED PROCEDURE to handle the TRANSACTION and the DELETEs.

[Christian F.]

I would have set up the foreign key relations to cascade the deletion, and let the database sort the rest out after I deleted the user from the user table. Easiest way to do it, if you ask me.

[DavidAM]

True, provided you are using innoDB. Of course, not all engines support the TRANSACTION option, either.

 

A while back, I wrote an application for the company where I work. After a while, I dumped the data to load on my development server to test some code changes I was making. When I loaded the dump, it created all of the tables as myIsam. Apparently, the (production) host did not support innoDB, and silently created all of the tables as myIsam. The dump did include all of my foreign key constraints and the server did create indexes for them, but the cascade deletes and updates were not active in production; and the dump specified the engine as myIsam.

 

Personally, I would have preferred that the production server fail the create table statements rather than convert them to a different engine. So I would have known about the lack of support for FKeys. Fortunately, this particular application did not have any DELETEs in it (at that point).

 

Just another "Gotcha" you have to watch out for when software is designed to "protect" the "script kiddies" from their own lack of knowledge. Along the same lines as "magic quotes" and "register globals" (IMO).