PHP / MySQL INSERT功能逻辑错误 [英] PHP/MySQL INSERT feature logic error

问题描述

我最近问了一个有关写入多个表的问题： PHP / MySQL插入到提交的多个数据表中

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

我现在已经尝试了此代码，实际代码中没有产生任何错误但是我得到的结果很奇怪。当用户单击注册时，将调用 insert.php页面，并且可以在下面找到代码。

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?

推荐答案

错误：

• 返回 cla在第二个 INSERT 语句中缺少使用。

• RETURNING clause is missing in the second INSERT statement.

为第二个 INSERT 语句也是如此。

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

不提供 NULL INSERT c>语句，如果您想插入默认列（串行列？）。请使用关键字 DEFAULT 或根本不提及该列。

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.

更好的解决方案：

使用数据混合CTE ，自PostgreSQL 9.1起可用只需一条语句即可完成所有操作，并节省了开销和往返服务器的往返费用。 （MySQL一无所知，甚至连普通的CTE都不知道。）

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).

另外，跳过 UPDATE 对逻辑建模。使用 nextval（） ，并仅使用两个 INSERT 语句。

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

此 one 查询可以完成所有操作：

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

---

数据模型

您可能会考虑将数据模型更改为经典的n：m关系。
将包括以下表和主键：

---

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

如果用户只能属于一个<，则始终可以定义 institution_staff.i_id UNIQUE 。 code>机构。

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

这篇关于PHP / MySQL INSERT功能逻辑错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！