我最近问了一个关于写入多个表的问题:PHP/MMySQL在提交时插入多个数据表
我现在已经试用了这个代码,在实际的代码中没有产生错误,但我得到的结果很奇怪。当用户单击register时,会调用这个"insert.php"页面,代码可以在下面找到。
<?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);
?>
这样做的结果只是浏览器在等待服务器响应,但它只是不断地等待。我假设几乎就像一个无限循环。目前没有产生任何错误,所以我认为这可能是一个逻辑错误。有什么想法吗?
错误:
-
第二个
INSERT
语句中缺少RETURNING
子句。 -
也为第二个
INSERT
语句提供列的显式列表。 -
如果要启用列默认值(serial columns?),请不要在
INSERT
语句中提供NULL
。请使用关键字DEFAULT
,或者根本不提及该列。
更好的解决方案:
使用自PostgreSQL 9.1以来可用的数据屏蔽CTE,可以在一条语句中完成所有操作,并节省服务器的开销和往返行程。(MySQL对此一无所知,甚至连普通的CTE都不知道)。
此外,通过重新建模逻辑跳过UPDATE
。使用nextval()
检索一个id,然后只使用两个INSERT
语句。
假设这个数据模型(你应该在问题中提供):
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
数据模型
您可能会考虑将数据模型更改为经典的n:m关系。将包括这些表和主键:
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
如果一个用户只能属于一个institution
,则始终可以定义institution_staff.i_id UNIQUE
。