• 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!
  • If you're using Gesior 2012 or MyAAC, please review this thread for information about a serious security vulnerability and a fix.

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

Extrodus

|| Blazera.net ||
Premium User
Joined
Dec 22, 2008
Messages
2,648
Solutions
7
Reaction score
446
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.

slaw

MyAAC Creator
Premium User
Joined
Aug 27, 2007
Messages
3,476
Solutions
113
Reaction score
916
Location
Germany
GitHub
slawkens
Twitch
PL_Flamaster
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
OP
OP
Extrodus

Extrodus

|| Blazera.net ||
Premium User
Joined
Dec 22, 2008
Messages
2,648
Solutions
7
Reaction score
446
Location
Canada
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.
 

slaw

MyAAC Creator
Premium User
Joined
Aug 27, 2007
Messages
3,476
Solutions
113
Reaction score
916
Location
Germany
GitHub
slawkens
Twitch
PL_Flamaster
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.
 
OP
OP
Extrodus

Extrodus

|| Blazera.net ||
Premium User
Joined
Dec 22, 2008
Messages
2,648
Solutions
7
Reaction score
446
Location
Canada
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
 
Top