在php中使用外键在表中插入值

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 - 无法添加或更新子行：外键约束failedMysql错误1452 - 无法添加或更新子行：外键约束失败

Example - I have pre-entered the contents of team table-

示例 - 我已预先输入了团队表的内容 -

idTeam - teamName
  1      Arsenal
  2      Chelsea
  3      Liverpool

Now if i want to add a user then I would add him by entering in user table-

现在，如果我想添加一个用户，那么我会通过输入用户表来添加他 -

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代码中使用什么查询？

3 个解决方案

#1

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

#2

There needs to be exist a record in the team table that corresponds to the value being passed to $team

团队表中需要存在与传递给$ team的值相对应的记录

#3

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

#1

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

#2