Populating a link table

Databases
1

Hello,

I’m wondering if a link table can be populated automatically ? Here’s the example

Table1 is a list of movies (movie_id, name)
Table2 is a list of producers (producer_id, name)
Table3 is the link table (movie_id, producer_id)

When I insert into both the movies and the producers table, is there a way that the link table can be inserted with both ids automatically ? or do I have to do a select on both tables, then insert into Table3 ?

I assume once the database is populated, I will have to do selects on the producer table to see if he/she already exists, but for the time being, I’d like to know based on inserting into both tables.

Thanks!
(mysql5, php5)

2

true, that was 75% pseudo code anyway. The tables themselves will have a composite unique key to avoid exact duplicates.

I just read that stored procedures (which I’m trying to get my head around) have CASE ability, which might be another way to go for anyone referencing this thread.

3

Whaaa ? Whatchu Talkin Bout Rudy ?

–googles-- Ahhh crap… so it does.

Web monkey of all trades… but master of none :frowning:

Thanks Rudy, thinks I will be having some really funky queries in the near future.

4

that’ll work just splendidly as long as no two movies or producers have the same name

otherwise you’re sunk :slight_smile:

5

I actually managed to create an insert with a select subquery from the two tables I needed… so I guess 1 insert is better than 2.

Posted for posterity…


insert into product_producers_movies (producer_id, movie_id)
(select p.id, m.id
FROM product_producers_list as p, product_movies as m WHERE p.name = '$producername' AND m.name = '$moviename')

Hopefully it helps others, eh. :slight_smile:

6

so do SELECT queries :slight_smile:

7

no, unfortunately you will have to do it with an additional INSERT

insert the movie, and if necessary, obtain the id

insert the producer, and if necessary, obtain the id

then just insert the link row, using the id values from the previous two steps

p.s. congrats on having only two columns in the link table :slight_smile: