Deleting a user > need to also delete their project, and then activities for that project? (PHP, MySQL)

Question

Hi guys,

Really stuck with this... basically my system has 4 tables; users, projects, user_projects and activities. The user table has a usertype field which defines whether or not they are admin or user (by an integer)...

An admin can create a project, create an acitivity for the project and assign a user (limited access user) an activity. Therefore, this setup means that an admin is never directly associated with an activity (instead a project).

When my head admin user deletes an admin, I need all projects and activities (for their projects) to be deleted also. My delete script for a user is simple so far and works, but I'm having trouble on how to gain the projectID in order to know which activities to remove (associated with the projects which are about to be deleted):

$userid = $_GET['userid'];

$query = "DELETE FROM users WHERE userid=".$userid;
$result = mysql_query($sql, $connection)
    or die("Error: ".mysql_error());

$query = "DELETE FROM projects WHERE userid=".$userid;
$result = mysql_query($sql, $connection)
    or die("Error: ".mysql_error());

$query = "DELETE FROM userprojects WHERE userid=".$userid;
$result = mysql_query($sql, $connection)
    or die("Error: ".mysql_error());


$query = "DELETE FROM activities WHERE projectid=".$projectid;
$result = mysql_query($sql, $connection)
    or die("Error: ".mysql_error());

Now the first three queries execute fine, obviously because the userid is being retrieved successfully. However the 4th and final query I know is wrong, because there is no projectid to be gained from anywhere, however I put it there to help understand what I am trying to get!! :D

Im guessing that i would need something like 'WHERE projectid=' then something to gather the removed projects from the userid which can be related to the activities for that project(s)!! Its a simple concept but I'm having trouble...please excuse any bad code as I am learning also. Thanks for any help!

Answer 1

If I understand your question correctly, you have to first re-order your queries. Before you delete the Projects, you have to delete the activities for the projects for the user. By doing it in the order stated, you lose the info required for later deletes.

So try them in this order (I did not use actual syntax to make it clearer what I was doing):

- delete from activities where projectid in (select projectid from projects where userid = $userid)
- delete from users...
- delete from projects...
- delete from userprojects...

As an aside, I am required by law to recommend you use bind variables instead of creating SQL strings on the fly like that. Seems like you are populating those strings yourself, but code like that is subject to SQL Injection, and you probably don't want that.

Answer 2

Must read: MySQL Manual - FOREIGN KEY Constraints.

Pay special attention to the ON DELETE CASCADE triggers, this does all the heavy lifting for you. =)

Answer 3

You might want to look at the multi-table DELETE syntax supported by MySQL:

$sql = "DELETE u, p, up, a 
        FROM users u
        LEFT OUTER JOIN projects p ON (u.userid = p.userid)
        LEFT OUTER JOIN userprojects up ON (u.userid = up.userid)
        LEFT OUTER JOIN activities a ON (p.projectid = a.projectid) 
        WHERE u.userid = {$userid}";

$result = mysql_query($sql, $connection)
    or die("Error: ".mysql_error());

As a side issue, please be careful about protecting against SQL Injection risks. Don't use web request parameters without filtering them. At the very least do something like this:

$userid = intval($_GET['userid']);