• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

AAC [SQL/Gesior/TFS 1.5] Display Players Frag Count

Extrodus

|| Blazera.net ||
Premium User
Joined
Dec 22, 2008
Messages
2,724
Solutions
7
Reaction score
534
Location
Canada
Hey there guys trying to convert this query from 0.4 to 1.5; looked around and couldnt find the solution.
Trying to get the players frag count to display on characters.php for gesior.

Code:
$frags = $SQL->query("SELECT COUNT(`p`.`name`) AS count FROM `killers` k LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id` LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id` WHERE `p`.`id` = '".$player->getId()."';")->fetch();

This is an example of what the sql query looks like to pull the players death count.
Code:
  $deaths = $SQL->query("SELECT COUNT(`pd`.`player_id`) AS count FROM `player_deaths` pd WHERE `pd`.`player_id` = '".$player->getId()."';")->fetch();

There is a column "is_player" and also "unjustified" in the player_deaths table so I've tried a few variations of that but to no success.

Any help is appreciated.
 
Last edited:
Solution
Code:
$frags = $SQL->query("SELECT COUNT(pd.killed_by) AS count FROM player_deaths pd WHERE pd.killed_by = ".$SQL->quote($player->getName())." AND is_player = 1 AND unjustified = 1;")->fetch();

You may remove part with " AND unjustified = 1" if you wish to display all frags.
Code:
$frags = $SQL->query("SELECT COUNT(pd.killed_by) AS count FROM player_deaths pd WHERE pd.killed_by = ".$SQL->quote($player->getName())." AND is_player = 1 AND unjustified = 1;")->fetch();

You may remove part with " AND unjustified = 1" if you wish to display all frags.
 
Last edited:
Solution
Code:
$frags = $SQL->query("SELECT COUNT(pd.killed_by) AS count FROM player_deaths pd WHERE pd.killed_by = '".$SQL->quote($player->getName())." AND is_player = 1 AND unjustified = 1';")->fetch();

You may remove part with " AND unjustified = 1" if you wish to display all frags.
Thank you so much for the response! When testing the code, this is the following error.
Code:
Query:    SELECT COUNT(pd.killed_by) AS count FROM player_deaths pd WHERE pd.killed_by = ''Mister Tester' AND is_player = 1 AND unjustified = 1';
SQLSTATE:    42000
Driver code:    1064
Error message:    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Mister Tester' AND is_player = 1 AND unjustified = 1'' at line 1

Tried with removing AND is_player = 1 AND unjustified = 1 and error continues.
 
Thank you so much for the response! When testing the code, this is the following error.
Code:
Query:    SELECT COUNT(pd.killed_by) AS count FROM player_deaths pd WHERE pd.killed_by = ''Mister Tester' AND is_player = 1 AND unjustified = 1';
SQLSTATE:    42000
Driver code:    1064
Error message:    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Mister Tester' AND is_player = 1 AND unjustified = 1'' at line 1

Tried with removing AND is_player = 1 AND unjustified = 1 and error continues.
I edited the query, try now.
 
I edited the query, try now.
Looks like its not grabbing correctly from player_deaths?

Code:
Query:    SELECT `id`, `name`, `password`, `premium_ends_at`, `email`, `key`, `create_ip`, `creation`, `premium_points`, `page_access`, `location`, `rlname`, `email_new`, `email_new_time`, `email_code`, `next_email`, `last_post`, `flag` FROM `accounts` WHERE `name` = '1'
SQLSTATE:    00000
Driver code:    
Error message:    
Query:    SELECT `id`, `name`, `group_id`, `account_id`, `level`, `vocation`, `health`, `healthmax`, `experience`, `lookbody`, `lookfeet`, `lookhead`, `looklegs`, `looktype`, `lookaddons`, `maglevel`, `mana`, `manamax`, `manaspent`, `soul`, `town_id`, `posx`, `posy`, `posz`, `conditions`, `cap`, `sex`, `lastlogin`, `lastip`, `save`, `skull`, `skulltime`, `lastlogout`, `blessings`, `balance`, `stamina`, `skill_fist`, `skill_fist_tries`, `skill_club`, `skill_club_tries`, `skill_sword`, `skill_sword_tries`, `skill_axe`, `skill_axe_tries`, `skill_dist`, `skill_dist_tries`, `skill_shielding`, `skill_shielding_tries`, `skill_fishing`, `skill_fishing_tries`, `deleted`, `create_ip`, `create_date`, `comment`, `hide_char`, `questpoints`, `exphist_lastexp`, `exphist1`, `exphist2`, `exphist3`, `exphist4`, `exphist5`, `exphist6`, `exphist7` FROM `players` WHERE `name` = 'Dream'
SQLSTATE:    00000
Driver code:    
Error message:    
Query:    SELECT COUNT(pd.killed_by) AS count FROM player_deaths pd WHERE pd.killed_by = 'Dream' AND is_player = 1 AND unjustified = 1';
SQLSTATE:    42000
Driver code:    1064
Error message:    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
 
Back
Top