PVP Ranking in Flux CP

[Rebel]

Hello. Im using the latest FluxCP here -> https://github.com/rathena/FluxCP/ 
and I tried to add this PVP Ranking here -> https://rathena.org/board/files/file/2380-free-flux-themes-design-no-3-v14/This is what i got:

 

module:

<?php
/**
 *
 * Pvp Ranking Module Section
 *
 * @package		GTheme
 * @author		John Gerome "Gerome" Baldonado
 * @copyright	Copyright (c) 2013, jiidesignstudio.com
 * 
 * Please do not redistribute my work without
 * permission and leave all credits in tact.
 */
 
if (!defined('FLUX_ROOT')) exit;

$title    = 'PvP Ranking';
$classes  = Flux::config('JobClasses')->toArray();
$jobClass = $params->get('jobclass');
$bind     = array();

if (trim($jobClass) === '') {
	$jobClass = null;
}

if (!is_null($jobClass) && !array_key_exists($jobClass, $classes)) {
	$this->deny();
}

$col  = "ch.char_id, ch.name AS char_name, ch.class AS char_class, ch.base_level, ch.base_exp, ch.job_level, ch.job_exp, ch.kills, ch.deaths, ch.streaks,";
$col .= "ch.guild_id, guild.name AS guild_name, guild.emblem_len AS guild_emblem_len";

$sql  = "SELECT $col FROM {$server->charMapDatabase}.`char` AS ch ";
$sql .= "LEFT JOIN {$server->charMapDatabase}.guild ON guild.guild_id = ch.guild_id ";
$sql .= "LEFT JOIN {$server->loginDatabase}.login ON login.account_id = ch.account_id ";
$sql .= "WHERE 1=1 ";

if (Flux::config('PvpHidePermBannedCharRank')) {
	$sql .= "AND login.state != 5 ";
}
if (Flux::config('PvpHideTempBannedCharRank')) {
	$sql .= "AND (login.unban_time IS NULL OR login.unban_time = 0) ";
}

$groups = AccountLevel::getGroupID((int)Flux::config('PvPRankingHideGroupLevel'), '<');
if(!empty($groups)) {
	$ids   = implode(', ', array_fill(0, count($groups), '?'));
	$sql  .= "AND login.group_id IN ($ids) ";
	$bind  = array_merge($bind, $groups);
}

if ($days=Flux::config('PvpCharRankingThreshold')) {
	$sql    .= 'AND TIMESTAMPDIFF(DAY, login.lastlogin, NOW()) <= ? ';
	$bind[]  = $days * 24 * 60 * 60;
}

if (!is_null($jobClass)) {
	$sql .= "AND ch.class = ? ";
	$bind[] = $jobClass;
}

$sql .= "ORDER BY ch.kills DESC, ch.streaks DESC, ch.base_level DESC, ch.char_id ASC ";
$sql .= "LIMIT ".(int)Flux::config('PvpRankingLimit');
$sth  = $server->connection->getStatement($sql);

$sth->execute($bind);

$chars = $sth->fetchAll();
?>

Can anyone explain to me why it has error? I dont understand it, i tried figuring how can i make it work. also tried Xantara's Flux CP, But no luck that's why I decide to ask here in rA forums instead. Thank you!

sql:

CREATE TABLE IF NOT EXISTS `pvpladder` (char_id int(11) not null default '0' primary key,name varchar(30) not null default '',streaks smallint(6) unsigned not null default '0',kills smallint(6) unsigned not null default '0',deaths smallint(6) unsigned not null default '0',streaktime datetime) engine = myisam;

CREATE TABLE IF NOT EXISTS `ownladder` (guild_id int(11) not null default '0' primary key,name varchar(24) not null default '',currentown smallint(6) unsigned not null default '0',highestown smallint(6) unsigned not null default '0',owntime datetime) engine = myisam; 

 

Edited December 24, 2017 by Rebel
added sql

[Rebel]

anyone care to help?

[Caith1991]

The error states that the column "ch.kills" doesnt exist. Regarding to your script the table-reference `ch` is defined by this line:

$sql  = "SELECT $col FROM {$server->charMapDatabase}.`char` AS ch ";

