Mysql transaction error in php

Question

Good morning, forum guys. I’m trying to do the simultaneous Insert, then I’m using transaction control in php, only it doesn’t make the first Insert, so it generates me a Foreign key error.

public function insert(){
    try{

        self::getInstance()->beginTransaction();
        self::getInstance()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        //insert manufactures
        $insertPJ = "INSERT into trein_pj VALUES ( NULL,:cnpj,:razao_social,:nome_fantasia,:tel_comercial1,:tel_comercial2)";
        $stmt = DB::prepare($insertPJ);
        $stmt->bindParam(":cnpj",$this->cnpj,PDO::PARAM_STR);
        $stmt->bindParam(":razao_social",$this->razao_social,PDO::PARAM_STR);
        $stmt->bindParam(":nome_fantasia",$this->nomeFantasia,PDO::PARAM_STR);
        $stmt->bindParam(":tel_comercial1",$this->tel_comercial1,PDO::PARAM_STR);
        $stmt->bindParam(":tel_comercial2",$this->tel_comercial2,PDO::PARAM_STR);
        $stmt->execute();
        //insert peoples
        $insertPF = "INSERT INTO trein_pf VALUES (NULL,:nome,:cargo,:rg,:cpf,:tel_fixo,:tel_celular,:email,:escolaridade,:formacao,:tempo_experciencia,:aceite,:data_registro,:ip_registro,:id_trein_pj,:id_treinamento)";

        $stmt = DB::prepare($insertPF);
        $stmt->bindParam(":nome",$this->nome,PDO::PARAM_STR);
        $stmt->bindParam(":cargo",$this->cargo,PDO::PARAM_STR);
        $stmt->bindParam(":rg",$this->rg,PDO::PARAM_STR);
        $stmt->bindParam(":cpf",$this->cpf,PDO::PARAM_STR);
        $stmt->bindParam(":tel_fixo",$this->tel_fixo,PDO::PARAM_STR);
        $stmt->bindParam(":tel_celular",$this->tel_celular,PDO::PARAM_STR);
        $stmt->bindParam(":email",$this->email,PDO::PARAM_STR);
        $stmt->bindParam(":escolaridade",$this->escolaridade,PDO::PARAM_STR);
        $stmt->bindParam(":formacao",$this->formacao,PDO::PARAM_STR);
        $stmt->bindParam(":tempo_experciencia",$this->tempo_experiencia,PDO::PARAM_STR);
        $stmt->bindParam(":aceite",$this->aceite,PDO::PARAM_STR);
        $stmt->bindParam(":data_registro",$this->data_registro,PDO::PARAM_STR);
        $stmt->bindParam(":ip_registro",$this->ip_registro,PDO::PARAM_STR);
        $stmt->bindParam(":id_trein_pj",$this->id_trein_pj,PDO::PARAM_INT);
        $stmt->bindParam(":id_treinamento",$this->id_treinamento, PDO::PARAM_INT);

        $stmt->execute();
        $stmt->closeCursor();

        if (self::getInstance()->commit()) {
            return true;
        }

    }catch (PDOException $ex){
        echo $ex->getMessage();
        self::getInstance()->rollBack();
    }
}

I thank the members of the forum.

Answer 1

The FK error does not necessarily indicate that the first Insert did not occur, but rather that FK was not found.

After running the first Insert you need to store the ID generated in that Insert in order to bind the second Insert.

//insert manufactures
$stmt->execute();
//atribui a ID inserida ao atributo id_trein_pj do objeto
$this->id_trein_pj = DB::lastInsertId();