KaiZ KaiZ - 5 months ago 13
SQL Question

How do I properly insert data into both tables, with one of them having a foreign key to the other table?

Currently, I've got these 2 tables in my database:

Users

id - Primary Key
fname
lname
email
password
level


Authorizations

id_network
id_user - FK to id on table Users
network


The PHP code that I was thinking of doing was something like this. And I am aware that this code is vulnerable to SQL injections, I will fix it, but I haven't done so yet.

$query = "INSERT INTO users (id, pnome, unome, email, pass, level) VALUES (DEFAULT, '$pnome', '$unome', '$email', DEFAULT, DEFAULT)";
$result = pg_query($dbconn, $query);

$query = "INSERT INTO authorizations (id_network, id_user, network) VALUES ('$id', ?????, 'Facebook')";
$result = pg_query($dbconn, $query);


My question is, what do I put in place of the question marks of the Authorization table insertion?
Since there is the
id_user
foreign key to the Users table, and I wish to use the
id
from the entry that I just inserted into Users, how do I make sure that I am using that specific entry?

Answer

In postgreSQL, there's no concept of last insert ID. You can use either curval() or lastval(). You can also use a query that returns the id:

INSERT INTO `users` (
    `fname`, `lname`, `email` -- ...
) VALUES (
    'Smith', 'John', -- ...
) RETURNING `id`;

More info: postgreSQL function for last inserted ID

Comments