quickest way to get data (mysql/php)

[Chronicle]

Hello
I am building a new personal site that will have a few game reviews and things on it. i have built the database structure (well, planned it...). it has four tables:
games
platforms
gamesplatforms
reviews

of course, games come in more than one platform, so that is what gamesplatforms is for.
i want to display a list of games, basically as:

title: games.releasedate
releasedate: games.releasedate
consoles: [list of all platforms]
and then a link (using games.id)

i have produced a ms paint picture showing the database layout. what is the best way to retrieve this data? LEFT JOIN etc? I always get confused by things like this. and also, the list of consoles will be more than one often.
See : http://img272.imageshack.us/img272/2893/dblayoutqr3.png

i hope ive made it as clear as i can. i know its not the clearest thing ever...

cheers!!

 

[EKsparky]

Well i think unless you are quite experienced in coding both php and mysql you are bitting of a bit more than you can chew may use google for tutorials as the answer would be quite a bit longer than a forum reply
maybe you should just try building html site using dreamweaver using the same sort of structure as your picture then you could slowly start adding things in to a database
Or even try cpgnuke or phpnuke to get the hang of php/mysql websites
hope this helped
ps my site link below is cpgnuke site

 

[paulsheff]

Just make sure you associate your foreign keys and primary keys.

If you've got your games.id I think you'd want something like

"SELECT platforms.name 
FROM platforms, gamesplatforms 
WHERE gamesplatforms.gameid = '$games_id'
AND gamesplatforms.platformid=platforms.id"

The only thing I would suggest is you've got a lot of id columns which won't help your queries, I personally would use games.game_id, platforms.platform_id, gamesplatforms.gp_id.

 

[JIMA]

One thing you might want to do is drop the "id" on the gamesplatforms table and have a composite key of gameid, platformid. So you could get all the platforms for a game or see if a game was on a particular platform. Makes things a bit simpler.

Another thing is that each game/platform combination could have a different review as some games are better on different platforms etc. To do this you could use a gameid, platform composite key on the reviews table.

Hope that helps.