Veryfing Accounts - delete inactive

things needed:
muonline server ( yes, really :))
mssql server installed on mixed mode auth.
working php server with MSSQL extension installed ( NOTE: MSSQL EXTENSION ! not mysql databases ! O.o )



Step 1: Preparing mssql server

open query analyzer and run this query:

Code:

---

ALTER TABLE MEMB_INFO
ADD verify INT NOT NULL DEFAULT 0;
ALTER TABLE Character
ADD verify INT NOT NULL DEFAULT 0;
ALTER TABLE AccountCharacter
ADD verify INT NOT NULL DEFAULT 0;
ALTER TABLE MEMB_STAT
ADD verify INT NOT NULL DEFAULT 0;
ALTER TABLE VI_CURR_INFO
ADD verify INT NOT NULL DEFAULT 0;
ALTER TABLE warehouse
ADD verify INT NOT NULL DEFAULT 0

---

Step 2: Downloading the script :lol:

Download the files attached to this topic ^^.

Step 3: Configuration

fill the config ( verify_conf.php ) file so it looks like this:

Code:

---

$servername = "your_servers_name";
$ipsql = "your_servers_ip";
$sqluser = "login_to_your_sql";
$sqlpass = "password_to_your_sql";

---

Ofc you will have to put there your real ip instead of your_servers_ip and so on..

Step 4: Easy as pie :)

Put the files on your web server ( apache will be great, setup tutorial comming soon )
Note! All files must be in same folder

Step 5: Getting started

your ready to go now, now what you have to do, is informing your players about incomming verificaton
( let your GM do this :) ), tell them about this on your forum etc. dont forget to give them a link to verifaction ( http://yourdomain.com/verify.php )
place to check out if their accounts are verificated is http://yourdomain.com/verifylist.php

Step 6: The major one.

Give your players a good amount of time to do this, on my server it is 2 weeks ( i do it each 3 mnths ), and after two weeks we are deleting unverificated acc's
so we have space for new players.

Step 7: Deleting non-active accounts

Time has passed, time to delete non-active accounts, to do this you should run this query in query analyzer:

Code:

---

DELETE FROM Character
WHERE verify = ('0');
DELETE FROM MEMB_INFO
WHERE verify = ('0');
DELETE FROM AccountCharacter
WHERE verify = ('0');
DELETE FROM VI_CURR_INFO
WHERE verify = ('0');
DELETE FROM MEMB_STAT
WHERE verify = ('0');
DELETE FROM warehouse
WHERE verify = ('0')

---

Step 8: Prepare your server to next verification

Just run this query:

Code:

---

UPDATE MEMB_INFO
SET verify = ('0');
UPDATE MEMB_STAT
SET verify = ('0');
UPDATE VI_CURR_INFO
SET verify = ('0');
UPDATE Character
SET verify = ('0');
UPDATE AccountCharacter
SET verify = ('0');
UPDATE warehouse
SET verify = ('0');

---

remove verification script from your server, and upload it again when u need to use it.



NOTE ! My english sux, so when sometihng is written wrong you have to change it :P
do not change nothing inside the queries, posted here on in attached files.

NOTE ! i dont have much time so i dont be able to supprot this topic,
for support go here: http://dhost.info/darkskyteam/index.php?showtopic=17

NOTE ! if u want to use this script, link back to us on your webpage, and dont remove copyrights, its also peace of cake, am i right ?:D

enjoy,
fiszol

Nice!! I will try use! hehe


[]'s

Gj :d

Weeeeeeeeeeeee.. a very WEbzen like thing to do.. but i guess it is the simpliest one.. another things would be make a super store procedure... but this is great and simple..

question is.. wat if some users are on vacation, and didnt get to verify.. lolz.. bye bye 5month old account.

hehehe.. this code is nice..

jaa its great :thumbup: THX :thumbup:

I need help! I did all what in the guida was told to do, exept for the conection to your server! I used "MuOnline_Server_Version_0.97_With_Items_From_Version_0.99" and i dont get it how to make clint to mine server???? I owerwatced all files and all i found was client exe!!!! Cold someone givi me a link or tell what to do??

where is query analizer? =S

yess good work my friend ThX

or you could only verify accounts that are inactive for say like 6months active users need not do that.

errr, this doesnt work that great! The verify list says that there are 658 accounts are verified, but there are only 589 left!
Oh and it wiped most of the characters Geez...

there are users that only visits our website and tests our registration. so technically there a lot of accounts with empty characters. use the following script to generate list of empty accounts then its up to you what to do with it


NOTE: you need to create table named "NullAccounts"
with fields Username (nvarchar=10) and Status (nvarchar=10)
*you can change the table name and fields if you want

Quote:

---

truncate table nullaccounts;
insert into nullaccounts SELECT a.memb___id, b.AccountID FROM MEMB_INFO a
LEFT JOIN (SELECT DISTINCT AccountID FROM [Character]) b
ON a.memb___id = b.AccountID
WHERE b.AccountID IS NULL;

---

Quote:

---

Originally Posted by Resupro

where is query analizer? =S

---

Ur sux.... how don't you know where SQL Query Analyzer???????


start>programs>Microsoft Sql Server>Query Analyzer LOL!!!!

nice job.

i had this query once to delete any inactive accounts+ the other tables with it.. but it could only delete a few before timing out. i never did figure out why.. or wat too lazy to even try.

Quote:

---

Originally Posted by john_d

i had this query once to delete any inactive accounts+ the other tables with it.. but it could only delete a few before timing out. i never did figure out why.. or wat too lazy to even try.

---

Lol. I also agree what if someone is on holiday? If webzen did, they will have ALOT of hatemail....

i already have a lot of hate mail.. so doesnt really bother me.

one think to do about the holiday part. is use the MUtoolz hybernation.

if some1 s in hybernation the script wont delete them.

btw: hybernation is memb_info bloc_code =2

(",) i agree with u john_d that is why i suggest we only delete account with no characters in it. at leat we are pretty sure that they only tried our registration page.

dont dont work whit MD5 pass