SQL query help? too complicated for me...

[kerriganm]

Background:
I'm working on an insert page where the user can enter a movie title with corresponding info, including director name, writer name, and actors' names. Most movie info is in a movie table. There is a person table that includes an sequence PK, first name, last name and bio for all people (directors, writers, actors) and a role table (director=1, writer=2, actor=3). Finally there is a person_role_movie table ('Person: Ed Wood has the role: director for movie: Plan 9').

Problem:
I've got the movie title, release date, etc. input going into the movie table. But for the person info- I need a query to say:
If the combination of input: director_firstname and director_lastname does not match a current combination of firstname + lastname matching a person_id in the person table, make a new record and enter it. Whether it does or not, locate the person_id for the person and link it to the current movie_id and the director job_role_id.

Can anyone help me? I'm not very good at this stuff, as you might have guessed.

 

[Harold III]

it seems like you are trying to link three tables (person, role, movie) with one table (person_role_movie), we need to figure out what relationship each table has with another one. Say a person can be both a writer and a director; or a movie can be directed/written/acted by multiple people; there are many directors/writers/actors; a person might has directed/written/acted multiple movies. So the relationship here is, person (M=M) movie; person (M=M) role. So my suggestion is to delete that person_role_movie table and add two link tables for each many-to-many relationship. for example,

CREATE TABLE person_movie (
person_id int(5) DEFAULT '0' NOT NULL,
movie_id int(5) DEFAULT '0' NOT NULL,
PRIMARY KEY (person_id, movie_id)
);

CREATE TABLE person_role (
person_id int(5) DEFAULT '0' NOT NULL,
role_id int(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (person_id, role_id)
);

then writing that query is much easier. first test the result of the user-input-query. if the person doesn't exist, create an entry to the person table, an entry indicating the role to the person_role table, such as
person_id-------role_id
---1------------------2----
---1------------------3----
which indicates that this guy is both an actor and a writer.
and same for the person_movie table.

Hope this helps.

 

[kerriganm]

Thanks, Harold III.

Well, I've been trying to look up how to test user input against a table, but since I don't know how to do it, I don't know how to look it up. That's my problem. A hint, anyone?

 

[Harold III]

$result=@mysql_query("select firstname, lastname from person where firstname='blabhblahblah' and lastname='blabhblahblah'", $connection);

if ($result=='') {

mysql_query("insert into person (NULL, blahblahblah, blahblahblah)", $connection);
$id=mysql_query("select last_insert_id()", $connection);
mysql_query("insert into person_movie ($id, 1)", $connection);
mysql_query("insert into person_movie ($id, 2)", $connection);

same for table person_role. I don't know which sql you were referring to so I used mysql with php as example.

 

[kerriganm]

wow, thanks! i'm not using mysql. i'm using cold fusion with sql, so what kind of sql would that be? is there a vanilla flavor?

 

[Harold III]

I have no idea then.