Need help [ SQL ]

[PandaLovesHamster]

prontera,155,175,5	script	Ranking#pvp	123,{

if(getgmlevel() >= 99){
	mes "[ Ranking ]";
	mes "What do you want to do?";
	menu "View Ranking",-,"Reset Ranking",OnResetRanking,"Update Ranking",OnRankingUpdate;
}
	mes "[ Ranking ]";
	mes "Top Players - Kills / Deaths";
	
	while(.@i < 10){
		mes .@i+1+". "+.top_name$[.@i]+" "+.kill_count[.@i]+" "+.top_death[.@i];
		.@i++;
	}
	
	close;
	end;
	
OnResetRanking:
	query_sql "DELETE FROM `main`.`char_reg_num` WHERE `key`='kill_count'"; //update this line
	query_sql "DELETE FROM `main`.`char_reg_num` WHERE `key`='death_count'"; //update this line
	deletearray .top_name$,getarraysize(.top_name$);
	deletearray .top_death,getarraysize(.top_death);
	deletearray .id,getarraysize(.id);
	deletearray .kill_count,getarraysize(.kill_count);
	mes "Ranking reset confirmed.";
	close;
	end;

	
OnRankingUpdate:
	mes "Ranking updated.";
	close2;
OnMinute30:
OnInit:
	query_sql(" SELECT `char_id`,`value` FROM `char_reg_num` WHERE `key` = 'kill_count' ORDER BY value DESC LIMIT 10 ",.id,.kill_count);
		for(set .@i,0; .@i < getarraysize(.id); .@i++){
			query_sql(" SELECT `name` FROM `char` WHERE `char_id` = '"+.id[.@i]+"'",.name$);
			query_sql(" SELECT `value` FROM `char_reg_num` WHERE `key` = 'death_count' AND `char_id` = '"+.id[.@i]+"'",.death_count);
			setarray .top_name$[.@i],.name$[.@i];
			setarray .top_death[.@i],.death_count[.@i];
		}
end;
}

Well, the issue with the script is that it does not show the proper output.

I suspect it's because of this line:

query_sql(" SELECT `char_id`,`value` FROM `char_reg_num` WHERE `key` = 'kill_count' ORDER BY value DESC LIMIT 10 ",.id,.kill_count);

But I have no idea how to manipulate the ORDER BY <column name>, will it be more appropriate to just use a new table for this? I hope that there can be a solution.

Thanks.

[PandaLovesHamster]

Fixed by using:

			setarray .top_name$[.@i],.name$;
			setarray .top_death[.@i],.death_count;

Edited April 10, 2016 by PandaLovesHamster

[Capuche]

It could be optimized but the query seems correct. However the correct value of the variable for players online may differ from the sql variable value. You need to check the variable value for players offline differently from players online.

[PandaLovesHamster]

How do I go about it? Or would it be better if I just add a new database? But I'd really prefer if I can just limit it to what's already available rather than adding another database.

[Emistry]

perhaps you should show us the "output" that you get.

and describe what should be the "proper output" you looking for.

 

the script itself update every hour.

but the database update every short interval, 300 seconds if i recall correctly.