PHP/SQL Database design help

[madk]

Hello all,

I'm here today looking for a bit of advice. I currently run a site that already has a large database full of video game data. Each game is stored by a unique ID.

I am looking to begin a project that will allow our users to track their collections through the site. Since we already have a large games database we are obviously going to be using the unique game IDs to help.

I just don't know how I should store the users collection. I already have a method where I just store the collection data as just 1 long string with each id separated by a coma. ie. 123,43,56,87 and so on. I then explode the string into an array to use the data.

Are there any other solutions I should pursue before going forward with this one?

Thanks in advance for the guidance.
MDK

[WesleyC]

Since this is a many-to-many relationship (each user can have multiple games and each game can have multiple users) this table structure works well:

tbl_games:
id
...

tbl_users:
id
...

tbl_games2users:
game_id
user_id

Make sure there's a unique key on tbl_games2users for both columns to avoid duplicate records--and index might also speed up your queries.

Then, when you want to get data in PHP, you'd do something like

SELECT id, ...
FROM tbl_users
INNER JOIN tbl_games2users ON user_id = tbl_users.id
INNER JOIN tbl_games ON game_id = tbl_games.id
WHERE ...

To get all games for user 3, use a WHERE clause of tbl_users.id = 3
To get all users of game 256, use a WHERE clause of tbl_games.id = 256

Don't forget to change the column names as desired as well. :)

SQL is more efficient for this sort of thing than PHP would be, so it's preferable to keep the logic in SQL if at all possible.

Edit: Almost forgot. Each relationship in this system is inserted as a row into tbl_games2users. Thus, if user 1 has games 2 and 3 in his collection, you'd have 2 rows tbl_games2users with user_id = 1, with game_id = 2 and game_id = 3.

[edited by: WesleyC at 5:31 pm (utc) on Aug. 1, 2007]

[madk]

Thank you for your help so far. Your method is definitely more elegant than the solution I was working on.

Let me throw a curveball at you.

tbl_games contains id (int), name (char) and platform(int).

I am going to need to be able to sort the data by name and by platform.

[WesleyC]

tbl_games contains id (int), name (char) and platform(int).

In that case, you'd just add an ORDER BY clause to the end of your SQL, like you would with a simple SELECT statement without any joins.

SELECT tbl_games.name
FROM tbl_users
INNER JOIN tbl_games2users ON user_id = tbl_users.id
INNER JOIN tbl_games ON game_id = tbl_games.id
WHERE tbl_users.id = 3
ORDER BY tbl_games.name ASC

Would get all game names for user 3, sorted by the game name in ascending order.

Hope this helps! :)

[madk]

Right now I am getting "Column 'id' in on clause is ambiguous". I am having trouble wrapping my head around the join statements.

Here is my structure and query:

ds_gamestats
------------
id (int)
name (char)
platform (int)

ds_users
--------
user_id (int)

ds_collections
--------------
id (int)
game_id (int)

Query
-----
SELECT ds_gamestats.name FROM ds_users
INNER JOIN ds_collections ON id = ds_users.user_id
INNER JOIN ds_gamestats ON game_id = ds_gamestats.id
WHERE ds_users.user_id = 2 ORDER BY ds_gamestats.name ASC"

[Habtom]

This is due to the same field name "id" you have on the two tables in the query. Identifying the id fieldname could solve the problem. I have added ds_collections to your query.

SELECT ds_gamestats.name FROM ds_users
INNER JOIN ds_collections ON ds_collections.id = ds_users.user_id
INNER JOIN ds_gamestats ON game_id = ds_gamestats.id
WHERE ds_users.user_id = 2 ORDER BY ds_gamestats.name ASC"

Habtom

[edited by: Habtom at 5:44 am (utc) on Aug. 2, 2007]