I was curious if there is someone a little more familiar with SQL/PHP than I am that could throw in some suggestions. I'm trying to clean out my database. The Profiles and Survivor tables to be more specific. I want to clean out people who haven't been on in X amount of days. Just trying to keep things easy to manage. I guess it wouldn't be a bad thing if I couldn't do it. I really am just trying to clear data from people who log on once and never to be seen again. Thanks!
Cleaning the Database
- Thread starter Gunhaver
- Start date
snipes
New Member
Backup your database before attempting this as I'm just doing this off the top of my head here. I assume you are using something like HeidiSqL or mysql workbench to manage your databse.
Try the following.
DELETE FROM survivor, instance_deployable
USING survivor
LEFT JOIN instance_deployable ON survivor.id = instance_deployable.owner_id
WHERE survivor.last_updated < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
This will only remove the records from the survivor table and the tents/deployables that are linked to them. If you don't want the tents to disappear you will need to use a DELETE IGNORE and modify the above as any character with a tent will make the query fail due to foreign key constraints.
Leaving the records in the profile table is useful if a player returns after more than a month away but if you must delete then you need to do a delete count query e.g.
You could run the above query and then run
DELETE FROM profile
WHERE NOT EXISTS
( SELECT * FROM survivor
WHERE profile.unique_id = survivor.unique_id);
There's an all encompassing route but this is the 'easiest' for people to understand and allows you just to remove the clutter out of the survivor table.
Is there a reason you want to do this though?
Also you may need to disable safe update mode in MySql.
I'll give this a try when i get home but it should be ok, maybe someone can confirm first?
Try the following.
DELETE FROM survivor, instance_deployable
USING survivor
LEFT JOIN instance_deployable ON survivor.id = instance_deployable.owner_id
WHERE survivor.last_updated < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
This will only remove the records from the survivor table and the tents/deployables that are linked to them. If you don't want the tents to disappear you will need to use a DELETE IGNORE and modify the above as any character with a tent will make the query fail due to foreign key constraints.
Leaving the records in the profile table is useful if a player returns after more than a month away but if you must delete then you need to do a delete count query e.g.
You could run the above query and then run
DELETE FROM profile
WHERE NOT EXISTS
( SELECT * FROM survivor
WHERE profile.unique_id = survivor.unique_id);
There's an all encompassing route but this is the 'easiest' for people to understand and allows you just to remove the clutter out of the survivor table.
Is there a reason you want to do this though?
Also you may need to disable safe update mode in MySql.
I'll give this a try when i get home but it should be ok, maybe someone can confirm first?
Code:
DELETE FROM survivor, instance_deployable
USING survivor
LEFT JOIN instance_deployable ON survivor.id = instance_deployable.owner_id
WHERE survivor.last_updated < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);doesn't work for me.
Reason:
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`celle2013`.`instance_deployable`, CONSTRAINT `instance_deployable_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `survivor` (`id`))