Group table data with INNER JOIN

Asked

Viewed 329 times

3

I want to create polls on my website but I have a problem relating two tables. Questions are repeating.

Table pergunta

id_question = 1: what do you think of the Samsung S8?

id_question = 2: what do you think of iPhone 8?

Table opção

id_question = 1: id_option = 1: good

id_question = 1: id_option = 2: bad

id_question = 1: id_option = 3: bad

id_question = 2: id_option = 4: good

id_question = 2: id_option = 5: bad

id_question = 2: id_option = 6: bad

How I want to show off:

o que você acha do Samsung s8?
bom
ruim
péssimo

o que você acha do iPhone 8?
bom
ruim
péssimo

Like you’re showing off:

o que você acha do Samsung s8
bom

o que você acha do Samsung s8
ruim

o que você acha do Samsung s8
péssimo

o que você acha do iPhone 8?
bom

o que você acha do iPhone 8?
ruim

o que você acha do iPhone 8?
péssimo

My code

$id_categoria = $_GET['id'];
$tabela = duas_tabelas($id_categoria, $mysqli);

foreach($tabela as $resultado){
  echo $resultado[1]."";
  echo $resultado[2]."";
};

On the page function (where this the Function).

function duas_tabelas($id_categoria, $mysqli) {
  if (isset($id_categoria)) {
    $stmt = $mysqli->prepare("faz a consulta");         
    $stmt->bind_param('i', $id_categoria);
    $stmt->execute();   
    $stmt->store_result();

    $stmt->bind_result($pergunta, $opcao);

    while ($stmt->fetch()) {
      $resultado = array($pergunta, $opcao);
      $resultss[] = $resultado;
    };

    return $resultss;
  };
};

I’m using INNER JOIN to relate, then use while to mount a array. and on the exhibition page I foreach to manipulate the array that comes from function. How to solve this problem?

php database
  • 2

    puts the query and the php code you are using

2 answers

2


I’ve had this problem and solved the following using an if to filter when the question is repeated, below an example more or less how the code would work 

$id_categoria = $_GET['id'];
$tabela = duas_tabelas($id_categoria, $mysqli);
$filtro = $tabela[0,0]; //recebe a primeira pergunta da tabela.
echo $filtro."";

foreach($tabela as $resultado){
       if($filtro != $resultado[1]){
   echo $resultado[1]."";
   }
   echo $resultado[2]."";
   $filtro = $resultado[1]
  };

  • I’ll test it here

  • Thank you very much, it worked here. but I have a question, if I want to put this in a form for a poll as I do?? <form>priemeira query aq</form> <form>second query aq</form

  • I tried but I’m not getting it

  • Guys this very complicated do this, please one here.

1

If you do not want to change the way the display code is implemented you can change the query to the following:

SELECT x.tipo,
       x.id_pergunta,
       x.id_opcao,
       x.descricao
  FROM (SELECT 0 AS tipo,
               p.id_pergunta,
               NULL as id_opcao,
               p.descricao
          FROM pergunta p
         UNION ALL
        SELECT 1 AS tipo,
               o.id_pergunta,
               o.id_opcao,
               o.descricao
          FROM opcao) x
 ORDER BY x.tipo, x.id_pergunta, x.id_opcao

The best way would be to use two querys, one for question and one for option and change your code PHP to display the information correctly.

  • Wouldn’t it be bad for database to make two queries? I’m thinking about the delay to display these queries. how would you look with two queries? my code is this. on the page display search. $table = unir_table($id_cotegoria, $mysqli); foreach($table as $result){ echo $result[1]." </br>"; echo $result[2]." </br>"; };

  • @Xironakamura would not be bad, after all are two different tables, in fact it would not make a difference because the access would be equal. As for the fact of displaying, you have to show first how your code is in the PHP now so we can adapt.

  • I’ll post the code.

Browser other questions tagged php database

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