Multiple choice questions in php and database

Asked

Viewed 1,148 times

1

Hello, I am setting up a search system with filter to search multiple choice questions on a website, according to the discipline, banking, position, year, etc. For this I registered the questions in a 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>";
}
}

?>

'
php

  • Your database is Mysql?

  • It is @Eduardoalmeida

  • Can be more specific when referring to issues and their alternatives?

  • Carolina, could you give an organized in the code? It is almost impossible to understand the way it is, nor organized because I did not understand your organization well.

  • Sorry, I tried to put as code by putting ' ' but I could not

  • Hi Carolina! I’m sorry I walked away. Well, I was wondering if you were able to resolve that issue. How are things going?

  • Hi @Eduardoalmeida, I’m still the same. Using the left Join as you indicated, appears on the screen all the alternatives that were registered in my table of alternatives, this without I have done the search for the questions. And when I do the search it continues appearing only the questions alone and the alternatives all together, that already appeared before the search.

Show 2 more comments

1 answer

1

UPDATING

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);

?>

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

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

Post the results so we can see how it’s going.

------------------------------------*

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

An example of the use would be the following:

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 query, i put Questao. and Alternative.Alternatives.: the first word is a nickname I gave to each table using the word AS the second word is the name of the field.

In the second line, it is explicit where I give the nickname to the table of questions and, in the third line, I give a nickname to the table of alternatives and I refer to it through the keywords LEFT JOIN.

On the last line, I finish the LEFT JOIN, who uses the word ON for group the values of the two tables by means of a common ground, in the case of the example, the field is ID_Questao which must exist in the two tables, so the sign of equal (can be any comparison sign, mass is more common to look for equalities, since there are others types of JOIN for other types of associations or junctions).

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

  • 1

    humm cool, when I put this in the database it worked. But how and where do I put this in my code, to appear on the site? and what about echo? was thus: 'SELECT Q.Description, A.Descricao FROM Table_questao AS Q LEFT JOIN table_reply AS A ON Q.id_questao = A.id_questao'

  • I edited the issue with an example of code application.

  • Hummm, in this part: <H1><? php echo $result['questao']; ? ></H1> <p><? php echo $result['alternative']; ? ></p> the question and the alternative would be the table fields? pq in my tables these two fields have the same name: Description. It didn’t work out, that must be why. I’ll try to change.

  • Yes, it’s the table fields. Actually, if you look at the example query, I nicknamed the fields returned from the database using the word AS. Suppose: SELECT campo_questao FROM tabela_questao and echo $resultado['campo_questao'], i am printing the field that is returned in the query. What I did was: SELECT campo_questao AS questao... and echo $resultado['questao'], calling the field by the nickname I gave.

  • Okay, I get it.. but I am doing something wrong, because I printed all the alternatives of my table on the screen without me having filtered the questions in the filter I created. I’ll edit the first code I posted to see how you’re doing

  • I took this part: <p><? php echo $result['Questao']; ? ></p> pq already had above, in my filter.

Show 1 more comment

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.