CODEWITHSUNDEEP
mysqlleft-join
dohdle
dohdle

Reputation: 91

Query SQL Limit

TABLES

PLAYERS

id | name    | rank id
1  | player1 | 3
2  | player2 | 0
3  | player3 | 0
4  | player4 | 1
5  | player5 | 0
6  | player6 | 0

GUILDS

id | name
1  | test1
2  | test2

GUILD RANKS

id | guild_id
1  |  1 <- which is test
2  |  2 <- which is test2

QUERY

$player = query -> name
$player_id = $SQL->query('SELECT rank_id from players where name = "'.htmlspecialchars($player['name']).'"')->fetch();
$player_id = $player_id['rank_id'];
$has_guild = $SQL->query('SELECT guild_id from guild_ranks where id = '. $player_id .'')->fetch();
$has_guild = $has_guild['guild_id'];
if($has_guild == 0){
    $guild_d_player = "No Guild";
}else{
    $guild_do_player = $SQL->query('SELECT name from guilds where id = '.$has_guild.' > 0')->fetch();
    $guild_do_player = $guild_do_player ['name'];
    $guild_d_player = "";
}

This is what happen with this:

This is about guilds.

Example:

Name    |   Guild Name   | This player truly is in a guild?

Player1 |      Test      | Yes

Player2 |  TestNo Guild  | <- Here is the problem, this player doesn't have guild but seems like if he was on one.

Player3 |  TestNo Guild  | No

Player4 |      Test2     | Yes

Player5 |  Test2No Guild | No

Player6 |  Test2No Guild | No

Etc...

It repeat the guild name until the query get a new guild name which truly exist.

Full query: http://pastebin.com/03TYv9V9

Upvotes: 0

Views: 72

Answers (1)

Shadow
Shadow

Reputation: 34232

You should use a single query with left joins joining the 3 tables:

SELECT g.name from players p
left join guild_ranks gr on p.rank_id=gr.id
left join guilds g on g.id=gr.guild_id
where name=...

If the returned value is empty (null), then the player has no guild.

Further notes:

  1. You should not query this information by player. You should combine this query with your main one on te top of your code in pastebin. Just change the comma syntax used there to explicit join.

  2. The sample data you provided is at odds with the expected results. Player1 has 3 as rank_id, but there is no record in guild_rank table with id=3, yet according to the ecoected results, the player is in test1 guild.

  3. I do not understand the use of guild_rank table and why you link your players to that table instead of storing guild_id in players table.

Upvotes: 2

Related Questions