Rosamunda Rosamunda - 2 months ago 11
MySQL Question

MySQL duplicates results even after using DISTINCT every time I try to show more than one field of the same table

I have a MySQL query that shows a list of items after joining two tables:

SELECT contenidos.tituloContenido FROM contenidos
JOIN cursosContenidos ON cursosContenidos.contenidoID = contenidos.contenidoID
WHERE cursosContenidos.cursoID = ?;


There's no problem with this query, but, when I change it to show another field of the contenidos table, the results are duplicated.

So they get duplicated when doing:

SELECT contenidos.contenidoID, contenidos.tituloContenido FROM contenidos
JOIN cursosContenidos ON cursosContenidos.contenidoID = contenidos.contenidoID
WHERE cursosContenidos.cursoID = ?;


I've tried adding a
GROUP BY cursosContenidos.contenidoID
clause, and
DISTINCT
as well, but they keep appearing duplicated. Any idea on why?

To retrieve the results, I'm using PHP:

$mostrarContenidos = $conectar1->prepare("
SELECT contenidos.tituloContenido FROM contenidos
JOIN cursosContenidos ON cursosContenidos.contenidoID = contenidos.contenidoID
WHERE cursosContenidos.cursoID = ?;
");
$mostrarContenidos->bindParam(1, $cursoID);
$mostrarContenidos->execute();
$contenidos = $mostrarContenidos->fetch(PDO::FETCH_ASSOC);
if ($contenidos) {
echo '<h2>Contenidos del Curso</h2>';
foreach ($contenidos as $value) {
echo 'Id: '.$contenidos['contenidoID'].'<br>';
echo 'Title: '.$contenidos['tituloContenido'].'<br>';
}
} else {
echo 'error retrieving results';
}


The expected result is:

Id: 1

Title This is my super title


What I get:

Id: 1

Title This is my super title
Id: 1
Title This is my super title

Answer

Your SQL query is most likely not returning multiple rows - you are only outputting it twice. This is because your foreach statement loops over each column in the single row you've fetched, rather than each row of the result set.

To clarify a bit further, this line just fetches the first (and probably only) row:

$contenidos = $mostrarContenidos->fetch(PDO::FETCH_ASSOC);

The foreach loop goes through each column in the row, and outputs info about the whole row for each column.

If you want $contenidos to be an array of all rows, you could do this instead:

$contenidos = $mostrarContenidos->fetchAll(PDO::FETCH_ASSOC);