When deleting the record from a nested table, it is deleted from a list, but the main table leaves its data in it

2

I have a list of data showing the title of a project (project table) and the students (student table) that are registered in it, these being stored in a nested table called Proyecto_Alumno.

I have the option of modifying and deleting the students of the project, but it happens that when I delete the record to delete the students and the project from the list of enrolled students, the project is the same in the list, students are eliminated but the project is not.

EYE: I would like to delete the project from the list, but not from the database

ERROR capture:

Here is the list of students and projects form

link

here the function to eliminate

function bd_proyecto_alumno_eliminar($id)
{    
$sql=sql("DELETE FROM proyecto_alumno WHERE proy_id = '$id'");
}

Function to obtain data:

function bd_proyecto_alumno_datos_todos(){
$sql = "SELECT 
          proy_id, proy_deno                
           FROM                
           proyecto
        ORDER BY proy_id ASC";
 $datos = sql2array( $sql );

 foreach ($datos as &$dato) { //faltaba el aspersan para que funcionara//
$dato['alumno']    = sql2array("SELECT 
                                    a.alum_id, b.cedu_alum,nom1_alum,nom2_alum, ape1_alum, ape2_alum
                                  FROM 
                                    proyecto_alumno a, alumno b 
                                  WHERE 
                                    a.proy_id='{$dato['proy_id']}' 
                                     AND a.alum_id=b.cedu_alum");
}
return $datos;
}
    
asked by Victor Alejandro Alvarado Vilo 08.09.2016 в 18:36
source

1 answer

2

The problem is that the table proyectos has all the projects that there are (or were), this by design (you explain in your question that you do not want it to be deleted from the database). In this case, instead of selecting all the projects to be shown, you should simply filter those that do not have students:

SELECT p.proy_id, p.proy_deno                
FROM proyecto p
WHERE EXISTS(SELECT 1 FROM proyecto_alumno 
             WHERE proy_id = p.proy_id)
ORDER BY p.proy_id ASC
    
answered by 08.09.2016 / 18:47
source