Page 1 of 1

php favourites list

Posted: Mon Jun 15, 2009 2:13 pm
by tomsace
Hi,
What do you all think would be the best way about letting my (logged in) users save there favourite games in a list?
Each game on my games website has a unique id, would it be best to create a new table in my mysql database and link it to the users table? Or is there a way to save a list of the unique game ids in the users table in just one field?
I need to link each result back to a different page so I need to be able to parse the results if possible?
My tables are set out something like this:

Games Table
ID--Game
1---Pacman
2---Mario
3---Space Invaders
etc...

Users Table
Username--Password--fav_games??
user123-----#####
user321-----######
etc...

Favourite Games Table??
Username--Game1--Game2--Game3--Game4--Game5
User123----3---------2---------1

Can anyone tell me the best way to let users save favourite games?

Re: php favourites list

Posted: Mon Jun 15, 2009 2:23 pm
by Jonah Bron
If you're just looking for a concept, what you showed is a good direction. Just make the Users Table like this:

Users Table
Username--Password--fav_games
user123----*******--1,4,5,9,13
user321----*******--2,5,8,10,58,103
etc...

The fav_games column contains the IDs of the games in the Games Table. Just use a "," as a delimiter, and separate the string into an array with explode(',', fave_games)

Re: php favourites list

Posted: Tue Jun 16, 2009 7:02 am
by tomsace
thanks alot I will start working on it this way,
How would I go about letting a user remove one of the id's from the list?

For example, user123 has 4,6,16,2,90 if they wanted to remove 16 how would I go about doing this?

Re: php favourites list

Posted: Tue Jun 16, 2009 7:06 am
by mattpointblank
No, don't do it that way - it completely defeats the object of a relational database.

You had it right in your first post:

Games Table
ID--Game
1---Pacman
2---Mario
3---Space Invaders
etc...

Users Table
ID Username
456 user123
789 user321
etc...

New table: User Favourite Games

FavGameID | UserID | GameID
1 456 3
2 456 1
3 456 2
etc...

When you need to display a list of the user's favourite games, you can just select all rows from the new table where the UserID = 456 or whatever. Doing it this way allows you to do much more interesting things: display the most popular game for all users, for example. Plus, as you identify, much easier to edit and work with.