Descending Order SQL Script

[Xagul]

Hello, I am currently having some trouble with executing an SQL Query via talkaction. Basically this SQL Query is suppose to find everyone with x storagekey and order the value of that storagekey however when I test the script I get this:

21:17 Descending Order:
2[9]
7[60]
6[19]
5[11]

It looks like it is ordering it in descending order but not properly because its reading the number from left to right and it needs to be reading it from right to left in order to properly sort the numbers by descending order.

Here is the script I am currently using, it would be great if anyone knows a way around this.

function onSay(cid, words, param, channel)
			
	local list = db.getResult("SELECT `player_id`, `key`, `value` FROM `player_storage` WHERE `key` = 7214 ORDER BY `value` DESC LIMIT 0, 4;")
	if(list:getID() ~= -1) then
		local v = 'Descending Order:\n'
		repeat
			v = v .. list:getDataString("player_id") .. "[" .. list:getDataString("value")  .. "]\n"
		until not list:next()
		list:free()
		doBroadcastMessage(v, MESSAGE_STATUS_CONSOLE_RED)
	end
	return true
end

 

[Cykotitan]

Known issue :/

The column's datatype has to be integer (INT)

CREATE TABLE `player_storage`
(
	`player_id` INT NOT NULL DEFAULT 0,
	`key` INT UNSIGNED NOT NULL DEFAULT 0,
[B]	`value` [COLOR="Red"]VARCHAR(255)[/COLOR] NOT NULL DEFAULT '0',[/B]

To fix it, execute this query:

ALTER TABLE `player_storage`
MODIFY `value` INT UNSIGNED NOT NULL DEFAULT 0

Not sure if there are going to be any side effects, make a backup of this table just in case.

 

[Xagul]

Is it fixable or? If so and you change to integer then any storage values you are currently using to hold a word would be unusable wouldn't it?

 

[Cykotitan]

F5.
and,

Is it fixable or? If so and you change to integer then any storage values you are currently using to hold a word would be unusable wouldn't it?

that's right. :/
Not sure how often people store strings in storage keys.

 

[Xagul]

I have a few times due to the convenience of it so this kinda sucks do you think there would be a way to execute the query to pull up all the values and then have the server turn them into numbers "using like tonumber(...)" and then order it?

 

[Cykotitan]

Uhm actually, you could have Lua sort it instead

 

[Xagul]

how would I go about doing that xD I have never done it

 

[Cykotitan]

function onSay(cid, words, param, channel)
	local list = db.getResult('SELECT `player_id`, `key`, `value` FROM `player_storage` WHERE `key` = 7214 LIMIT 0, 4;')
	if(list:getID() ~= -1) then
		local v, t = 'Descending Order:\n', {}
		repeat
			table.insert(t, {tonumber(list:getDataString('player_id')), tonumber(list:getDataString('value'))})
		until not list:next()
		list:free()
		table.sort(t, function(a,b) return a[2]>b[2] end)
		for i = 1, #t do
			v = v .. t[i][1] .. '[' ..  t[i][2] .. ']\n'
		end
		doBroadcastMessage(v, MESSAGE_STATUS_CONSOLE_RED)
	end
	return true
end

?

 

[Xagul]

21:50 Descending Order:
7[60]
6[19]
5[11]
2[9]

Works awesome but those are the only 4 values in that table, when I try to limit it to only 3/4 like so

local list = db.getResult('SELECT `player_id`, `key`, `value` FROM `player_storage` WHERE `key` = 7214 LIMIT 0, 3;')

then it skips the 60 for some reason and shows

21:51 Descending Order:
6[19]
5[11]
2[9]

 

[Cykotitan]

Because value 60 was in ~4[or 5?]th row?

I'd suggest you remove LIMIT, because it's not possible to use it effectively with larger tables in this case because we're sorting the results in Lua.

 

[Xagul]

Yea I just realized the problem xD so there will have to be no limit in the SQL Query however with no limit there will be a huge list shown so might there be a way to limit it in lua aswell?

 

[Cykotitan]

function onSay(cid, words, param, channel)
	local list = db.getResult('SELECT `player_id`, `key`, `value` FROM `player_storage` WHERE `key` = 7214 LIMIT 0, 4;')
	if(list:getID() ~= -1) then
		local v, t = 'Descending Order:\n', {}
		repeat
			table.insert(t, {tonumber(list:getDataString('player_id')), tonumber(list:getDataString('value'))})
		until not list:next()
		list:free()
		table.sort(t, function(a,b) return a[2]>b[2] end)
		for i = 1, [B][COLOR="Red"]math.min(#t, 5)[/COLOR][/B] do
			v = v .. t[i][1] .. '[' ..  t[i][2] .. ']\n'
		end
		doBroadcastMessage(v, MESSAGE_STATUS_CONSOLE_RED)
	end
	return true
end

(math.min was incase there's less results than 5 [unlikely])

 

[Xagul]

Yea that works perfectly going to see if I can finish this script now without running into to many problems xD thanks a ton Cykotitan