PvP Ranking & SQL

MukkiesftKies · June 3, 2018

OnPCKilLEvent:
		if ( getcharid(3) != killedrid && strcharinfo(3) == "prontera" ) {
			 set .@amount,rand(100,1000);
			set .@points, .@points + rand(100,1000);
			query_sql "insert into `mvp_ranking` value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) +"', 1 ) on duplicate key update `points` = point + rand(100,1000)";
		}
		end;
		
OnPCDieEvent:
		if ( getcharid(3) != killedrid && strcharinfo(3) == "prontera" ) {
			set .@amount,rand(100,1000);
			set .@points, .@points - rand(100,1000);
			query_sql "insert into `mvp_ranking` value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) +"', 1 ) on duplicate key update `points` = point + rand(100,1000)";
		}
		end;
}

How to do if rand point function with sql ?
i try to put something like this on duplicate key update `points` = point + rand(100,1000)";.

i got error.

AnnieRuru · June 4, 2018

On 6/4/2018 at 7:54 PM, sader1992 said:

i suggest you just use character variables , it's better for your script

curious .... do you actually mean telling others to make their own ladder type algorithm ... script ?
I'm actually interested if you can show me your txt style ladder script

On 6/4/2018 at 3:36 PM, MukkiesftKies said:

so this script working. but after player point going to 0. my map show this .


[SQL]: DB error - BIGINT UNSIGNED value is out of range in '(`ragnarok`.`mvp_ranking`.`points` - 11)'
[Debug]: at e:\game\ro\rathena-master\src\map\script.cpp:16697 - insert into `mvp_ranking` value ( 150002, 'lolo', 1 ) on duplicate key update `points` = `points` - 11
[Debug]: Source (NPC): MVP Rank at livion (148,149)

.@rand = rand(10,20);
query_sql "insert into `mvp_ranking value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) "', 0 ) on duplicate key update points = IF( points - "+ .@rand +" < 0, 0, points - "+ .@rand +" )";

Edited June 5, 2018 by AnnieRuru

sader1992 · June 4, 2018

prontera,159,185,4	script	PVP Rank	100,{
	mes "[PVP Rank]";
	.@rank = query_sql( "select `char_id`, `value` from `char_reg_num` where `key` = 'PVPPoints' AND `value` > '0' order by `value` DESC", .@char_id, .@pvp_points );
	for(.@i=0;.@i<.@rank;.@i++){
		query_sql( "select `name` from `char` where `char_id` = '" + .@char_id[.@i] + "'", .@name$ );
		mes (.@i + 1) + " " + .@name$ + " Have " + .@pvp_points[.@i] + " PVP Points";
	}
end;
OnPCKilLEvent:
	if(strcharinfo(3) == "prontera"){
		.@points = rand(100,1000);
		PVPPoints += .@points;
		dispbottom "You get ^FF0000" + .@points +"^000000 Points. Total =^FF0000" + PVPPoints +"^000000 Points.";
	}
	end;
OnPCDieEvent:
	if(killerrid >= 2000000)
		if(strcharinfo(3) == "prontera"){
			PVPPoints -= rand(100,1000);
			dispbottom "Total =^FF0000" + PVPPoints +"^000000 Points.";
			if(PVPPoints < 0)
				PVPPoints = 0;
		}
	end;
}

i just meant something like this @AnnieRuru

Edited June 4, 2018 by sader1992

Emistry · June 3, 2018

on duplicate key update `points` = `points` + "+rand(100,1000);

on duplicate key update `points` = `points` + " + .@points;

Edited June 3, 2018 by Emistry

AnnieRuru · June 3, 2018

#should be `points`
on duplicate key update `points` = `points` + "+ rand(100,1000);
on duplicate key update `points` = `points` + " .@points;


#SQL rand()
on duplicate key update `points` = `points` + floor( rand() * (1000-100+1) )+100;"

PS: your OnPCDieEvent: should use killerrid instead of killedrid

Spoiler


