Home / Questions / 158842
Accepted
ByGroxD
Asked: 2020-04-25 10:37:18 +0800 CST 2020-04-25 10:37:18 +0800 CST

Problem in mysql php pdo query the data is repeated

  • 772

Companions of StackOverFlow, I have the following doubt, I am trying to make a simple test or evaluation system in php and I created my database in the following way:

+----------------+
| Tables_in_game |
+----------------+
| answers        |
| questions      |
+----------------+

Questions table

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | int(11)    | NO   | PRI | NULL    | auto_increment |
| question | text       | NO   |     | NULL    |                |
| type     | varchar(5) | NO   |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+

Answer table (Answers)

+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(11)    | NO   | PRI | NULL    | auto_increment |
| id_question | int(11)    | NO   |     | NULL    |                |
| answer      | text       | NO   |     | NULL    |                |
| is_right    | tinyint(1) | NO   |     | NULL    |                |
+-------------+------------+------+-----+---------+----------------+

My PHP PDO query is as follows:

$db = new PDO('mysql:host=localhost;dbname=game', 'root', '');
    $stmt = $db->prepare('SELECT * FROM questions LEFT JOIN answers ON (questions.id = answers.id_question) WHERE questions.type = :type');
    $type = "quiz3";
    $stmt->bindParam(':type', $type);
    $stmt->execute();

The problem is that when going through it, it prints the question several times according to the answers that the question has:

<div class="container">
        <?php foreach ($stmt as $val): ?>
        <p>
            <?= $val['question']; ?>
        </p>
        <ul>
            <li>
                <?= $val['answer']; ?>
            </li>
        </ul>
        <?php endforeach ?>
    </div>

What I need is to be able to print the question only once and to be able to print all the possible answers.

As an example I show how it should look:

This is question #1............

  1. Answer 1
  2. Answer #2
  3. Answer #3
  4. Answer #4
php

2 Answers

  1. Best Answer

    You can make a query by combining GROUP_CONCATand GROUP_BY, that way it will bring you a column with all the answers and a separator. Then you explode that column.

    For example:

    SELECT 
    q.id,
    q.question, 
    GROUP_CONCAT(a.answer SEPARATOR '|') all_answers
FROM questions q
LEFT JOIN answers a ON (q.id = a.id_question) 
WHERE q.type = :type
GROUP BY q.id

    You would have a result something like this:

    id    question                                    all_answers
----------------------------------------------------------------------------
1     ¿De qué color es el caballo blanco?         blanco|negro|incoloro

    Reading in PHP

    The query written like this will bring you an array similar to this:

    Array
(
    [0] => Array
        (
            [id] => 1
            [question] => ¿De qué color es el caballo blanco?
            [all_answers] => blanco|negro|incoloro
        )

    [1] => Array
        (
            [id] => 2
            [question] => ¿En qué batalla murió Napoleón?
            [all_answers] => En la última|En Waterloo|En ninguna
        )

)

    This would be an example of code to read the results:

    foreach ($arrDatos as $row){
    $strHTML="";
    $strHTML="Pregunta ".$row["id"].": ".$row["question"].PHP_EOL;
    $strHTML.="<ul>";
    $a=explode("|",$row["all_answers"]);
    foreach ($a as $v){
        $strHTML.= "<li>$v</li>";
    }
    $strHTML.="</ul><hr />";
    echo $strHTML;
}

    This code will print this:

    <p>Pregunta 1: ¿De qué color es el caballo blanco?</p>
<ul>
  <li>blanco</li>
  <li>negro</li>
  <li>incoloro</li>
</ul>
<hr /><p>Pregunta 2: ¿En qué batalla murió Napoleón?</p>
<ul>
  <li>En la última</li>
  <li>En Waterloo</li>
  <li>En ninguna</li>
</ul>
<hr />

    More data in the GROUP_CONCATand internal order

    You can add more data in the GROUP_CONCAT, and we can also order them by any column if we want. We will also apply COALESCE, to make sure that we always have the same data, since if there is any value NULLin any column our beautiful code will fail.

    Let's see:

    SELECT 
    q.id,
    q.question, 
    GROUP_CONCAT
       (
         COALESCE(a.id,''),'. '
         COALESCE(a.answer,'')
         SEPARATOR '|'ORDER BY a.id
       ) all_answers    
FROM questions q
LEFT JOIN answers a ON (q.id = a.id_question) 
WHERE q.type = :type
GROUP BY q.id

    Now the column all_answerswill come like this:

    all_answers
-----------------------------------
1. blanco|2. negro|3. incoloro

    Better let's see an example of the full array:

    Array
(
    [0] => Array
        (
            [id] => 1
            [question] => ¿De qué color es el caballo blanco?
            [all_answers] => 1. blanco|2. negro|3. incoloro
        )

    [1] => Array
        (
            [id] => 2
            [question] => ¿En qué batalla murió Napoleón?
            [all_answers] => 4. En la última|5. En Waterloo|6. En ninguna
        )

)

    Now we have the answer with its idnext to it and a period. This is a handy way to bring in grouped data without having to repeat rows.

    If we read the results with the same code as above, we would have something like this:

    <p>Pregunta 1: ¿De qué color es el caballa blanco?</p>
<ul>
  <li>1. blanco</li>
  <li>2. negro</li>
  <li>3. incoloro</li>
</ul>
<hr />
<p>Pregunta 2: ¿En qué batalla murió Napoleón?</p>
<ul>
  <li>4. En la última</li>
  <li>5. En Waterloo</li>
  <li>6. En ninguna</li>
</ul>
<hr />

    • 3

  2. USE IN YOUR sql QUERY A "list group" this is used to group according to the data that you do not want to be repeated

    example "SELECT * FROM table name list group table field you don't want to repeat"

    • 0