'Save to 2 different tables with the same id - PHP

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: enter image description here

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

phpmysql


Solution 1:[1]

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

Solution 2:[2]

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.

Solution 3:[3]

Thank you for those who commented here. I already got the answer guys based to your comments I just adjust my code. Thank you so much.

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

        {
       
            $connection = $this->openConnection();
            $stmt = $connection->prepare("INSERT INTO `log_db`(`type`, `subject`, `added_by`) VALUES(?,?,?)");
            $stmt->execute([$type,$subject,$added_by]);
                
            $last_id = $connection->lastInsertId();
            $N = count($person_involved);
            for($i=0; $i < $N; $i++)
            
              $connection->query("INSERT INTO `person_envolve`(`log_id`, `person_envolved`) VALUES('$last_id','$person_involved[$i]')");
                
            echo header("Location:index.php");
          
        
        }
    }

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Joffrey Schmitz
Solution 3 Victor Macion Jr.

Related Questions