on duplicate key update `points` = `points` + floor( rand()*100 )*10;

sry this was wrong answer, sometimes rand() can random as 0.01 ...
means 0.01 * 100 = 1 * 10 = 10
but he request rand(100,1000) means it shouldn't random to get number 10

Edited June 6, 2018 by AnnieRuru

MukkiesftKies · June 4, 2018

I got it. set .@point same function with query_sql "insert into .

OnPCKilLEvent:
		if ( getcharid(3) != killedrid && strcharinfo(3) == "prontera" ) {
			//set .@amount,rand(100,1000);
			//set .@points, .@points + rand(10,20);
			query_sql "insert into `mvp_ranking` value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) +"', 1 ) on duplicate key update `points` = `points` + "+rand(10,20);
			query_sql("SELECT `points` FROM `mvp_ranking` WHERE `name`='"+strcharinfo(0)+"'",.@points);
			dispbottom "You get ^FF0000" + .@points +"^000000 Points. Total =^FF0000" + .@points +"^000000 Points.";
		}
		end;

so this script working. but after player point going to 0. my map show this .

[SQL]: DB error - BIGINT UNSIGNED value is out of range in '(`ragnarok`.`mvp_ran
king`.`points` - 11)'
[Debug]: at e:\game\ro\rathena-master\src\map\script.cpp:16697 - insert into `mvp_ranking` value ( 150002, 'lolo', 1 ) on duplicate key update `points` = `point
s` - 11
[Debug]: Source (NPC): MVP Rank at livion (148,149)

and the last thing. how to dispbtm show kill point and total point.

dispbottom "You get ^FF0000" + .@points +"^000000 Points. Total =^FF0000" + .@points +"^000000 Points.";

sader1992 · June 4, 2018

i suggest you just use character variables , it's better for your script

if you still want using sql

`points` = `points` + "+rand(10,20

you need to select the points and assign it to a variable than you say

.@variable += rand(10,20);

`points` = '" + .@variable + "'

AnnieRuru · June 4, 2018

eh ...
1. showing value from `char_reg_num` is not thread-safe ...
2. `value` field from `char_reg_num` is not index ...

well it seems the whole rathena community love to use this method ...
I'm not going to comment anymore

Spoiler

anyway about the TXT style ladder ...

http://herc.ws/board/topic/10967-req-top-leader-custom-mvp-hunter/?do=findComment&comment=65307

if(killerrid >= 2000000)

I think this condition is not needed for OnPCDieEvent
if he uses OnPCDieEvent, then we assume it will trigger whenever the player die .. no matter from which source

if he wants to make it trigger by player kill only then should have OnPCKillEvent, and attachrid killedrid; later

however the OnPCKillEvent need the killedrid check though,
Grand-Cross killing self can trigger both OnPCKillEvent and OnPCDieEvent

PS: I got notification several times while you editing your post

@sader1992

testing Mention ....

Edited June 4, 2018 by AnnieRuru
testing @mention ... heheheh ~

sader1992 · June 4, 2018

3 minutes ago, AnnieRuru said:

PS: I got notification several times while you editing your post

@sader1992

sorry first time i forgot a line xD

than when edit my reply i messed it up so i end up editing until 4 times :X

i am pretty sure you know better than me in scripting

and i don't really (know/think) that selecting from char_reg_num would do anything but i would be more careful in the future

(also i have bad grammar so i edit my posts a lot /sry )

MukkiesftKies · June 5, 2018

15 hours ago, AnnieRuru said:


.@rand = rand(-10,-20);
query_sql "insert into `mvp_ranking value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) "', 0 ) on duplicate key update points = IF( points - "+ .@rand +" < 0, 0, points - "+ .@rand +" )";

still same.

