Problem in query mysql php pdo the data is repeated

You can make a query combining GROUP_CONCAT and GROUP_BY , that way it will bring you a column with all the answers and a separator. Then you do explode of 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 an outcome more or less like this:

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

Reading in PHP

The query written in this way 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 GROUP_CONCAT and internal order

You can add more data in GROUP_CONCAT , and we can also sort them by any column if we want. We will also apply COALESCE , to make sure that we will always have the same data, since if there is any value NULL in 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_answers will come like this:

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

We'd better see an example of the complete 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 id next and a point. This is a practical way to bring 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 />

USA IN YOUR CONSULTATION sql A "list group" this serves to group according to the data that you do not want to be repeated

example "SELECT * FROM table name list group table field you do not want to repeat"