Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

Sql Queries

Custom Title Activated
Member
Joined
Feb 27, 2004
Messages
1,378
Reaction score
50
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%)
 
Custom Title Activated
Member
Joined
Feb 27, 2004
Messages
1,378
Reaction score
50
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.
 

Attachments

You must be registered for see attachments list
Last edited:
Custom Title Activated
Member
Joined
Feb 27, 2004
Messages
1,378
Reaction score
50
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!
 
Initiate Mage
Joined
Mar 18, 2004
Messages
1
Reaction score
0
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...
 
Custom Title Activated
Member
Joined
Feb 27, 2004
Messages
1,378
Reaction score
50
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..
 
Banned
Banned
Joined
Sep 1, 2004
Messages
170
Reaction score
5
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
 
Custom Title Activated
Member
Joined
Feb 27, 2004
Messages
1,378
Reaction score
50
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.
 
Custom Title Activated
Member
Joined
Feb 27, 2004
Messages
1,378
Reaction score
50
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.
 
Back
Top