[SQL]: DB error - BIGINT UNSIGNED value is out of range in '(`ragnarok`.`mvp_ran
king`.`points` - 17)'
[Debug]: at f:\game\ro\rathena-master\src\map\script.cpp:16697 - insert into `mvp_ranking` value ( 150001, 'MINE', 0 ) on duplicate key update `points` = IF (
points - 17 < 0, 0, points - 17 )

Thanks all, this script working properly. /thx

i just change my sql table
Type int(11) to varchar(30)

Edited June 5, 2018 by MukkiesftKies

AnnieRuru · June 5, 2018

12 hours ago, MukkiesftKies said:

Thanks all, this script working properly.

i just change my sql table
Type int(11) to varchar(30)

I think you just have to change your INT(11) UNSIGNED into INT(11) SIGNED
yeah I reproduce your error

[SQL]: DB error - BIGINT UNSIGNED value is out of range in '(`rathena`.`pvpladder`.`points` - 14)'
[Debug]: at d:\ragnarok\rathena\src\map\script.cpp:16882 - insert into `pvpladder` value ( 150001, 'EnnyRuru', 0 ) on duplicate key update `points` =  IF( points - 14 < 0, 0, points - 14)
[Debug]: Source (NPC): kjsdhfj at prontera (155,185)

if you make your `points` field as varchar(30), then when you list out the top 10 players,
let's say player A has 20 points and player B has 9 points,
the script will show player B has higher score than player A, because the number '9' is bigger than number '2' (remember this is a string)
you have to cast the `points` field as Integer like this

SELECT * FROM `pvpladder` ORDER BY CAST(`points` AS SIGNED) DESC;

Spoiler


/*
create table pvpladder (
char_id int(11) primary key,
name varchar(23),
#points int(11),
#points varchar(30),
points int(11) unsigned,
key (points)
) engine = innodb;
*/

prontera,155,185,5	script	kjsdhfj	1_F_MARIA,{
	mes "Rankings :->";
	.@query$  = "SELECT `name`, IF(@d=t.`points`, @r, @r:=@i), @d:=t.`points`, @i:=@i+1 ";
	.@query$ += "FROM `pvpladder` t, (SELECT @d:=0, @r:=0, @i:=1)q ";
	.@query$ += "ORDER BY `points` DESC LIMIT 10";
	.@nb = query_sql(.@query$, .@name$, .@rank, .@points, .@dummy);
	if ( !.@nb ) {
		mes "  No entry.";
		close;
	}
	for ( .@i = 0; .@i < .@nb; ++.@i )
		mes .@rank[.@i] +". "+ .@name$[.@i] +" -> "+ .@points[.@i] +" points";
	next;
	if ( !query_sql( "SELECT `points`, 1+(SELECT COUNT(1) FROM `pvpladder` t1 WHERE t1.`points` > t2.`points`) FROM `pvpladder` t2 WHERE `char_id` = "+ getcharid(0), .@points, .@rank ) ) {
		mes "You haven't kill anyonw";
		close;
	}
	mes "You have "+ .@points +" Points";
	mes "Your current rank is No."+ .@rank;
	close;
OnPCKillEvent:
	if ( killedrid == getcharid(3) ) end;
	.@killpoints = rand(10,20);
	.@deadpoints = rand(10,20);
	query_sql "insert into `pvpladder` value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) +"', "+ .@killpoints +" ) on duplicate key update `points` = `points` + "+ .@killpoints;
	query_sql "select points from pvpladder where char_id = "+ getcharid(0), .@points;
	dispbottom "You have earned "+ .@killpoints +" Points. Total "+ .@points +".";
	attachrid killedrid;
	query_sql "insert into `pvpladder` value ( "+ getcharid(0) +", '"+ escape_sql( strcharinfo(0) ) +"', 0 ) on duplicate key update `points` =  IF( points - "+ .@deadpoints +" < 0, 0, points - "+ .@deadpoints  +")";
	query_sql "select points from pvpladder where char_id = "+ getcharid(0), .@points;
	dispbottom "You have lose "+ .@deadpoints +" Points. Total "+ .@points +".";
	end;
}

Sign In

PvP Ranking & SQL

Question

Link to comment

Share on other sites

10 answers to this question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members