How to delete account using SQL if they are not active?

[eJay]

I would like to delete their account if player is not active for almost 5 months

using SQL script.

[Skorm]

Here's a quick and easy solution.

DELETE FROM `login` WHERE `lastlogin` < (CURRENT_TIMESTAMP - interval '5' month)

Although I do have to ask do you mean players not active for 5 months or a range of players from 4th months to the 5th month of inactivity, because from the way your sentence is worded it's hard to tell.

Stydianx's post reminded me that you have to delete characters to... but there is also a bunch of other information to delete.

CREATE TEMPORARY TABLE TmpTable AS (SELECT `account_id`,`char_id` FROM `char` WHERE `account_id` IN (SELECT `account_id` FROM `login` WHERE `lastlogin` < (CURRENT_TIMESTAMP - interval '5' month)));

#Add tables to delete from here.
DELETE FROM `guild` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `skill` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `quest` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `mercenary_owner` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `auction` WHERE `seller_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `mercenary` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `memo` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `inventory` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `hotkey` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `cart_inventory` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `homunculus` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `friends` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `global_reg_value` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `elemental` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `pet` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `party` WHERE `leader_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `guild_member` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `sc_data` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `storage` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `char` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `login` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `login` WHERE `lastlogin` < (CURRENT_TIMESTAMP - interval '5' month);

DROP TABLE TmpTable;

Will delete all accounts that haven't logged in for less than 5 months.

Edited July 7, 2013 by Skorm

[stydianx]

go to your PhpAdmin, then go to your Main_db (the sql db of your server) go to char then delete "ALL" characters that belongs to the account.

then go to login (can also be found on Main_db) then look for the account you want to delete, when you see it, hit "delete".

 

done.

 

NOTE: A character is tied to a number i.e:

 

Main_db > char

2000097 username password F [email protected] groupid

 

Main_db > login

 

char_id 2000097 char_num charactername class baselevel

Edited July 7, 2013 by stydianx

[eJay]

Thanks i'll try this