Delete with inner join

Question

Delete with inner join

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

Good I have a problem currently in my code, I'm working on a student registration system, where a representative can enroll several students, but if I delete a student I do not want the representative's data to be deleted because this may have another registered student, it would only be deleted if and only if it no longer has registered students.

PD: it is worth mentioning that the CedulaRepresentanteRef is in the table students and is the foreign key of CedulaRepresentante (Primary Key) located in the representative table.

<?php

$Conexion = mysqli_connect("localhost","root","","prueba2") or die ("No se pudo realizar la conexion");


$Registros = mysqli_query($Conexion, "select * from documentos, estatusalumno, padres, alumnos inner join representantes on alumnos.CedulaRepresentanteRef = representantes.CedulaRepresentante where alumnos.CedulaAlumno = '$_REQUEST[CedulaAlumno]'") or die ("Problemas en el select: ".mysqli_error($Conexion));


if ($Reg = mysqli_fetch_array($Registros))
$CedulaRepresentanteRef = $Reg['CedulaRepresentanteRef'];

{
    mysqli_query($Conexion, "delete from alumnos where CedulaAlumno = '$_REQUEST[CedulaAlumno]'")
    or die ("Problemas en el select: ".mysqli_error($Conexion));

    mysqli_query($Conexion, "delete from estatusalumno where CedulaAlumno = '$_REQUEST[CedulaAlumno]'")
    or die ("Problemas en el select: ".mysqli_error($Conexion));

    mysqli_query($Conexion, "delete from padres where CedulaAlumno = '$_REQUEST[CedulaAlumno]'")
    or die ("Problemas en el select: ".mysqli_error($Conexion));

    mysqli_query($Conexion, "delete from documentos where CedulaAlumno = '$_REQUEST[CedulaAlumno]'")
    or die ("Problemas en el select: ".mysqli_error($Conexion));



            $Registros2 = mysqli_query($Conexion, "select * from alumnos, representantes") or die 
            ("Problemas en el segundo registro: ".mysqli_error($Conexion));

            $Reg2 = mysqli_fetch_array($Registros2);

            if ($CedulaRepresentanteRef <> $Reg2['CedulaRepresentante']) 

            {
            mysqli_query($Conexion, "delete from representantes where CedulaRepresentante = $CedulaRepresentanteRef")
            or die ("Problemas en el select: ".mysqli_error($Conexion));
            }

            else 

            {
                echo "Se ha eliminado el alumno";
            }
}

else 

{
    echo "No se encontro el alumno";
}   

mysqli_close($Conexion);

  ?>

mysql php

asked by shadowmors 07.06.2016 в 23:52

source

2 answers

Create addEventListener in a span whose id is contained in a variable consequences of stopPropagation

score 2 · Answer 1

I detect an error in your code in the following code:

$Registros2 = mysqli_query($Conexion, "select * from alumnos, reñpresentantes") or die ("Problemas en el segundo registro: "....

I think you should change your SELECT , because you are consulting 2 tables without joining them ... And at the time of requesting the cedulaRepresentante it will not show you the information that you you occupy ...

score 0 · Answer 2

If I delete a student, I do not want the representative's data to be deleted because it can have another student enrolled.

Before removing the representative from the blind you must verify that the representative is not associated with any other student. For this, you can use a query as follows:

//no conozco la estructura correcta de tus tablas, sería bueno
//si mostraras la relación entre alumno y cédula del representante
//o que arregles este query debidamente
$stmt = mysqli_prepare("SELECT COUNT(1) FROM alumnos WHERE CedulaRepresentante = ?");
mysqli_stmt_bind_param($stmt, "s", $CedulaRepresentanteRef);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $alumnosDeRepresentante);
if ($alumnosDeRepresentante === 0) {
    //borrar representante
    //utiliza mysqli apropiadamente, como se muestra en el código previo
    mysqli_query($Conexion, "delete from alumnos where CedulaAlumno = '$_REQUEST[CedulaAlumno]'");
}

It is not part of the response, but your code is not protected against SQL injection attacks. Please review this question and answer regarding the topic.