How to cascade an Insert into several tables at once, when one works the other?

Question

I have a table with the name of questionnaire, questions and answers. I need to do an Insert in the question table, right after receiving the question table id, for when to insert the questions I insert with the question id, and the same thing with the answers. When I insert the answers insert with the question id. That is to say, it inserts QUESTIONS IN THE TABLE, THEN QUESTIONS IN THE TABLE ANSWER. I wanted to put it all in at once. I’m using php, and mysql.

Bank code.

public function novoquestionario($nome_questionario, $descricao, $categoria, $autor){
    try{$stmt = "
        INSERT INTO
        questionario (myid, nome_questionario, descricao, categoria, data, data_update,  autor)
        VALUES (NULL, :nome_questionario, :descricao, :categoria, :data, :data2, :autor)";
        $stmt = $this->con->prepare($stmt);
        $stmt->bindValue(":nome_questionario", $nome_questionario);
        $stmt->bindValue(":descricao", $descricao);
        $stmt->bindValue(":categoria", $categoria);
        $stmt->bindValue(":data", date('Y-m-d H:i:s'));
        $stmt->bindValue(":data2", date('Y-m-d H:i:s'));
        $stmt->bindValue(":autor", $autor);    
        $stmt->execute();
        return $this->processResults($stmt); 
    }
    catch (Exception $e){  
        $log = fopen('log.txt', 'a');
        fwrite($log, "ERRO EM 'pesRastreioDAO -> instPesRast' -=- DIA ".date("d/m/Y")." -=- HORA ".date("H:m:s")."\r\n".$e->getMessage()."\r\n");
        fclose($log);
        return false;
    }
}


private function processResults($stmt){
    $results = array();
    if($stmt) {
        while($row = $stmt->fetch(PDO::FETCH_OBJ)) {

            $objt = new Questionario();

            if (isset($row->myid)){
                $objt->setMyid($row->myid);
            }else{
                $objt->setMyid(NULL);
            }  
            if (isset( $row->nome_questionario)){
                $objt->setNome_questionario($row->nome_questionario);
            }else  {
                $objt->setnome_questionario(NULL);  
            }
            if (isset($row->descricao)){  
                $objt->setDescricao($row->descricao);
            }
            else{
                $objt->setDescricao(NULL);
            }
            if (isset($row->Categoria)){
                 $objt->setCategoria($row->categoria);
            }  
            else{
                $objt->setcategoria(NULL);     
            }
            if (isset( $row->autor)){
                $objt->setAutor($row->autor);
            }
            else{
                $objt->setautor(NULL);    
            }  
            if(isset( $row->data)){
                $objt->setData($row->data);
            }
            else{
                $objt->setdata(NULL);    
            }          
            if(isset( $row->Data_update )){
                $objt->setData($row->data);
            }
            else{
                $objt->setdata_update(NULL);    
            }      
            $results[] = $objt;

        }
    }
    return $results;   
}

public Function inserirperguntas ($id_questionario ,$texto_pergunta){ Try{

    $stmt = "
      INSERT INTO
        perguntas (myid, id_questionario, texto_pergunta)
      VALUES (null, :id_questionario, :texto_pergunta)";
    $stmt = $this->con->prepare($stmt);
    $stmt->bindValue(":id_questionario", $id_questionario);
    $stmt->bindValue(":texto_pergunta", $texto_pergunta);

    $stmt->execute();
  return $this->processResults($stmt); 
  }
  catch (Exception $e){  
    $log = fopen('log.txt', 'a');
    fwrite($log, "ERRO EM 'pesRastreioDAO -> instPesRast' -=- DIA ".date("d/m/Y")." -=- HORA ".date("H:m:s")."\r\n".$e->getMessage()."\r\n");
    fclose($log);
    return false;
  }
}

public function insertRespostas ($id_perguntas, $texto_resposta, $correct){
    try{
        $stmt = "
            INSERT INTO
            respostas (myid, id_perguntas, texto_resposta, correct)
            VALUES (null, :id_perguntas, :texto_resposta, :correct)";
        $stmt = $this->con->prepare($stmt);
        $stmt->bindValue(":id_perguntas", $id_perguntas);
        $stmt->bindValue(":texto_resposta", $texto_resposta);

        $stmt->bindValue(":correct", $correct);
        $stmt->execute();
        return $this->processResults($stmt); 
    }
    catch (Exception $e){  
        $log = fopen('log.txt', 'a');
        fwrite($log, "ERRO EM 'pesRastreioDAO -> instPesRast' -=- DIA ".date("d/m/Y")." -=- HORA ".date("H:m:s")."\r\n".$e->getMessage()."\r\n");
        fclose($log);
        return false;
    }
}

CREATE TABLE IF NOT EXISTS perguntas ( myid int(8) NOT NULL AUTO_INCREMENT, id_questionario int(8) NOT NULL, texto_pergunta varchar(300) NOT NULL, PRIMARY KEY (myid), KEY fk_questionario_pergunta (id_questionario) ) ENGINE=Innodb AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS questionario ( myid int(8) NOT NULL AUTO_INCREMENT, nome_questionario varchar(300) DEFAULT NULL, descricao varchar(300) DEFAULT NULL, categoria varchar(300) DEFAULT NULL, data datetime DEFAULT NULL, data_update datetime DEFAULT NULL, autor varchar(300) DEFAULT NULL, PRIMARY KEY (myid) ) ENGINE=Innodb AUTO_INCREMENT=92 DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS respostas ( myid int(8) NOT NULL AUTO_INCREMENT, texto_resposta varchar(150) DEFAULT NULL, id_perguntas int(150) NOT NULL, correct varchar(300) DEFAULT NULL, PRIMARY KEY (myid), KEY fk_respostas_perguntas (id_perguntas) ) ENGINE=Innodb DEFAULT CHARSET=utf8; ALTER TABLE perguntas ADD CONSTRAINT fk_questionario_pergunta FOREIGN KEY (id_questionario) REFERENCES questionario (myid);

--

-- Limiters for the table respostas

ALTER TABLE respostas ADD CONSTRAINT fk_respostas_perguntas FOREIGN KEY (id_perguntas) REFERENCES perguntas (myid); COMMIT;

Answer 1

Using Mysql PDO

con = new PDO( ... dados da conexão ...);

you start the transaction with

con->beginTransaction();

after each sql execute (as you wish), you recover the ID of the last inserted element with

$id = con->lastInsertId();

dai you can reuse that $id in next sql.

When a problem occurs at the time of giving the run (in case it returns false), you use the rollback to undo everything that has already been done (and don’t forget to use Try catch to recover Pdoexception exceptions)

con->rollBack();

If everything goes well you complete the commit transaction

con->commit();

Sources with code examples:

http://php.net/manual/en/pdo.lastinsertid.php

http://php.net/manual/en/pdo.begintransaction.php

http://php.net/manual/en/pdo.commit.php

http://php.net/manual/en/pdo.rollback.php