Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- $username = $_POST["username"];
- $password = $_POST["password"];
- $institution = $_POST["institution"];
- $conn = pg_connect("database connection information"); //in reality this has been filled
- $result = pg_query($conn, "INSERT INTO institutions (i_id, name) VALUES (null, '$institution') RETURNING i_id");
- $insert_row = pg_fetch_row($result);
- $insti_id = $insert_row[0];
- // INSTITUTION SAVED AND HAS ITS OWN ID BUT NO MEMBER OF STAFF ID
- $resultTwo = pg_query($conn, "INSERT INTO staff VALUES (NULL, '$username', '$password', '$insti_id'");
- $insert_rowTwo = pg_fetch_row($resultTwo);
- $user_id = $insert_rowTwo[0];
- // USER SAVED WITH OWN ID AND COMPANY ID
- // ASSIGN AN INSTITUTION TO A STAFF MEMBER IF THE STAFF'S $company_id MATCHES THAT OF THE
- // INSTITUION IN QUESTION
- $update = pg_query($conn, "UPDATE institutions SET u_id = '$user_id' WHERE i_id = '$insti_id'");
- pg_close($conn);
- ?>
- CREATE TABLE institutions(i_id serial, name text, u_id int);
- CREATE TABLE staff(user_id serial, username text, password text, i_id int);
- WITH x AS (
- INSERT INTO staff(username, password, i_id) -- provide column list
- VALUES ('$username', '$password', nextval('institutions_i_id_seq'))
- RETURNING user_id, i_id
- )
- INSERT INTO institutions (i_id, u_id, name)
- SELECT x.i_id, x.user_id, '$institution'
- FROM x
- RETURNING u_id, i_id; -- if you need the values back, else you are done
- staff (u_id serial PRIMARY KEY, ...)
- institution (i_id serial PRIMARY KEY, ...)
- institution_staff (i_id, u_id, ..., PRIMARY KEY(i_id, u_id)) -- implements n:m
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement