Mysql Database Relation Help

[TheMovieman]

Hey all,

 

For my site I want to build two databases, one for movies (which will contain info like release date, MPAA rating, Box Office Gross, etc) and the other for actors. Now, I have enough knowledge to create the DB and tables but I don't know how to relate them.

 

What I want to do is have a column in the movies db where it'll list the actors who were in that movie. The examples I've seen online dealing with a foreign key don't explain how to use more than one.

 

For instance, if I have Batman Begins, I want it to relate to the actor db of Christian Bale, Liam Neeson, Katie Holmes, Gary Oldman, Morgan Freeman, Michael Caine.

 

How do I do this? I've tried googling some tutorials but haven't come up with anything that has helped. Are there any sites you can suggest? This is probably not as hard as I'm making it out to be, but I want to be sure I'm getting it right before I get in too deep...

[surefire]

I'm surprised to say you didn't find any tutorials on the subject. The problem I had way back when I was learning was that there were many more tutorials than I had time to read, and not all of them were excellent.

 

I'm sure someone will give you a link to one or two, but I want to give you some other advice: dissect someone else's code. That's one of the best ways to learn. It doesn't replace a book or a tutorial, but will speed up your learning.

 

http://hotscripts.com/Detailed/30989.html

 

Looks right up your alley.

 

By the way, if you really want to learn php and database programming, you owe it to yourself to buy a book. The internet breeds this concept of something for nothing, but from my personal experience I have to say that learning the ropes on nothing but free tutorials is much more difficult.

 

Good luck.

[TweezerMan]

It looks to me what you'd need is an intermediate (third) table (I'll call it 'cast') that would actually store the relationship between the movie and actor tables - which actors were actually in what movies.

 

The cast table could have just three columns - its own ID column, and columns for movieID and actorID. A separate record is created in the cast table for each actor who was in a movie.

 

movie table:

>id  title
1   Batman Begins

actor table:

>id  name
1   Christian Bale
2   Liam Neeson
3   Katie Holmes
4   Gary Oldman
5   Morgan Freeman
6   Michael Caine

cast table:

>id  movieID  actorID
1   1        1
2   1        2
3   1        3
4   1        4
5   1        5
6   1        6

Here's an example of a MySQL query that returns the list of actors in a particular movie, starting with the movie table and going through the cast table to retrieve the appropriate records in the actor table:

>SELECT actor.name AS Actors FROM movie JOIN cast ON movie.id = cast.movieID JOIN actor ON cast.actorID = actor.id WHERE movie.title = 'Batman Returns'

The query yields this result:

>Actors
Christian Bale
Liam Neeson
Katie Holmes
Gary Oldman
Morgan Freeman
Michael Caine

Using this table setup, you can also run queries which return a list of movies that a particular actor was in.

 

Hope this helps...

[TheMovieman]

That does help

 

 

Thanks David!

 

 

 

Surefire - I did buy a book that covers the basics of MySQL which helped some but didn't broach the subject of a One-to-Many relationship well. I am planning on making a trip to the bookstore to find something that is more advanced...

 

Thanks for the link, I'm downloading it and will give it a try

Edited July 25, 2005 by TheMovieman