Stackoverflow, greetings, I think this question because I have a system that registers projects and the students that integrate it, it saves them in a database, in a nested table PROJECT_ALUMNO that contains student_id and project_id, as well as student in a STUDENT table and the project in a PROJECT table.
The problem is that I have a form that generates two selects, one showing the projects that exist in the database and another showing in checkbox all the students that are in the database, I would like it to be conditioned on the students so that only those who are NOT enrolled appear, because if I add the students to a project they reappear for each of the projects, and a student could not be in more than one project.
Try to solve this with a LEFT JOIN that will only take those that your student_id was not in the table, but it happens that the students are not saved there directly, first you create the project, then you create the students and then you enroll them.
NOTE: the table of PROJECT_ALUMNO has as main key alumni_id project and as UNIQUE student_id field
Here I leave the codes thank you very much for your help:
1) Function showing all existing students
function bd_alumno_opciones()
{
$sql = "SELECT cedu_alum, CONCAT(cedu_alum,' ',nom1_alum,' ',nom2_alum,' ape1_alum,' ',ape2_alum) FROM alumno ORDER BY cedu_alum ASC";
$res = sql2options( $sql );
return $res;
}
2) Suggested function to only show those that are not registered (the problem occurs that the table itself will be empty, as explained above)
function bd_alumno_opciones()
{
$sql = "SELECT cedu_alum, CONCAT(cedu_alum,' ',nom1_alum,' ',nom2_alum,' ',ape1_alum,' ',ape2_alum)
FROM alumno LEFT JOIN proyecto_alumno ON cedu_alum = alum_id
WHERE proy_id = null ORDER BY cedu_alum ASC";
$res = sql2options( $sql );
return $res;
}
3) Form to register the students on the student_ project table (pro_registration4.php)
<?php
include 'conexion.php';
foreach ($_REQUEST['alum'] as $alumno_id)
{
$alumno = array('proy_id' => NULL, 'proy_id' => $_REQUEST['proy_id'], 'alum_id' => $alumno_id);
bd_proyecto_alumno_agregar($alumno);
}
header("Location: listado4.php");
exit;
4) Code saved in the Student_Project table
function bd_proyecto_alumno_agregar($d)
{
$sql = sprintf("INSERT INTO proyecto_alumno (proy_alum_id, proy_id, alum_id)
VALUES ('%s','%s','%s')",
$d['proy_requ_id'],
$d['proy_id'],
$d['alum_id']
);
$res = sql($sql);
$id = sql2value("SELECT LAST_INSERT_ID()");
return $id;
}
NOTE: The operation is like this:
The student registration form calls the file pro_inscripcion4.php