Mysql Mass Delete [Resolved]

[Epiales]

Okay, I've created an admin panel where I can update and delete users. Only problem is, is that I have information stored in several tables for user information. I have successfully worked out an innerjoin for recalling user information from two table, thanks for some of ya on here YAY But how do I go about deleting the user information from MORE than 2 tables? Here's the tables that I have user information stored in.

business
farms
inventory
locations
logs
rankings
stats
user
store
unit
weapon

If we're talking about an innerjoin for delete, well, I'm gonna die LMAO! Doing two at once was good enough for me LOL. Would it be best just to leave that information there after their username is deleted? Any suggestions or info? Thanks.

[MikuzA]

If the database table aren't connected in any way (foreign key / primary key etc), then I guess the only way is to inner join delete.

This can be used if all tables ID's are referencing to user.ID

Code: Select all

DELETE FROM
		business
		farms
		inventory
		locations
		logs
		rankings
		stats
		user
		store 
		unit
		weapon
USING user
		INNER JOIN business ON user.id = business.id
		INNER JOIN farms ON user.id = farms.id
		INNER JOIN inventory ON user.id = inventory.id
		INNER JOIN locations ON user.id = locations.id
		INNER JOIN logs ON user.id = logs.id
		INNER JOIN rankings ON user.id = rankings.id
		INNER JOIN stats ON user.id = stats.id
		INNER JOIN store ON user.id = store.id
		INNER JOIN unit ON user.id = unit.id
		INNER JOIN weapon ON user.id = weapon.id
WHERE user.id = '1';

[vitinho444]

Well.. not sure what is the problem here because it seems very easy to me.

I assume you have some kind of ID in each table to identify the player right?
If yes i would create a function to delete or change anything in a row.

Like:

Code: Select all

function DeleteUser($userID)
{
SQLQUERY("DELETE FROM `business` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `farms` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `locations` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `inventory` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `logs` WHERE `ID`='$userID' ");
(...)
}

 

Then in the admin panel on a click of a button it would be as simple as calling DeleteUser($userID); and that user is gone from your table.
To get all info a similar function can be used that could create arrays for each table for that specific user then you would call the function and after that all the arrays variables would be created and able to use.

Hope it helps

[Epiales]

If the database table aren't connected in any way (foreign key / primary key etc), then I guess the only way is to inner join delete.

This can be used if all tables ID's are referencing to user.ID

Code: Select all

DELETE FROM
		business
		farms
		inventory
		locations
		logs
		rankings
		stats
		user
		store 
		unit
		weapon
USING user
		INNER JOIN business ON user.id = business.id
		INNER JOIN farms ON user.id = farms.id
		INNER JOIN inventory ON user.id = inventory.id
		INNER JOIN locations ON user.id = locations.id
		INNER JOIN logs ON user.id = logs.id
		INNER JOIN rankings ON user.id = rankings.id
		INNER JOIN stats ON user.id = stats.id
		INNER JOIN store ON user.id = store.id
		INNER JOIN unit ON user.id = unit.id
		INNER JOIN weapon ON user.id = weapon.id
WHERE user.id = '1';

This makes sense Thanks! I will have to experiment.

[Epiales]

Well.. not sure what is the problem here because it seems very easy to me.

I assume you have some kind of ID in each table to identify the player right?
If yes i would create a function to delete or change anything in a row.

Like:

Code: Select all

function DeleteUser($userID)
{
SQLQUERY("DELETE FROM `business` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `farms` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `locations` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `inventory` WHERE `ID`='$userID' ");
SQLQUERY("DELETE FROM `logs` WHERE `ID`='$userID' ");
(...)
}

Then in the admin panel on a click of a button it would be as simple as calling DeleteUser($userID); and that user is gone from your table.
To get all info a similar function can be used that could create arrays for each table for that specific user then you would call the function and after that all the arrays variables would be created and able to use.

Hope it helps

I'll experiment with this as well. Thanks

[MikuzA]

You should definetly go with vitinho444's suggestion if it's going to be sort of part of the Administrative functionality.

Creating a function/page dedicated for the deletion, one table and one query is much more easy to administer in long run.

However, if you need to do a development fast sweep of database to avoid truncation, that's when you can use a innerjoin deletion. Otherwise it is highly NOT recommended

[Epiales]

Yea, but how would you run the function to delete from the database? I know how to run the innerjoin now, but using a function?

[Epiales]

I"m thinking this might work for me... any ideas?

Code: Select all

if(isset($_POST['delete'])){


$tables = array('user', 'stats', 'locations');
foreach ($tables as $table) {

$DeleteQuery = "DELETE FROM $table WHERE id='$_POST[hidden]'";
mysql_query($DeleteQuery);
};
};

*EDITED* Yeah, tried and tested. Works fine. Just have to put all the tables with the same userid in there and then it will delete them all at once. YAY

[vitinho444]

I never worked with innerjoin, thats why i cant really say i know MYSQL or any language.. i know how to use them for what i use them, that's it..

Happy to see it worked

[Epiales]

I never worked with innerjoin, thats why i cant really say i know MYSQL or any language.. i know how to use them for what i use them, that's it..

Happy to see it worked

Thanks, I"m learning heaps lol. YAY