I am having a bit of a strange error on mysql pdo. How can I solve it?

Question

In my profile table I want to insert some data and my profile table structure look likes

  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;

Here is the position table look like

  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;    

Here is my insert query:

           $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());
                }

In these insert queries, I wanted to try insert into two tables at the same time when a user would click to the add button. Now my problem is: When I want to insert data into the profile and position table. It gives an error. Here is the error

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

So, now my question is: What is the problem in my code? If you can then please assist me to get the solution. Thank you in advance.

Answer 1

try this code , you should execute query before getting last insertid , and also you should execute queries inside loop not outside

$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());
}