insert data into database

[johnhelen]

Hello

 

I need to insert records into a table. A primary key will be generated automatically as it is serial number (postgres database). Then I have to take this key to insert into another table (as they have a relationship).

 

For example, I have to insert a record into movie table - this table has a primary key that is automatically created.

 

INSERT INTO movie (title, summary ) VALUES ('the ghost', 'a true story about ghost, 12 minutes');

 

After inser, I need to have get the movie_id

 

SELECT currval('movie_id_seq');

 

Then insert this id into a location_movie table. I have only one location_id, that is 27.

 

INSERT INTO location (movie_id, location_id) values (13, 27);

 

 

The problem here is that I have about 200 records and I have a list of INSERT queries like this

 

INSERT INTO movie (title, summary ) VALUES ('the ghost', 'a true story about ghost, 122 minutes');

INSERT INTO movie (title, summary ) VALUES ('titanic' 'a true story about titanic, 180 minutes');

INSERT INTO movie (title, summary ) VALUES ('the friend', '112 minutes');

....

....

 

How I create a php script that read each "INSERT" query, get the movie_id and add into location_movie table automatically using php?

 

Many thanks

shuhu

[mmarif4u]

A few  solutions to your problem.

1:Add a date column to your 1st table and retrieve data by latest datetime.

2:Use max for movie_id to retrieve from 1st table.(Already inserted to that table will be the maximum id)

 

Hope this will help.