Well I'm making an school project and I need to get the students from table 'alumnos' (I'm from Mexico so my table is in spanish) that are in an specific career (table is 'carreras') from example engineering in computer Systems.
Well I get the results(I have 2 students) but it duplicates each one three times and I don't know why
This is the code I'm using:
<?php
include_once ("conexion.php");
$Idcar = $_POST["Combo"];
$sql="SELECT Car_ID_pk FROM carreras WHERE Car_nombre = '$Idcar'";
$cs=mysql_query($sql, $cn);
while($row=mysql_fetch_array($cs))
{
$idcarrera=$row["Car_ID_pk"];
}
$query = "SELECT alumnos.Al_id_pk, alumnos.Al_nombre, alumnos.Al_ap_pat, alumnos.Al_ap_mat FROM alumnos,carreras WHERE alumnos.Carrera_ID= '$idcarrera'";
$cs1 = mysql_query($query,$cn);
$i = 0;
$dyn_table = '<table border="1" cellpadding="10">';
$dyn_table .= '<tr><td>Numero de control</td><td>Nombre</td><td>Apellido paterno</td><td>Apellido materno</td></tr>';
while($row = mysql_fetch_array($cs1)){
$NumControl = $row["Al_id_pk"];
$Nombre = $row["Al_nombre"];
$Paterno = $row["Al_ap_pat"];
$Materno = $row["Al_ap_mat"];
if ($i % 4 == 0)
{
$dyn_table .= '<tr><td>' .$NumControl. '</td><td>'.$Nombre.'</td><td>'.$Paterno.'</td><td>'.$Materno.'</td>';
} else
{
$dyn_table .= '<tr><td>' . $NumControl . '</td><td>'.$Nombre.'</td><td>'.$Paterno.'</td><td>'.$Materno.'</td></tr>';
}
$i++;
}
$dyn_table .= '</tr></table>';
?>
<html>
<body>
<h3>Alumnos registrados.</h3>
<?php echo
$dyn_table; ?>
<form name="RS" method="POST" action="ABCCarrera.php">
<input type="submit" value="Salir" />
</form>
</body>
</html>
like you can see im using an implicit join, there is a way i can fix this and where is my error?
You're using an implicit join, but you're not filtering that join in the WHERE clause, so you're getting the Cartesian product of the two tables: each record in the alumnos table gets paired with every record in the carerras table.
In your current query, you're not actually retrieving any data from the carreras table, so you can just remove it from the FROM clause, and that'll make the duplicates go away. But if you really need to do the join — for example, if you want to add some of the carreras columns to the SELECT list — then you need to add something like:
AND carreras.id = alumnos.Carrera_ID
to your WHERE clause. That ensures that each student is matched with only that student's career, not all the careers.
To help avoid this sort of mistake, you can use an explicit join:
FROM alumnos
JOIN carreras
ON carreras.id = alumnos.Carrera_ID
WHERE alumnos.Carrera_ID = (whatever)
The ON clause is required in an explicit JOIN, so if you forget it, the database will tell you.
Explicit joins also keep the join criteria (in the ON clause) separate from the filter criteria (in the WHERE clause), which can help to make your SQL easier to understand, especially in complex queries.