[MySQL] Useful Queries For Administrators

Post Reply
User avatar
atom0s
Developer
Posts: 537
Joined: Thu Oct 25, 2012 9:52 am

[MySQL] Useful Queries For Administrators

Post by atom0s » Thu Jan 02, 2014 2:38 pm

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%'
Pull Costume ID From Model ID (mob_pools)
(Change Tiamat to the name of the mob you want the costume id of.)

Code: Select all

SELECT ((ORD(SUBSTR(`modelid`, 4, 1)) << 8) + (ORD(SUBSTR(`modelid`, 3, 1)))) 
    FROM mob_pools 
    WHERE `name` LIKE '%Tiamat%'

User avatar
atom0s
Developer
Posts: 537
Joined: Thu Oct 25, 2012 9:52 am

Re: [MySQL] Useful Queries For Administrators

Post by atom0s » Tue Oct 07, 2014 4:08 pm

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;

User avatar
atom0s
Developer
Posts: 537
Joined: Thu Oct 25, 2012 9:52 am

Re: [MySQL] Useful Queries For Administrators

Post by atom0s » Tue Oct 07, 2014 4:10 pm

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
Lookup Single Monster

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'

User avatar
whasf
Site Admin
Posts: 1312
Joined: Thu Jul 19, 2012 9:11 pm

Re: [MySQL] Useful Queries For Administrators

Post by whasf » Wed Oct 08, 2014 7:28 pm

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 :P
-- Whasf

User avatar
atom0s
Developer
Posts: 537
Joined: Thu Oct 25, 2012 9:52 am

Re: [MySQL] Useful Queries For Administrators

Post by atom0s » Thu Oct 09, 2014 12:16 am

whasf wrote:I still don't know why you tell people to use this complicated query instead of using the built in function :P
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.

Post Reply