how to query db when user has more than one table associated with the results i want?

I'm stumped on this one, or just to late at night to be thinking logically ;-0

I have a database for movies and each movie has a start and end date to them.

I have no problem querying the db to get the movie name and start, end dates..

my problem is that the movie can have more than one person associated with it, or the user can have more than one movie associated with it.

so for example:

Sue wants to make movie 'gore' and she sets the start date to 2010-11-09 and end date to 2011-11-10 this is 1year and 1 day.

But now Sue wants to make another movie 'fish' with different days.

My problem is here, how can i query this so that i can echo out the time diff between end and start dates for one movie at a time.

my end result i'm trying to do is have a little box that list all the movies she's making and then list the start, end dates for each of those movies.

I know I ca开发者_高级运维n do this in jQUery and somehow grab the current movie and do an ajax call to query something like

select movie_name, start, end, from movies where id = 'sue' and movie_name = $(.selector).val()

that's my idea, but i want a php version.

any ideas on how to do this?

i'm just boggled by the fact that a movie will eventually have like 50 people associated with it and those 50 people might have more than one movie associated with them.

I have a table called projects where it has those fields

You cannot do this with just one table. Since this is a many-to-many relationship between people and movies (each person can be involved in multiple movies, and each movie is related to multiple people), you need three tables. One table has people, one table has movies, and the third table holds the associations between the first two. It needs only two columns, the person's ID and the movie's ID.

If sue is involved in movies 1, 2 and 3, there will be 3 rows in the association table. You will join the three tables in your SELECT query to get the list of movies for a user, or the list of users for a movie.