Jump to content
  • 0

Add death count on pvp ranking

Switcho

Asked by Switcho,

 Share

Question

Switcho Poring

Switcho

Posted
Posted

Hi ,

I use this pvp ranking : 

//===== rAthena Script =======================================
//= PvP log
//===== By: ================================================== 
//= Vietlubu
//===== Description: =========================================
//= Simple SQL PvP log
//= Notes:
//= - Create table `pvplog` when start script.
//= - Add new record when has OnPCKillEvent
//= - Remove NCP PvP Ranking if you don't want to use.
//===== Compatible With: ===================================== 
//= rAthena Project
//===== Changelogs: ==========================================
//= 1.0 First version.
//============================================================

prontera,155,181,5	script	PvP Ranking	430,{
	.@limitRanking = 3;
	.@limitEnemies = 3;

	mes "[PvP Ranking]";
	//menu "Ranking of month", L_RankMonth, "Ranking of week", L_RankWeek, "Ranking of day", L_RankDay;

	select "Ranking of month", "Ranking of week", "Ranking of day", "My enemies";
	if (@menu < 4) {
		// Ranking
		switch(@menu) {
			case 1:
				.@ranking$ = "MONTH";
				.@message$ = "Ranking of month";
				break;
			case 2:
				.@ranking$ = "WEEK";
				.@message$ = "Ranking of week";
				break;
			case 3:
				.@ranking$ = "DAY";
				.@message$ = "Ranking of day";
				break;
		}

		query_sql("SELECT `char`.`name`, COUNT(`char`.`name`) AS count_kill FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE " + .@ranking$ + "(`pvplog`.`pvp_date`) = " + .@ranking$ + "(CURRENT_DATE()) GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitRanking, .@name$, .@count_kill);
		
		mes .@message$;
		for( @i = 0; @i < getarraysize(.@name$); @i++ ) {
			mes "Top " + (@i+1) + ": ^2ecc71" + .@name$[@i] + "^000000 kills times: ^e74c3c" + .@count_kill[@i] + "^000000";
		}

	} else if (@menu == 4) {
		query_sql("SELECT `char`.`name`, COUNT(`char`.`name`) AS count_kill FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE `pvplog`.`victim_id` = " + getcharid(0) + " GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitEnemies, .@name$, .@count_kill);
		
		mes "Top your enemies:";
		for( @i = 0; @i < getarraysize(.@name$); @i++ ) {
			mes "Top 1: ^e74c3c" + .@name$[0] + "^000000 kill you ^2ecc71" + .@count_kill[0] + "^000000 times" + "^000000";
		}
	}
	close;

	L_RankMonth:
		mes "Ranking of month";
		close;
	
	L_RankWeek:
		mes "Ranking of week";
		close;

	L_RankDay:
		mes "Ranking of day";
		close;

	end;
}

-	script	pvplog	-1,{
	OnInit:
		query_sql("CREATE TABLE IF NOT EXISTS `pvplog` (`pvp_id` mediumint(9) unsigned NOT NULL auto_increment, `pvp_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `killer_id` int(11) NOT NULL default '0', `victim_id` int(11) NOT NULL default '0', `map` varchar(11) NOT NULL default '', PRIMARY KEY  (`pvp_id`)) ENGINE=MyISAM AUTO_INCREMENT=1;");
		end;

	OnPCKillEvent:
		query_sql("INSERT INTO `pvplog` (`killer_id`, `victim_id`, `map`) VALUES ('" + getcharid(0) + "', (SELECT `char_id` FROM `char` WHERE `name` = '" + rid2name(killedrid) + "'), '" + strcharinfo(3) + "');");
		end;
}

The script works perfectly , but he show only the kill. :

kill.jpg.c346ffd09dfedada50378e99ecbc5892.jpg

I want to add the number of death like this :

Top 1 : Test1 Nombre de kills/Deaths : 19/14

Top 2 : Test2 Nombres de kills/Deaths :14/19

 

but I do not know how to do it

Thx

3 answers to this question

Recommended Posts

  • 0
n0tttt Poring

n0tttt

Posted
Posted

Don't know if works. 

//===== rAthena Script =======================================
//= PvP log
//===== By: ================================================== 
//= Vietlubu
//===== Description: =========================================
//= Simple SQL PvP log
//= Notes:
//= - Create table `pvplog` when start script.
//= - Add new record when has OnPCKillEvent
//= - Remove NCP PvP Ranking if you don't want to use.
//===== Compatible With: ===================================== 
//= rAthena Project
//===== Changelogs: ==========================================
//= 1.0 First version.
//============================================================

prontera,155,181,5	script	PvP Ranking	430,{
	.@limitRanking = 3;
	.@limitEnemies = 3;

	mes "[PvP Ranking]";
	//menu "Ranking of month", L_RankMonth, "Ranking of week", L_RankWeek, "Ranking of day", L_RankDay;

	select "Ranking of month", "Ranking of week", "Ranking of day", "My enemies";
	if (@menu < 4) {
		// Ranking
		switch(@menu) {
			case 1:
				.@ranking$ = "MONTH";
				.@message$ = "Ranking of month";
				break;
			case 2:
				.@ranking$ = "WEEK";
				.@message$ = "Ranking of week";
				break;
			case 3:
				.@ranking$ = "DAY";
				.@message$ = "Ranking of day";
				break;
		}

		query_sql("SELECT `char`.`id``char`.`name`, COUNT(`char`.`name`) AS count_kill FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE " + .@ranking$ + "(`pvplog`.`pvp_date`) = " + .@ranking$ + "(CURRENT_DATE()) GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitRanking, .@id, .@name$, .@count_kill);
		
		mes .@message$;
		for( .@size = getarraysize(.@name$); .@i < .@size; .@i++ ) {
			query_sql("SELECT COUNT(`pvplog`.`victim_id`) FROM `pvplog` WHERE `pvplog`.`victim_id` = '"+.@id[.@i]+" AND " + .@ranking$ + "(`pvplog`.`pvp_date`) = " + .@ranking$ + "(CURRENT_DATE())", .@count_death);
			mes "Top " + (.@i+1) + ": ^2ecc71" + .@name$[.@i] + "^000000 kills times: ^e74c3c" + .@count_kill[.@i]/"+.@count_death+" + "^000000";	
		}

	} else if (@menu == 4) {
		query_sql("SELECT `char`.`name`, COUNT(`char`.`name`) AS count_kill FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE `pvplog`.`victim_id` = " + getcharid(0) + " GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitEnemies, .@name$, .@count_kill);
		
		mes "Top your enemies:";
		for( .@size = getarraysize(.@name$); .@i < .@size; .@i++ ) {
			mes "Top "+(.@i+1)+": ^e74c3c" + .@name$[.@i] + "^000000 kill you ^2ecc71" + .@count_kill[.@i] + "^000000 times" + "^000000";
		}
	}
	close;

	L_RankMonth:
		mes "Ranking of month";
		close;
	
	L_RankWeek:
		mes "Ranking of week";
		close;

	L_RankDay:
		mes "Ranking of day";
		close;

	end;
}

-	script	pvplog	-1,{
	OnInit:
		query_sql("CREATE TABLE IF NOT EXISTS `pvplog` (`pvp_id` mediumint(9) unsigned NOT NULL auto_increment, `pvp_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `killer_id` int(11) NOT NULL default '0', `victim_id` int(11) NOT NULL default '0', `map` varchar(11) NOT NULL default '', PRIMARY KEY  (`pvp_id`)) ENGINE=MyISAM AUTO_INCREMENT=1;");
		end;

	OnPCKillEvent:
		query_sql("INSERT INTO `pvplog` (`killer_id`, `victim_id`, `map`) VALUES ('" + getcharid(0) + "', (SELECT `char_id` FROM `char` WHERE `name` = '" + rid2name(killedrid) + "'), '" + strcharinfo(3) + "');");
		end;
}

 

  • 0
AnnieRuru Drops

AnnieRuru

Posted
Posted (edited)

wow ... this kind of table logs every single kills
I wonder will this kind of table can cause lag to the server ? because `the killer_id` field is not indexed"
perhaps index that field just to be safe 

explain pvplog;
alter table pvplog add key killer_id (killer_id);

 

and here's the script for it, @Switcho 

//===== rAthena Script =======================================
//= PvP log
//===== By: ================================================== 
//= Vietlubu
//===== Description: =========================================
//= Simple SQL PvP log
//= Notes:
//= - Create table `pvplog` when start script.
//= - Add new record when has OnPCKillEvent
//= - Remove NCP PvP Ranking if you don't want to use.
//===== Compatible With: ===================================== 
//= rAthena Project
//===== Changelogs: ==========================================
//= 1.0 First version.
//============================================================

prontera,155,181,5	script	PvP Ranking	430,{
	.@limitRanking = 3;
	.@limitEnemies = 3;

	mes "[PvP Ranking]";
	//menu "Ranking of month", L_RankMonth, "Ranking of week", L_RankWeek, "Ranking of day", L_RankDay;

	select "Ranking of month", "Ranking of week", "Ranking of day", "My enemies";
	if (@menu < 4) {
		// Ranking
		switch(@menu) {
			case 1:
				.@ranking$ = "MONTH";
				.@message$ = "Ranking of month";
				break;
			case 2:
				.@ranking$ = "WEEK";
				.@message$ = "Ranking of week";
				break;
			case 3:
				.@ranking$ = "DAY";
				.@message$ = "Ranking of day";
				break;
		}

		query_sql("SELECT `pvplog`.`killer_id` AS CID, `char`.`name`, COUNT(`pvplog`.`killer_id`) AS count_kill, (SELECT COUNT(`pvplog`.`victim_id`) from pvplog where victim_id = CID) FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE " + .@ranking$ + "(`pvplog`.`pvp_date`) = " + .@ranking$ + "(CURRENT_DATE()) GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitRanking, .@cid, .@name$, .@count_kill, .@count_death);
		
		mes .@message$;
		for( @i = 0; @i < getarraysize(.@name$); @i++ ) {
			mes "Top " + (@i+1) + ": ^2ecc71" + .@name$[@i] + "^000000 kill/death: ^e74c3c" + .@count_kill[@i] + "/"+ .@count_death[@i] + "^000000";
		}

	} else if (@menu == 4) {
		query_sql("SELECT `char`.`name`, COUNT(`char`.`name`) AS count_kill FROM `pvplog` INNER JOIN `char` ON `char`.`char_id` = `pvplog`.`killer_id` WHERE `pvplog`.`victim_id` = " + getcharid(0) + " GROUP BY `char`.`name` ORDER BY count_kill DESC LIMIT " + .@limitEnemies, .@name$, .@count_kill);
		
		mes "Top your enemies:";
		for( @i = 0; @i < getarraysize(.@name$); @i++ ) {
			mes "Top 1: ^e74c3c" + .@name$[0] + "^000000 kill you ^2ecc71" + .@count_kill[0] + "^000000 times" + "^000000";
		}
	}
	close;

	L_RankMonth:
		mes "Ranking of month";
		close;
	
	L_RankWeek:
		mes "Ranking of week";
		close;

	L_RankDay:
		mes "Ranking of day";
		close;

	end;
}

-	script	pvplog	-1,{
	OnInit:
		query_sql("CREATE TABLE IF NOT EXISTS `pvplog` (`pvp_id` mediumint(9) unsigned NOT NULL auto_increment, `pvp_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `killer_id` int(11) NOT NULL default '0', `victim_id` int(11) NOT NULL default '0', `map` varchar(11) NOT NULL default '', PRIMARY KEY  (`pvp_id`)) ENGINE=MyISAM AUTO_INCREMENT=1;");
		end;

	OnPCKillEvent:
		query_sql("INSERT INTO `pvplog` (`killer_id`, `victim_id`, `map`) VALUES ('" + getcharid(0) + "', (SELECT `char_id` FROM `char` WHERE `name` = '" + rid2name(killedrid) + "'), '" + strcharinfo(3) + "');");
		end;
}

 

Edited by AnnieRuru

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to question listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...