While i was building Our new webby i stumbled to some new things...
1. Vault Locking Query
- pw : is the 4 digit vault code... that if u try to take something out of the vault, it wil ask u for it. only use numbers.. dont repeat number like 4444 or 1111Quote:
Update WareHouse set pw = 1234 where accountid = 'youraccount';
- youraccount : is ur mu username
2. Looking for someone Online.. but tired of making this query
- which only shows u usernames, and servername and not the actual Character name.Quote:
Select memb___id, ServerName from Memb_stat where connectstat = 1;
New version
- now this one shows... online character with username and serverQuote:
Select AccountCharacter.GameIDC, memb_stat.memb___id, memb_stat.ServerName from Memb_stat join AccountCharacter on Memb_stat.memb___id = AccountCharacter.ID collate Latin1_general_CI_AS where Memb_stat.connectstat = 1;
3. Looking for all accounts in a IP or IP range
- now this show all accounts from an IP range in example it is 123.123.123.1 to 255Quote:
select DISTINCT s.ip, memb_info.memb___id, memb_info.bloc_code from memb_info join memb_stat s on memb_info.memb___id = s.memb___id collate Latin1_general_CI_AS where s.ip like '123.123.123.%';
- % - is the wildcard.. u can also use ? for single characters
4. From 3.. to ban all account from an IP or IP range
- this will block all accounts from the ip range 123.123.123.1 to 255Quote:
update memb_info set memb_info.bloc_code = 1 from memb_info join memb_stat s on memb_info.memb___id = s.memb___id collate Latin1_general_CI_AS where s.ip like '123.123.123.%';
- % look in 3
5. Some fun stuff: Let says u want to make a section in website dedicated to all those mass pkers who are online or offline... lets say the section is "People you should avoid"
Online
OfflineQuote:
Select top 100 c.name,c.pkcount,m.ServerName from memb_stat m join character c on m.memb___id = c.accountid collate Latin1_general_CI_AS where m.connectstat = 1 order by pkcount desc;
- Top 100 - this is stop the script from flooding you. change it to anything u want. let say Top 10 or even TOP 1000Quote:
Select top 100 c.name,c.pkcount,m.ServerName from memb_stat m join character c on m.memb___id = c.accountid collate Latin1_general_CI_AS where m.connectstat = 0 order by pkcount desc;
- The online needs revision ... done in 2
6. OMG u sql got hacked.. some characters are now edit .. stats boosted. but ur too lazy to check them one by one.. here is some queries that should do the checking for u.
Soul Master
ELFQuote:
select name as DW,clevel,resets,sum(strength+dexterity+vitality+energy+leveluppoint) as points, sum((clevel*5)+104) as sumd, sum((strength+vitality+energy+dexterity+leveluppoint)-(clevel*5)+101) as dif from character where ctlcode != '8' and class = '1' group by name, resets, clevel order by sum(strength+vitality+energy+dexterity+leveluppoint) desc;
BKQuote:
select name as ELF,clevel,resets,sum(strength+dexterity+vitality+energy+leveluppoint) as points, sum((clevel*5)+104) as sumd, sum((strength+vitality+energy+dexterity+leveluppoint)-(clevel*5)+102) as dif from character where ctlcode != '8' and class = '33' group by name, resets, clevel order by sum(strength+vitality+energy+dexterity+leveluppoint) desc;
MGQuote:
select name as BK,clevel,resets,sum(strength+dexterity+vitality+energy+leveluppoint) as points, sum((clevel*5)+103) as sumd, sum((strength+vitality+energy+dexterity+leveluppoint)-(clevel*5)+103) as dif from character where ctlcode != '8' and class = '17' group by name, resets, clevel order by sum(strength+vitality+energy+dexterity+leveluppoint) desc;
--- thats all for now.. i have some more.. but i you already know them all... if u have questions.. Or SQL request. just ask.Quote:
select name as MG,clevel,resets,sum(strength+dexterity+vitality+energy+leveluppoint) as points, sum((clevel*7)+104) as sumd, sum((strength+vitality+energy+dexterity+leveluppoint)-(clevel*7)+104) as dif from character where ctlcode != '8' and class = '48' group by name, resets, clevel order by sum(strength+vitality+energy+dexterity+leveluppoint) desc;
--- PHP version script on all these plus more soon. ( inventory viewer at 75% complete, character researcher at 80% complete, complete admin panel 50%, MuGlobal Php Version at 99%)
