php 在php中使用外键在表中插入值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33477661/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert values in table with foreign key in php
提问by Kaus
I am new to PHP and SQL. I simply want to register a user after a team of users has been created. Tables that i have used -
我是 PHP 和 SQL 的新手。我只想在创建用户团队后注册一个用户。我使用过的表格 -
team
团队
idTeam int(11) PK + AI
teamName varchar(25)
user
用户
idUser int(11) PK + AI
name varchar(30)
username varchar(30)
password varchar(30)
team int(11) FK
I have used the following code-
我使用了以下代码-
<?php
session_start();
include('conn.php');
$name=$_POST['name'];
$team=$_POST['team'];
$username=$_POST['username'];
$password=$_POST['password'];
$qry="SELECT * FROM team WHERE idTeam='$team";
if(mysqli_query($con,$qry)){
mysqli_query("INSERT INTO user(name, team, username, password)VALUES('$name', '$team', '$username', '$password')");
header("location: add_user.php?remarks=success");
mysqli_close($con);
}
else
mysqli_error($con);
?>
i used to get error- Mysql error 1452 - Cannot add or update a child row: a foreign key constraint failsMysql error 1452 - Cannot add or update a child row: a foreign key constraint fails
我曾经得到错误 - Mysql 错误 1452 - 无法添加或更新子行：外键约束失败 Mysql 错误 1452 - 无法添加或更新子行：外键约束失败
Example- I have pre-entered the contents of teamtable-
例子-我已经预先输入了团队表的内容-
idTeam - teamName
1 Arsenal
2 Chelsea
3 Liverpool
Now if i want to add a user then I would add him by entering in usertable-
现在如果我想添加一个用户，那么我会通过输入用户表来添加他-
idUser team name username password
1 2 abc root pass
So here i am unable to figure out what query should i use in PHP code?
所以在这里我无法弄清楚我应该在 PHP 代码中使用什么查询？
采纳答案by Gayan Fernando
There is a single quotation within your first sql query near idTeam='$team. idTeam is integer type. So it need not within single quotation. Make sure that you are passing value for $team variable that is exist in team table. Try following code.
在 idTeam='$team 附近的第一个 sql 查询中有一个单引号。idTeam 是整数类型。所以它不需要在单引号内。确保您正在传递团队表中存在的 $team 变量的值。尝试以下代码。
$name=$_POST['name'];
$team=$_POST['team'];
$username=$_POST['username'];
$password=$_POST['password'];
$qry="SELECT * FROM team WHERE idTeam=$team";
$result = mysqli_query($qry);
$num_rows = mysqli_num_rows($result);
if($num_rows > 0){
mysqli_query("INSERT INTO user(name, team, username, password)VALUES('$name', $team, '$username', '$password')");
}else{
echo "Team is not valid!!!";
}
mysqli_close($con);
header("location: add_user.php?remarks=success");
回答by Parris Varney
There needs to be exist a record in the team table that corresponds to the value being passed to $team
需要在团队表中存在与传递给的值相对应的记录 $team
回答by Ninju
Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.
外键关系涉及一个包含中心数据值的父表，以及一个具有相同值的子表指向其父表。FOREIGN KEY 子句在子表中指定。
It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.
如果父表中没有匹配的候选键值，它将拒绝任何试图在子表中创建外键值的 INSERT 或 UPDATE 操作。
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
