我在mysql-pdo上有一个奇怪的错误.我该如何解决

Question

在我的配置文件表中，我想插入一些数据，我的配置表结构看起来像

CREATE TABLE Profile (
profile_id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
first_name TEXT,
last_name TEXT,
email TEXT,
headline TEXT,
summary TEXT,
PRIMARY KEY(profile_id),
CONSTRAINT profile_ibfk_2
FOREIGN KEY (user_id)
REFERENCES users (user_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是看起来像的位置表

CREATE TABLE Position (
position_id INTEGER NOT NULL AUTO_INCREMENT,
profile_id INTEGER,
rank INTEGER,
year INTEGER,
description TEXT,
PRIMARY KEY(position_id),
CONSTRAINT position_ibfk_1
FOREIGN KEY (profile_id)
REFERENCES Profile (profile_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;    

这是我的插入查询：

$insert = $con->prepare("insert into 
profile(user_id,first_name,last_name,email,headline,summary)


values(:user_id,:firstName,:lastName,:email,:headline,:summary)");

$insert->bindParam(':user_id', $user_id);
$insert->bindParam(':firstName', $firstName);
$insert->bindParam(':lastName', $lastName);
$insert->bindParam(':email', $email);
$insert->bindParam(':headline', $headline);
$insert->bindParam(':summary', $summary);
//Now need to execute the operation
// $insert->execute();
$profile_id=$con->lastInsertId();
//insert to position
$rank=1;
for($i=1; $i<=9; $i++){
if(! isset($_POST['year'.$i])) continue;
if(! isset($_POST['desc'.$i])) continue;
$insert = $con->prepare("insert into position 
(profile_id,rank,year,description)
values(:profile_id,:rank,:year,:desc)");
//Here we need to bind those variable for storing 
value into the database
$insert->bindParam(':profile_id', $profile_id);
$insert->bindParam(':rank', $rank);
$insert->bindParam(':year', $_POST['year'.$i]);
$insert->bindParam(':desc', $_POST['desc'.$i]);
//Now need to execute the operation
$rank++;
//   $insert->execute();
}
if($insert->execute()){
$_SESSION['profile_addition'] = 'Profile added';
header("location:index.php");
}
else{
print_r($insert->errorInfo());
}

在这些插入查询中，当用户单击添加按钮时，我想尝试同时插入两个表。现在我的问题是：当我想将数据插入配置文件和位置表时。它给出了一个错误。这是错误

Array ( [0] => 23000 [1] => 1452 [2] => Cannot add or update a child 
row: a foreign key constraint fails (`courseraassignment`.`position`, 
CONSTRAINT `position_ibfk_1` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`profile_id`) ON DELETE CASCADE ON UPDATE CASCADE) )

所以，现在我的问题是：我的代码中有什么问题？如果可以的话，请帮我找到解决方案。提前谢谢。

Answer 1

试试这个代码，你应该在获得最后一个insertid之前执行查询，而且你应该在循环内而不是在外执行查询

$insert = $con->prepare("insert into 
profile(user_id,first_name,last_name,email,headline,summary)

values(:user_id,:firstName,:lastName,:email,:headline,:summary)");
$insert->bindParam(':user_id', $user_id);
$insert->bindParam(':firstName', $firstName);
$insert->bindParam(':lastName', $lastName);
$insert->bindParam(':email', $email);
$insert->bindParam(':headline', $headline);
$insert->bindParam(':summary', $summary);
//Now need to execute the operation
$insert->execute(); // uncomment this execute
$profile_id=$con->lastInsertId();
//insert to position
$rank=1;
$checker = false ;
for($i=1; $i<=9; $i++){
if(! isset($_POST['year'.$i])) continue;
if(! isset($_POST['desc'.$i])) continue;
$insert = $con->prepare("insert into position 
(profile_id,rank,year,description)
values(:profile_id,:rank,:year,:desc)");
//Here we need to bind those variable for storing 
value into the database
$insert->bindParam(':profile_id', $profile_id);
$insert->bindParam(':rank', $rank);
$insert->bindParam(':year', $lastName);
$insert->bindParam(':desc', $email);
//Now need to execute the operation inside loop 
$rank++;
if($insert->execute()){
$checker = true ;
}
}
// here you can't check execute 
if( $checker){
$_SESSION['profile_addition'] = 'Profile added';
header("location:index.php");
}
else{
print_r($insert->errorInfo());
}