How can I get the ID after it saved to log_db and save after into person_envolve log_id table?

Here's my code guys. I tried everything I know but I copy here the code which you can easily understand my question.

if(isset($_POST['savelog'])){
        
        $type = $_POST['type'];
        $person_involved = $_POST['person_involved'];
        $subject = $_POST['subject'];
        $added_by = $_POST['added_by'];

        $N = count($person_involved);
        for($i=0; $i < $N; $i  )
        {
        
            $connection = $this->openConnection();
            $stmt = $connection->prepare("INSERT INTO `log_db`(`type`, `person_involve`, `subject`,             `added_by`) VALUES(?,?,?,?)");
            $stmt->execute([$type, $subject, $added_by]);


        //Getting the primary ID
            $select = $connection->prepare("SELECT * FROM log_db");
            $select->execute();
            $profile = $select->fetch();

        
        $stmt2 = $connection->prepare("INSERT INTO `person_envolve`(`log_id`, `person_involve`)             VALUES(?,?)");
            $stmt2->execute([$profile(the primary ID),$person_involved[$i]]);

            echo header("Location:index.php");
        }
    }

Here's my database:

log_db:

person_involve: I want to save here in log_id the primary id that currently save in log_db

CodePudding user response：

You can easily collect the ID from LAST Entry by using ($last_id = $stmt->id;) Follow the code below.

if(isset($_POST['savelog'])){
    
    $type = $_POST['type'];
    $person_involved = $_POST['person_involved'];
    $subject = $_POST['subject'];
    $added_by = $_POST['added_by'];

    $N = count($person_involved);
    for($i=0; $i < $N; $i  )
    {
    
        $connection = $this->openConnection();
        $stmt = $connection->prepare("INSERT INTO `log_db`(`type`, `person_involve`, `subject`,             `added_by`) VALUES(?,?,?,?)");
        $stmt->execute([$type, $subject, $added_by]);

    // //Getting the primary ID
    //     $select = $connection->prepare("SELECT * FROM log_db");
    //     $select->execute();
    //     $profile = $select->fetch();

        if ($connection->query($stmt) === TRUE) {
              $last_id = $stmt->id; //The Primary Key {ID} from log_db table
        
            //Submiting data in another table
            $stmt2 = $connection->prepare("INSERT INTO `person_envolve`(`log_id`, `person_involve`)             VALUES(?,?)");
            $stmt2->execute($last_id,$person_involved[$i]]); // $last_id is holding "the primary ID"

            echo header("Location:index.php");
        }     // Here you can put some ERROR info if result is FALSE
    }        
    
}

CodePudding user response：

You can use the LAST_INSERT_ID() function : https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.