Multi-choice questions in php and database [closed]

1

Hello, I'm building a filter search system to search multiple choice questions on a site, according to discipline, banking, position, year, etc. For this I registered the questions in one table in the database and the alternatives in another table. Now I need the question and its alternatives to come together. How do I do that? follow the code:

'

//////////////////////DISCIPLINAS//////////////////////////////////////////////
$sql_disciplina = "SELECT * FROM tabela_disciplina ORDER BY Disciplina ASC";
$pega_disciplina = mysqli_query($connection, $sql_disciplina);

    ////////////////////////////////////////BANCAS/////////////////////////////////
    $sql_banca="SELECT * FROM tabela_banca ORDER BY Banca ASC";
    $pega_banca = mysqli_query($connection,$sql_banca);

////////////////////////////////////////INSTITUIÇÃO/////////////////////////////////
$sql_instituicao="SELECT * FROM tabela_instituicao ORDER BY Instituicao ASC";
$pega_instituicao = mysqli_query($connection,$sql_instituicao);

////////////////////////////////////////CARGO/////////////////////////////////
$sql_cargo="SELECT * FROM tabela_cargo ORDER BY Cargo ASC";
$pega_cargo = mysqli_query($connection,$sql_cargo);

////////////////////////////////////////ANO////////////////////////////////////
$sql_ano="SELECT * FROM tabela_ano ORDER BY Ano ASC";
$pega_ano = mysqli_query($connection,$sql_ano);

//////////////////////NÍVEL//////////////////////////////////////////////
$sql_nivel = "SELECT * FROM tabela_nivel ORDER BY id_nivel ASC";
$pega_nivel = mysqli_query($connection, $sql_nivel);

////////////////////////////////TRAZ AS QUESTÕES DE ACORDO COM O CRITÉRIO/////
if(!empty($_POST['bt_enviar'])) 
{
$disciplina = (empty($_POST['Disciplina']))? 'null' : $_POST['Disciplina'];
$banca = (empty($_POST['Banca']))? 'null' : $_POST['Banca'];
$instituicao = (empty($_POST['Instituicao']))? 'null' : $_POST['Instituicao'];
$cargo = (empty($_POST['Cargo']))? 'null' : $_POST['Cargo'];
$ano = (empty($_POST['Ano']))? 'null' : $_POST['Ano'];
$nivel = (empty($_POST['Nivel']))? 'null' : $_POST['Nivel'];
$sql_questao="SELECT * FROM tabela_questao WHERE id_disciplina = $disciplina OR id_banca = $banca OR id_ano = $ano OR id_nivel = $nivel OR id_instituicao = $instituicao OR id_cargo = $cargo";
$seleciona_questao = mysqli_query($connection,$sql_questao);
}

////////////////////////////////TRAZ AS ALTERNATIVAS DE ACORDO COM AS QUESTÕES/////

$query = "SELECT Q.Questao, A.Alternativa
FROM Tabela_questao AS Q
LEFT JOIN tabela_resposta AS A
ON Q.id_questao = A.id_questao";

$executar_query = mysqli_query($connection, $query); 

while($resultado = mysqli_fetch_array($executar_query)) {?>



<p><?php echo $resultado['Alternativa']; ?></p>

<?php
}
mysqli_close($connection);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>Busca</title>
</head>
<body>
<form action="" method="post" enctype="multipart/form-data">

<span></span>
<select name="Disciplina">
<option value="" selected="selected">Selecione a disciplina</option>
<?php
if(mysqli_num_rows($pega_disciplina) == 0) {
echo '<option value="">Não foram encontradas disciplinas</option>';
}else{
while($linha = mysqli_fetch_array($pega_disciplina)){
echo '<option value="'.$linha['id_disciplina'].'">'.utf8_encode($linha['Disciplina']).'</option>';
}
}
?>

</select>
<span></span>
<select name="Banca">
<option value="" selected="selected">Selecione a banca</option>
<?php
if(mysqli_num_rows($pega_banca) == 0) {
echo '<option value="">Não foram encontradas bancas</option>';
}else{
while($linhaB = mysqli_fetch_array($pega_banca)){
echo '<option value="'.$linhaB['id_banca'].'">'.utf8_encode($linhaB['Banca']).'</option>';
}
}
?>


