PHP/MySQL join issue

[waynew]

I have three tables:

• gamertags
  
• games
  
• game_type_preferences
  

When a new user signs up, his/her account info is inserted into the gamertags table. i.e. that is the main user table. On signup, a user can also check boxes on what games they play (games). They can also choose what multiplayer game modes they like to play (game_type_preferences). Obviously, because the user can choose more than one game and more than one game mode, I've had to setup two different tables. These two tables are linked to the gamertag table via a user_id.

 

 

gamertags

user_id

 

 

games

game_id

user_id

 

 

game_type_preferences

 

gametype_pref_id

user_id

 

 

As you can see, the gamertags table is the parent table and the other two tables are child tables. The two child tables are not related to one another (even though to some, it would make sense if they were). For example, when I setup an account, my info is stored in the gamertags table. The games I play are stored in the games table (can be one or more). The game modes I like to play are stored in the game_type_preferences table.

 

 

The problem I'm having is with the search utility. I want people to be able to search for users by games and game_type_preferences, however, I'm pretty sure that this is not going to work with the SQL I have, as info from the gamertags table will be repeated multiple times if the gamer in question has chosen more than one game / game mode.

 

 

Here's what I have so far, and it's without the basic search conditions.

 

 

 

SELECT gamertags.*,  games.*, game_type_preferences.*

FROM

gamertags

LEFT JOIN games ON gamertags.user_id = games.user_id

LEFT JOIN game_type_preferences ON gamertags.user_id = game_type_preferences.user_id

 

 

Problem is: This query above will give me the same user twice if he/she has chosen two games or two game types.

 

 

 

 

 

 

[cssfreakie]

I could be totally not understanding you, but as far as I can see the only thing that happens now is that you create a temporary table by joining them. If you were to narrow it down to something useful you need to narrow it down by for instance adding a where clause. That is what the search should  do in the end right?. You narrow it down by using either the game type or the game it self

 

SELECT gamertags.*,  games.*, game_type_preferences.*
FROM
gamertags
LEFT JOIN games ON gamertags.user_id = games.user_id
LEFT JOIN game_type_preferences ON gamertags.user_id = game_type_preferences.user_id
where game_type_preferences.gametype_pref_id = 2;

[waynew]

Hi. Thanks for the reply. Unfortunately, I'll still be in trouble, as a visitor may want to search for those who play multiple game modes. If a gamertag has more than one game mode attached to it, the same result will be repeated. For example:

 

SELECT 
                gamertags.*,  games.*, game_type_preferences.*
FROM
                gamertags
LEFT JOIN games ON 
                gamertags.user_id = games.user_id
LEFT JOIN game_type_preferences ON
                gamertags.user_id = game_type_preferences.user_id
WHERE game_type_preferences.gametype_id = 1 OR game_type_preferences.gametype_id = 2

 

returns my gamertag twice in the same resultset because I have both game modes attached to my gamertag.

[cssfreakie]

Ah if i am correct you require distinct for that. You might want to check this out: http://www.sqlcommands.net/sql+distinct/

[waynew]

Even if I add DISTINCT, it still repeats

[waynew]

Just solved it! I'm a bit tired at the moment, so it looked as if it wasn't working. Turns out I had used DISTINCT incorrectly.

[cssfreakie]

Just solved it! I'm a bit tired at the moment, so it looked as if it wasn't working. Turns out I had used DISTINCT incorrectly.

  I was already typing, send me an export file happy to test it because I was pretty sure it would work lol.

Good luck with the game