PHP / MySQL INSERT功能逻辑错误

Question

I recently asked a question about writing to multiple tables: PHP/MySQL insert into multiple data tables on submit

I have now tried out this code and there are no errors produced in the actual code but the results I am getting are strange. When a user clicks register this 'insert.php' page is called and the code can be found below.

<?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);

?>

What the result of this is just the browser waiting for a server response but there it just constantly waits. Almost like an infinite loop I'm assuming. There are no current errors produced so I think it may be down to a logic error. Any ideas?

Answer 1

The errors:

• RETURNING clause is missing in the second INSERT statement.

• Provide an explicit list of columns for your second INSERT statement, too.

• Don't supply NULL in the INSERT statements if you want the column default (serial columns?) to kick in. Use the keyword DEFAULT or just don't mention the column at all.

The better solution:

Use data-moidifying CTE, available since PostgreSQL 9.1 to do it all in one statement and save a overhead and round trips to the server. (MySQL knows nothing of the sort, not even plain CTEs).

Also, skip the UPDATE by re-modelling the logic. Retrieve one id with nextval(), and make do with just two INSERT statements.

Assuming this data model (you should have supplied that in your question):

CREATE TABLE institutions(i_id serial, name text, u_id int);
CREATE TABLE staff(user_id serial, username text, password text, i_id int);

This one query does it all:

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

---

Data model

You might think about changing your data model to a classical n:m relationship. Would include these tables and primary keys:

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

You can always define institution_staff.i_id UNIQUE, if a user can only belong to one institution.