Group table data with INNER JOIN

Question

Group table data with INNER JOIN

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

I want to create polls on my site plus I have a problem to relate two tables. The questions are being repeated.

Table pergunta

Question_id = 1: What do you think of Samsung s8?

Question_id = 2: What do you think of the iPhone 8?

Table opção

question_id = 1: id_option = 1: good

question_id = 1: id_option = 2: bad

question_id = 1: id_option = 3: lousy

question_id = 2: id_option = 4: good

question_id = 2: id_option = 5: bad

question_id = 2: id_option = 6: very bad

How do I display:

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

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

How are you displaying:

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]."";
};

In the function page (where the functions are).

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 I use while to mount an array . and on the display page I foreach to manipulate the array that comes from function . How do I resolve this issue?

php

asked by anonymous 08.09.2017 / 15:17

2 answers

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 information correctly.

08.09.2017 / 15:32

Servlet Container (Tomcat) or Application Server? Protect the server from "false requests"

score 2 · Accepted Answer

I already 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]
  };