• 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 Error no Top Guilds Gesior website

padrin

New Member
Joined
May 27, 2021
Messages
10
Reaction score
0
GitHub
padrinho
Good morning people, everything fine?! (I use XAMPP V7.1.1 - Control panel 3.2.2) Older version because only it ran this site!

I have an error related to TOPS GUILDS,

the mistake:
Code:
Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\pages\latestnews.php on line 17

Linha 17:
Code:
   foreach($SQL->query('SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `players` p LEFT JOIN `player_deaths` pd ON `pd`.`killed_by` = `p`.`name` LEFT JOIN `guild_membership` gm ON `p`.`id` = `gm`.`player_id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = `g`.`id` WHERE `g`.`id` > 0 AND `pd`.`unjustified` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 4;') as $guild)


Enabling debug, for more details on the error:
Code:
SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `players` p LEFT JOIN `player_deaths` pd ON `pd`.`killed_by` = `p`.`name` LEFT JOIN `guild_membership` gm ON `p`.`id` = `gm`.`player_id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = `g`.`id` WHERE `g`.`id` > 0 AND `pd`.`unjustified` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 4;
SQLSTATE:    42S02
Driver code:    1146
Error message:    Table 'otx.guild_membership' doesn't exist

If you can help me, thank you!!
 
Last edited:
I'm gonna guess this is the only thing that doesn't work, and that the rest of the website is otherwise functional? Else you might just be using a website that is incompatible with your server, and there will be lots of errors to patch, and there will likely be another version or configuration you have to use.

Table 'otx.guild_membership' doesn't exist

Since your database name is otx, and you dont have the guild_membership table, I'm gonna guess your running otx version 2, which is based on an obsolete TFS version. Which doesn't have the table guild_membership.

Looking at the SQL query, its a bit hard to analyse:
SQL:
SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `players` p LEFT JOIN `player_deaths` pd ON `pd`.`killed_by` = `p`.`name` LEFT JOIN `guild_membership` gm ON `p`.`id` = `gm`.`player_id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = `g`.`id` WHERE `g`.`id` > 0 AND `pd`.`unjustified` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 4;

So lets beautify it to figure out how it uses guild_membership, we get this:
SQL:
SELECT 
    `g`.`id` AS `id`, 
    `g`.`name` AS `name`, 
    COUNT(`g`.`name`) as `frags` 

FROM `players` p 

LEFT JOIN `player_deaths` pd 
    ON `pd`.`killed_by` = `p`.`name` 

LEFT JOIN `guild_membership` gm -- from error message: this table doesn't exist
    ON `p`.`id` = `gm`.`player_id` 

LEFT JOIN `guilds` g -- ON connector here uses gm alias (guild_membership) to connect to the guild data
    ON `gm`.`guild_id` = `g`.`id` 

WHERE 
    `g`.`id` > 0 
    AND `pd`.`unjustified` = 1 

GROUP BY `name` 
ORDER BY 
    `frags` DESC, 
    `name` ASC 
LIMIT 4;

What we see here, is that it joins in guild_membership to create a player -> guild association, to determine which guild a player is a member of, if any.

