Sql Queries

[john_d]

While i was building Our new webby i stumbled to some new things...

1. Vault Locking Query

Update WareHouse set pw = 1234 where accountid = 'youraccount';

- 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 1111
- youraccount : is ur mu username

2. Looking for someone Online.. but tired of making this query

Select memb___id, ServerName from Memb_stat where connectstat = 1;

- which only shows u usernames, and servername and not the actual Character name.

New version

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;

- now this one shows... online character with username and server

3. Looking for all accounts in a IP or IP range

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.%';

- now this show all accounts from an IP range in example it is 123.123.123.1 to 255
- % - is the wildcard.. u can also use ? for single characters

4. From 3.. to ban all account from an IP or IP range

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.%';

- this will block all accounts from the ip range 123.123.123.1 to 255
- % 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

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;

Offline

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;

- Top 100 - this is stop the script from flooding you. change it to anything u want. let say Top 10 or even TOP 1000
- 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

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;

ELF

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;

BK

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;

MG

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;

--- thats all for now.. i have some more.. but i you already know them all... if u have questions.. Or SQL request. just ask.

--- 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%)

[MentaL]

[john_d]

Space Reserved For More Scripts..

Below admin.php
is a basic script for administering you characters...

features:
- edit character [stats, exp, lvl, money,class]
- stats calculated to show how much is boosted
- inventory viewer (buggy but u get the idea)
- vault viewer (buggy)
- shows other character from the account for Quick links
- show last ip login /time connected /wat server
- shows other accounts from that ip.

[WYWH]

great jobing :)

waiting for ur site...

[john_d]

Second for a sampler of the admin section im working on

[WYWH]

check PMs john...

[john_d]

Dude... better change me Y! messenger now!.. while im still online...i got 3hour until i go out... so i can help you guys fix that problem!

[WYWH]

ur msn is?

[john_d]

i cant go msn.. lags be bad.. please use yahoo!

jdu_kim@yahoo.com

[WYWH]

i dont have yahoo :(

[john_d]

try now.. im in msn!... just for this hour.. same id jdu_kim@yahoo.com

[john_d]

updates in post 2

[ZyB-Er]

Excellent work :D.....
i have an idea..can u make in admins..panel....an option..."Ban Account"...but...in days...?..or Block some char and not all the account.
Or another...make an account for X days (i guess Charge info or some like that.) and get automatically Blocked or some like that....
just giving u ideas...

[john_d]

i have already added in my newer one.. a input for ip and it will automatically search all ip and blocks all accounts in them.. output is accounts that are banned..

[SpiritHawk]

john need a little more explanation of this is it possible i can run a new job for sql manager to check it automatically like auto reset

[john_d]

U wanna do auto cron job on sql.. hmmm.. most people just do obdc event.. like the auto level reset.

some do php pages with interval on them.

[jackass]

yeah sum no noobs ^^!

[john_d]

Spirit i like ur . Sig Image.. did u get it from my source?

[SpiritHawk]

yes i did , what type of odc job i need to make?

[john_d]

i'll check some jobs i already have installed...

we have a 24h auto reseter. on the server. it happens every 11:30pm +2gmt

make some more.

currently busy now... i just realized all Mu script and webpages that have been release for the last few months have a very big vunerability..
lets just say.. anyone who knows how to hack can go into ur webpages and get any account they want. tried and test on 12 server webpages.. all worked.. some even have capabilites to bring down ur server... i suggest u protect ur webpages..
im almost done with mine.

[master_unknown]

what about the items they make?? how to trace it and delete it???

[john_d]

all items made via sql has 00000000 serial... it's like items sold in pedler.. they all have 00000000 serial.

so if u see exc items with 00000000 serial .. that would mean.. sql generated item.

additional info:
- hehehe.. ur question is how to i know wat on there inventory..
answer: make urself a admin/gm web panel that can take vault/inventory codes and decode it into english understandable view. example > is that beta gm panel i release weeks ago.
- lets say u wanna understand items codes.. easy.. use search in ragezone

[koaru*]

what would i enter to make, all items in inventory gone.?
k thanks

[Wolfx]

Soul Master

select name as DW,clevel,resets,sum(strength+dexterity+vitality+e nergy+leveluppoint) as points, sum((clevel*5)+104) as sumd, sum((strength+vitality+energy+dexterity+leveluppoi nt)-(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+leveluppoin t) desc;

ELF

Quote:
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+leveluppoi nt)-(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+leveluppoin t) desc;



BK

Quote:
select name as BK,clevel,resets,sum(strength+dexterity+vitality+e nergy+leveluppoint) as points, sum((clevel*5)+103) as sumd, sum((strength+vitality+energy+dexterity+leveluppoi nt)-(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+leveluppoin t) desc;



MG

Quote:
select name as MG,clevel,resets,sum(strength+dexterity+vitality+e nergy+leveluppoint) as points, sum((clevel*7)+104) as sumd, sum((strength+vitality+energy+dexterity+leveluppoi nt)-(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+leveluppoin t) desc;


My table where it would have to appear the points, reset, etc. She appears empty. You it could help me?

See the picture.. plz.. Thx for all.

[nkerz]

impressive, john! good job =)

[genesis]

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smallint.



this is what comes out when i run this


select name as DW,clevel,resets,sum(strength+dexterity+vitality+e nergy+leveluppoint) as points, sum((clevel*5)+104) as sumd, sum((strength+vitality+energy+dexterity+leveluppoi nt)-(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+leveluppoin t) desc;