PHP / MySQL在提交时插入多个数据表 [英] PHP/MySQL insert into multiple data tables on submit
问题描述
我正在工作中构建一个Web应用程序,我有一个表单,用户可以在其中注册某些类型的信息。
I'm building a web application in work and I have a form where users can register certain types of information.
Username | Password | Company
现在我不确定该如何处理。我想要的是,当用户提交该注册表单时,用户名,密码和公司被写入一个数据表(用户),但因为用户数据表中的公司是外键引用,我需要将公司写入单独的数据表(公司)作为主键(当然,用户名也以1-1关系的形式写为FK参考)。
Now I'm unsure how to approach this. What I want is when the user submits that registration form Username, password and company get written to one data table(user) BUT because Company, in the user data table, is a foreign key reference I need Company to be written to a separate datatable(company) as a Primary Key (and of course username to be written as a FK reference as its a 1 - 1 relationship).
我不是在寻找你们提供了编码解决方案,因为我知道我的PHP和MYSQL,我只是在寻找一些伪代码算法来获得创意!
I'm not looking for a coded solution from you guys because I know my PHP and MYSQL I'm just looking for some pseudo code algorithms to get the creative juices flowing!
编辑:我使用的是PostgreSQL,而不是MYSQL,但是我敢肯定除了端口号和语法上的微小变化外,其他差别不大。
I AM USING POSTGRESQL not MYSQL but I'm pretty sure there's little difference except port numbers and small syntax changes
推荐答案
假定第一列是 id
:
- 保存公司
$ mysqli-> query( INSERT INTO company VALUES(NULL,'$ company_name');
- 获取该项目的ID。
$ company_id = $ mysqli-> insert_id;
- 使用该ID <$保存用户c $ c> $ mysqli-> query( INSERT INTO user VALUES(NULL,'$ username','$ password','$ company_id');
- 获取用户ID
$ user_id = $ mysqli-> insert_id;
- 用其更新公司:
$ mysqli-> query( UPDATE company SET user_id = $ user_id WHERE company_id = $ company_id);
- Save Company
$mysqli->query("INSERT INTO company VALUES (NULL, '$company_name')";
- Get id of this item.
$company_id = $mysqli->insert_id;
- Save User with this id
$mysqli->query("INSERT INTO user VALUES (NULL, '$username', '$password', '$company_id')";
- Get user's id
$user_id = $mysqli->insert_id;
- Update the company with it:
$mysqli->query("UPDATE company SET user_id = $user_id WHERE company_id = $company_id)";
这篇关于PHP / MySQL在提交时插入多个数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!