As there is no column called `kills` in the table `char` your script wont work. If you want this to access this script your table `pvpladder` you may change the mentioned line to:

$sql  = "SELECT $col FROM {$server->charMapDatabase}.`pvpladder` AS ch ";

Also which ranking script you are using to count the kills ingame? If you want you can add me to skype "ashtorias" or Discoard "Honey#9738"
 

(no warranty that this will work as I just backtraced your provided informations and official sql-structure of rathena)

Edited December 28, 2017 by Caith1991

[Rebel]

18 hours ago, Caith1991 said:

The error states that the column "ch.kills" doesnt exist. Regarding to your script the table-reference `ch` is defined by this line:

$sql  = "SELECT $col FROM {$server->charMapDatabase}.`char` AS ch ";

As there is no column called `kills` in the table `char` your script wont work. If you want this to access this script your table `pvpladder` you may change the mentioned line to:

$sql  = "SELECT $col FROM {$server->charMapDatabase}.`pvpladder` AS ch ";

Also which ranking script you are using to count the kills ingame? If you want you can add me to skype "ashtorias" or Discoard "Honey#9738"
 

(no warranty that this will work as I just backtraced your provided informations and official sql-structure of rathena)

Thank you for answering I just change it to this line:

"SELECT $col FROM {$server->charMapDatabase}.`char` AS ch INNER JOIN {$server->charMapDatabase}.`pvpladder` AS ph ";

 

[Rebel]

hi anyone can help me.. it only shows 1 result but when i try it on php sql it shows many results..

fluxcp result:

 

Schema:

<?php if (!defined('FLUX_ROOT')) exit;

$title    = 'PvP Ranking';
$classes  = Flux::config('JobClasses')->toArray();
$jobClass = $params->get('jobclass');
$bind     = array();

if (trim($jobClass) === '') {
	$jobClass = null;
}

if (!is_null($jobClass) && !array_key_exists($jobClass, $classes)) {
	$this->deny();
}

$col  = "ch.char_id, ch.name AS char_name, ch.class AS char_class, ch.base_level, ch.base_exp, ch.job_level, ch.job_exp, ";
$col .= "ch.guild_id, guild.name AS guild_name, guild.emblem_len AS guild_emblem_len, ";
$col .= "ph.char_id, ph.kills, ph.deaths, ph.streaks ";

$sql  = "SELECT $col FROM {$server->charMapDatabase}.char AS ch INNER JOIN {$server->charMapDatabase}.pvpladder AS ph ";
$sql .= "LEFT JOIN {$server->charMapDatabase}.pvpladder ON ph.char_id = ch.char_id ";
$sql .= "LEFT JOIN {$server->charMapDatabase}.guild ON guild.guild_id = ch.guild_id ";
$sql .= "LEFT JOIN {$server->loginDatabase}.login ON login.account_id = ch.account_id ";
$sql .= "WHERE 1=1 GROUP BY ch.char_id ";

if (Flux::config('PvpHidePermBannedCharRank')) {
	$sql .= "AND login.state != 5 ";
}
if (Flux::config('PvpHideTempBannedCharRank')) {
	$sql .= "AND (login.unban_time IS NULL OR login.unban_time = 0) ";
}

$groups = AccountLevel::getGroupID((int)Flux::config('PvPRankingHideGroupLevel'), '<');
if(!empty($groups)) {
	$ids   = implode(', ', array_fill(0, count($groups), '?'));
	$sql  .= "AND login.group_id IN ($ids) ";
	$bind  = array_merge($bind, $groups);
}

if ($days=Flux::config('PvpCharRankingThreshold')) {
	$sql    .= 'AND TIMESTAMPDIFF(DAY, login.lastlogin, NOW()) <= ? ';
	$bind[]  = $days * 24 * 60 * 60;
}

if (!is_null($jobClass)) {
	$sql .= "AND ch.class = ? ";
	$bind[] = $jobClass;
}

$sql .= "ORDER BY ph.kills DESC ";
$sql .= "LIMIT ".(int)Flux::config('PvpRankingLimit');
$sth  = $server->connection->getStatement($sql);

$sth->execute($bind);

$chars = $sth->fetchAll();
?>

Phpmyadmin Result: