How to make a cascade insert into several tables at once, when one works right the other?

0

I have a table with the name of the questionnaire, questions and answers. I need to do an insert in the questionnaire table, then receive the id of the questionnaire table, when to insert the questions I insert with the id of the questionnaire, and the same thing with the answers. When I enter the answers insert with the question id. That is, insert the QUESTIONNAIRE TABLE, AFTER THE QUERY TABLE, AND AFTER THE ANSWER TABLE. I wanted to insert everything at once. I am 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 insert questions ($ id_questionary, $ question_text) {      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 (% with%)   ) ENGINE = InnoDB AUTO_INCREMENT = 92 DEFAULT CHARSET = utf8;   CREATE TABLE IF NOT EXISTS myid (      respostas int (8) NOT NULL AUTO_INCREMENT,      myid varchar (150) DEFAULT NULL,      texto_resposta int (150) NOT NULL,      id_perguntas varchar (300) DEFAULT NULL,     PRIMARY KEY ( correct ),     KEY myid ( fk_respostas_perguntas )   ) ENGINE = InnoDB DEFAULT CHARSET = utf8;   ALTER TABLE id_perguntas     ADD CONSTRAINT perguntas FOREIGN KEY ( fk_questionario_pergunta ) REFERENCES id_questionario ( questionario );

-

- Limiters for table myid

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

    
asked by anonymous 08.03.2018 / 19:56

1 answer

0

Using Mysql PDO

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

You start the transaction with

con->beginTransaction();

After each sql run (as desired), you retrieve the last inserted element ID with

$id = con->lastInsertId();

So you can reuse that $ id in the next sql.

con->rollBack();

If it works fine you end the transaction with commit

con->commit();

Fonts with code examples:

link

link

link

link

    
08.03.2018 / 23:49