[MySQL] Useful Queries For Administrators

[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%'

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

[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;

[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

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'

[whasf]

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

[atom0s]

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.