trouble with querying two tables

MySQL / PHP issue with tables with 1-many relationship

[kernunrex]

I've got a MySQL database for my movie collection. I'm trying to build a webpage so that when folks come over, they can use it sort of like the IMDb to search for things to watch. There's a couple of query-related problems I'm having trouble getting my head around, though.

Simplifing my db a bit, say I have two tables: one stores the movies I own, another stores the genres for each of those movies. Example:

---------------- 
¦ Movie .......¦ 
---------------- 
¦ Total Recall ¦ 
---------------- 
¦ Commando ....¦ 
---------------- 

--------------------------- 
¦ Movie .......¦ Genre ...¦ 
--------------------------- 
¦ Total Recall ¦ Action ..¦ 
--------------------------- 
¦ Total Recall ¦ Sci-Fi ..¦ 
--------------------------- 
¦ Commando ....¦ Action ..¦ 
--------------------------- 
¦ Commando ....¦ Military ¦ 
--------------------------- 

One thing I would like to do is to display a movie with all of its genres without duplicating any movie entires, like this:

-------------------------------------- 
¦ Movie .......¦ Genre ...¦ Genre ...¦ 
-------------------------------------- 
¦ Total Recall ¦ Action ..¦ Sci-Fi ..¦ 
-------------------------------------- 
¦ Commando ....¦ Action ..¦ Military ¦ 
-------------------------------------- 

I'm familiar with basic SELECT statements and JOINS, etc and I'm familiar with basic PHP... I can't seem to figure out how to do something like this without the duplicate entries.

Another thing I'd like to do is to be able to exclude a movie based on one of these genres, even if it's also in a genre I'm looking for. For example, if I wanted a list of all "Action" movies that aren't also "Sci-Fi" movies, it should only list "Commando" from above.

Are these things that are possble to do with just a SELECT statement, or will it require a combo of SQL and PHP? Any advice on how to go about these two things?

[directrix]

Your first task cannot be done in SQL alone. Since a movie may belong to an arbitrary number of genres, you would need an arbitrary number of columns, but a select statement must specify a fixed number of columns.

A combination of SQL and PHP should do the trick.

[dmmh]

you could ofcourse simply make a TEXT type column to store the genres for the movie and query the field, explode the result into an array and use in_array to see if a certain genre is associated with a movie

it makes things a bit difficult, but it can be overcome :)

you could even keep it in the MOVIES table if you do this

[kernunrex]

Got my first task working A-OK with some simple PHP. Guess I was hoping there was some trick I wasn't aware of in SQL to take care of it...

dmmh - not a bad idea for the second thing was trying to do. Still playing around with it...