So how did the old database schema figure out this relationship if it doesn't have guild_membership table?
Looking into the players table, we find rank_id and guildnick. Thus we can use guild_ranks to create this association. Looking at the guild_ranks table:
SQL:
`guild_ranks`  (
    `id` INT NOT NULL AUTO_INCREMENT,
    `guild_id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `level` INT NOT NULL COMMENT '1 - leader, 2 - vice leader, 3 - member',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

So, manipulating the original query, to join in guild_ranks instead of guild_membership to obtain guild_id, we get something like this:
Replace:
SQL:
LEFT JOIN `guild_membership` gm 
    ON `p`.`id` = `gm`.`player_id`

With:
SQL:
LEFT JOIN `guild_ranks` gm 
    ON `p`.`rank_id` = `gm`.`id`

Changed the table name, and changed the association connection from player_id to rank_id, since rank_id is a unique primary key that connects a player to a guild_id, which works as a direct replacement for the rest of the query. Keeping the gm alias so the below guild ON connection remains the same.

The resulting query:
SQL:
SELECT 
    `g`.`id` AS `id`, 
    `g`.`name` AS `name`, 
    COUNT(`g`.`name`) as `frags` 
FROM `players` p 
LEFT JOIN `player_deaths` pd 
    ON `pd`.`killed_by` = `p`.`name` 
LEFT JOIN `guild_ranks` gm 
    ON `p`.`rank_id` = `gm`.`id` 
LEFT JOIN `guilds` g 
    ON `gm`.`guild_id` = `g`.`id` 
WHERE 
    `g`.`id` > 0 
    AND `pd`.`unjustified` = 1 
GROUP BY `name` 
ORDER BY 
    `frags` DESC, 
    `name` ASC 
LIMIT 4;

Putting it back into latestnews.php line 17:
PHP:
foreach($SQL->query('SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `players` p LEFT JOIN `player_deaths` pd ON `pd`.`killed_by` = `p`.`name` LEFT JOIN `guild_ranks` gm ON `p`.`rank_id` = `gm`.`id` LEFT JOIN `guilds` g ON `gm`.`guild_id` = `g`.`id` WHERE `g`.`id` > 0 AND `pd`.`unjustified` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 4;') as $guild)
 
Hi Znote, thanks for the feedback!!

I managed to solve this problem this morning,
Now i have problems to show the images of the guilds, I've tried all the ways but the images don't work. I don't use LINUX.

The problem is solved in 2 steps \/
1º I created the guild_memberships table,
Lua:
CREATE TABLE IF NOT EXISTS `guild_membership` (
  `player_id` int(11) NOT NULL,
  `guild_id` int(11) NOT NULL,
  `rank_id` int(11) NOT NULL,
  `unjustified` INT NOT NULL DEFAULT 0;
  `nick` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`player_id`),
  FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;


Replaced the code with this:
Code:
    foreach($SQL->query('SELECT ' . $SQL->tableName('g') . '.' . $SQL->fieldName('id') . ' AS ' . $SQL->fieldName('id') . ', ' . $SQL->tableName('g') . '.' . $SQL->fieldName('name') . ' AS ' . $SQL->fieldName('name') . ', COUNT(' . $SQL->tableName('g') . '.' . $SQL->fieldName('name') . ') AS ' . $SQL->fieldName('frags') . ' FROM ' . $SQL->tableName('killers') . ' k LEFT JOIN ' . $SQL->tableName('player_killers') . ' pk ON ' . $SQL->tableName('k') . '.' . $SQL->fieldName('id') . ' = ' . $SQL->tableName('pk') . '.' . $SQL->fieldName('kill_id') . ' LEFT JOIN ' . $SQL->tableName('players') . ' p ON ' . $SQL->tableName('pk') . '.' . $SQL->fieldName('player_id') . ' = ' . $SQL->tableName('p') . '.' . $SQL->fieldName('id') . ' LEFT JOIN ' . $SQL->tableName('guild_ranks') . ' gr ON ' . $SQL->tableName('p') . '.' . $SQL->fieldName('rank_id') . ' = ' . $SQL->tableName('gr') . '.' . $SQL->fieldName('id') . ' LEFT JOIN ' . $SQL->tableName('guilds') . ' g ON ' . $SQL->tableName('gr') . '.' . $SQL->fieldName('guild_id') . ' = ' . $SQL->tableName('g') . '.' . $SQL->fieldName('id') . ' WHERE ' . $SQL->tableName('g') . '.' . $SQL->fieldName('id') . ' > 0 AND ' . $SQL->tableName('k') . '.' . $SQL->fieldName('unjustified') . ' = 1 AND ' . $SQL->tableName('k') . '.' . $SQL->fieldName('final_hit') . ' = 1 GROUP BY ' . $SQL->fieldName('name') . ' ORDER BY ' . $SQL->fieldName('frags') . ' DESC, ' . $SQL->fieldName('name') . ' ASC LIMIT 4;') as $guild)

, now is problem with show images 'logo' guild.
 
Back
Top