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
), KEYfk_questionario_pergunta
(id_questionario
) ) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8; CREATE TABLE IF NOT EXISTSquestionario
(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 EXISTSmyid
(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
), KEYmyid
(fk_respostas_perguntas
) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; ALTER TABLEid_perguntas
ADD CONSTRAINTperguntas
FOREIGN KEY (fk_questionario_pergunta
) REFERENCESid_questionario
(questionario
);
-
- Limiters for table myid
ALTER TABLE respostas
ADD CONSTRAINT respostas
FOREIGN KEY ( fk_respostas_perguntas
) REFERENCES id_perguntas
( perguntas
);
COMMIT;