Page 1 of 1
[MySQL] Useful Queries For Administrators
Posted: Thu Jan 02, 2014 2:38 pm
by atom0s
Pull Costume ID From Model ID (mob_change_skin)
(Change pand to the name of the mob you want the costume id of.)
Code: Select all
SELECT ((ORD(SUBSTR(`skin_model`, 4, 1)) << 8) + (ORD(SUBSTR(`skin_model`, 3, 1))))
FROM mob_change_skin
WHERE skin_name LIKE '%pand%'
Code: Select all
SELECT ((ORD(SUBSTR(`modelid`, 4, 1)) << 8) + (ORD(SUBSTR(`modelid`, 3, 1))))
FROM mob_pools
WHERE `name` LIKE '%Tiamat%'
Re: [MySQL] Useful Queries For Administrators
Posted: Tue Oct 07, 2014 4:08 pm
by atom0s
Calculate the zone_ip via MySQL
Code: Select all
SET @ip = '12.34.56.78'; -- edit the address listed here!
SELECT (o4 << 24) | (o3 << 16) | (o2 << 8) | o1 AS ip_address
FROM (SELECT SUBSTRING_INDEX(@ip, '.', 1) AS o1,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', 2 ), '.', -1) AS o2,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', -2 ), '.', 1) AS o3,
SUBSTRING_INDEX(@ip, '.', -1) AS o4) AS t1;
Re: [MySQL] Useful Queries For Administrators
Posted: Tue Oct 07, 2014 4:10 pm
by atom0s
Obtain a full list of all notorious monsters. (Includes id, name, position and zoneid.)
Code: Select all
SELECT mobid, mobname, pos_x, pos_y, pos_z, dspdb.mob_groups.zoneid FROM dspdb.mob_spawn_points
INNER JOIN dspdb.mob_groups ON dspdb.mob_spawn_points.groupid = dspdb.mob_groups.groupid
INNER JOIN dspdb.mob_pools ON dspdb.mob_groups.poolid = dspdb.mob_pools.poolid
WHERE ((dspdb.mob_pools.mobType & 2) > 0)
ORDER BY mobname
Code: Select all
SELECT mobid, mobname, pos_x, pos_y, pos_z, zoneid FROM dspdb.mob_spawn_points
INNER JOIN dspdb.mob_groups ON dspdb.mob_spawn_points.groupid = dspdb.mob_groups.groupid
WHERE mobname = 'Cerberus'
Re: [MySQL] Useful Queries For Administrators
Posted: Wed Oct 08, 2014 7:28 pm
by whasf
atom0s wrote:Calculate the zone_ip via MySQL
Code: Select all
SET @ip = '12.34.56.78'; -- edit the address listed here!
SELECT (o4 << 24) | (o3 << 16) | (o2 << 8) | o1 AS ip_address
FROM (SELECT SUBSTRING_INDEX(@ip, '.', 1) AS o1,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', 2 ), '.', -1) AS o2,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', -2 ), '.', 1) AS o3,
SUBSTRING_INDEX(@ip, '.', -1) AS o4) AS t1;
I still don't know why you tell people to use this complicated query instead of using the built in function
Re: [MySQL] Useful Queries For Administrators
Posted: Thu Oct 09, 2014 12:16 am
by atom0s
whasf wrote:I still don't know why you tell people to use this complicated query instead of using the built in function
Because a lot of the time people skim over the install instructions and entirely miss the part where the IP has to be reversed. This handles that for the person automatically.