CODEWITHSUNDEEP
phppostgresql
Lazarus Rising
Lazarus Rising

Reputation: 2685

insert default value of a table into another table

Simplified version:

I have a table named books (book_id, book_title) where book_id is the primary key and of type serial (so it is assigned automatically without me inserting it).

I also have another table named book_version (id, book_id, book_version) where book_id is foreign key to the first table.

With a form I take the book title and version. I insert the title into the first table and have to insert the generated book_id along with version into the second table.

How can I do that without having 3 separate queries (one insert for first table, one select to get it's id and one insert for the second table)?

The idea here is that the same book may have been published several times with slight changes, so i want to keep track of different versions.

Upvotes: 1

Views: 112

Answers (4)

Lazarus Rising
Lazarus Rising

Reputation: 2685

Although there were some pretty good ideas here (ie. triggers, stored procedures or getting last inserted row) , using the Returning functionality seemed like the best solution, at least in my case.

Upvotes: 1

Manoj Saini
Manoj Saini

Reputation: 339

 mysql_insert_id() is mysql function which gets last inserted id into database.

<?php
    $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
    if (!$link) {
        die('Could not connect: ' . mysql_error());
    }
mysql_select_db('mydb');

here book_id is autofill 

mysql_query("INSERT INTO books ('book_title') values ('kossu')");

$last_insert_id = mysql_insert_id();

Now for second table id is autofill

mysql_query("INSERT INTO book_version ('book_id', 'book_version') values ($last_insert_id,'6.48' )");

?>

Upvotes: 1

Richard St-Cyr
Richard St-Cyr

Reputation: 995

I would use a stored procedure and delegate the work to it. There will still be 3 queries, but those 3 queries will be executed in MySQL. Your PHP code would only see it as 1 query,

Upvotes: 1

mikeb
mikeb

Reputation: 11267

Use an after insert trigger on the first table:

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

An after insert trigger can reference the inserted row and then you only have to run 1 statement to insert the version.

You only have to insert the first row via PHP, and your trigger is installed in the database and does the work after that.

That is the best way to do it, IMO.

Upvotes: 1

Related Questions