</select>
<span></span>
<select name="Instituicao">
<option value="" selected="selected">Selecione a instituição</option>
<?php
if(mysqli_num_rows($pega_instituicao) == 0) {
echo '<option value="">Não foram encontradas instituições</option>';
}else{
while($linhaD = mysqli_fetch_array($pega_instituicao)){
echo '<option value="'.$linhaD['id_instituicao'].'">'.utf8_encode($linhaD['Instituicao']).'</option>';
}
}
?>


</select>
<span></span>
<select name="Cargo">
<option value="" selected="selected">Selecione o cargo</option>
<?php
if(mysqli_num_rows($pega_cargo) == 0) {
echo '<option value="">Não foram encontrados cargos</option>';
}else{
while($linhaD = mysqli_fetch_array($pega_cargo)){
echo '<option value="'.$linhaD['id_cargo'].'">'.utf8_encode($linhaD['Cargo']).'</option>';
}
}
?>


</select>
<span></span>
<select name="Nivel">
<option value="" selected="selected">Selecione o nível</option>
<?php
if(mysqli_num_rows($pega_nivel) == 0) {
echo '<option value="">Não foram encontrados níveis</option>';
}else{
while($linhaC = mysqli_fetch_array($pega_nivel)){
echo '<option value="'.$linhaC['id_nivel'].'">'.utf8_encode($linhaC['Nivel']).'</option>';
}
}
?>


</select>
<span>Ano</span>
<select name="Ano">
<option value="" selected="selected">Selecione o Ano</option><br />
<br />
<?php
if(mysqli_num_rows($pega_ano) == 0) {
echo '<option value="">Não foram encontrados</option>';
}else{
while($linhaB = mysqli_fetch_array($pega_ano)){
echo '<option value="'.$linhaB['id_ano'].'">'.utf8_encode($linhaB['Ano']).'</option>';
}
}
?>

<br /><br /><input type="submit" name="bt_enviar" value="buscar" /><br />
</form>
<hr />

<?php 
if(!empty($_POST['bt_enviar'])) 
{
if(mysqli_num_rows($seleciona_questao) == 0)
{
echo '<h1>Desculpe, mas sua busca, não retornou resultados</h1>';
}
else
{
echo "<ul>";
while($linhaquestao = mysqli_fetch_array($seleciona_questao)){
echo '<li>'.utf8_encode($linhaquestao['Questao']).'</li>';
}
echo "</ul>";
}
}

?>

'
    
asked by anonymous 13.03.2016 / 21:35

1 answer

1

UPDATE

You can print the values as follows then.

<?php
//sua query: se ficar muito grande passe pra uma variável, só por questão de visualização

$query = "SELECT Questao.Questoes AS questao, Alternativa.Alternativas AS alternativas
FROM TABELA_DE_QUESTOES AS Questao
LEFT JOIN TABELA_DE_ALTERNATIVAS AS Alternativa
ON Questao.ID_Questao = Alternativa.ID_Questao";

$executar_query = mysqli_query($con, $query); //primeiro sua conexao, depois sua query

//iteração pelos resultados
while($resultado = mysqli_fetch_array($executar_query)){ ?>

<h1><?php echo $resultado['questao']; ?></h1>

<p><?php echo $resultado['alternativa']; ?></p>

<?php
   
}

mysqli_close($conn);

?>

Notice: I I escaped HTML in the while() loop.

Always use mysqli functions and not mysql , because they are obsolete .

Put the results to see how it goes.

------------------------------------

It's very simple, Carolina. Use LEFT JOIN for 1: N associations (a record of a table - question - for several records of another table - choices / alternatives).

An example of usage would be as follows:

SELECT Questao.Questoes, Alternativa.Alternativas
FROM TABELA_DE_QUESTOES AS Questao
LEFT JOIN TABELA_DE_ALTERNATIVAS AS Alternativa
ON Questao.ID_Questao = Alternativa.ID_Questao

Explaining:

In the first line of the query , I placed Questao.Questoes

In the last line, I end LEFT JOIN , which uses the word ON to group the values of the two tables by means of a common field , in the In the case of the example, the field is ID_Questao that must exist in both tables, so the equals sign (can be any sign of comparison, mass is more common to look for equalities, since there are other JOIN types for other types of associations or joins ).

Well, try and send the results to guys here !!!

    
14.03.2016 / 03:00