User deletion> do you also need to delete your project and then steps for that project?
Really stuck with this ... basically my system has 4 tables; users, projects, user_projects and activities. The user table has a usertype field that determines if they are an administrator or a user (integer) ...
The administrator can create a project, create an action for the project, and assign a user (restricted access user) to an action. Therefore, this setting means that the administrator is never directly associated with the activity (and not with the project).
When my admin head removes admin, I also need all projects and actions (for their projects). My deleting script for the user is simple so far and works, but I am having problems with how to get the project ID to find out which activities need to be removed (related to projects that need to be removed):
$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 requests are running fine, obviously because the user ID is being retrieved successfully. However the 4th and last request that I know is wrong because there is no project that can be accessed from anywhere, however I put it there to help understand what I am trying to get!
I guess I need something like "WHERE projectid =", then collect some remote projects from the userid that might be related to activities for that project (s)! It's a simple concept, but I'm having problems ...
a source to share
Must Read: MySQL Manual - FOREIGN KEY Constraints .
Pay close attention to triggers , this makes all the hard lifting for you. =) ON DELETE
CASCADE
a source to share
You might want to look at the syntax DELETE
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 question, 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']);
a source to share
If I understand your question correctly, you need to reorder your queries first. Before deleting projects, you must delete project actions for a user. By doing this in the order listed, you lose information needed for subsequent deletions.
So try them in this order (I haven't used the actual syntax to make it clearer what I am 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 legally obligated to recommend that you use bind variables rather than creating SQL strings on the fly. It looks like you are filling out these lines yourself, but such code is subject to SQL Injection and you probably don't want that.
a source to share