php - tutorial - El problema SQL JOIN no devuelve los detalles de usuario de la consulta y los imprime en matriz

the django project

Question

Tengo un rol que requiere ciertos certificados necesarios antes de que un usuario pueda ser asignado a ese rol.

Estoy intentando crear una lista de usuarios del DB que tienen 1 o más certificados coincidentes para este rol en particular. (También estoy mirando el último certificado que obtuvo el usuario y comparando solo ese con el rol)

La consulta funciona realmente bien siempre que un usuario tenga los dos certificados necesarios para esa función.

Si el usuario no coincide con todos los certificados requeridos, los detalles de los detalles de ese usuario no vuelven.

• Primero le mostraré el método que estoy ejecutando para unir los usuarios, luego le mostraré el resultado de la matriz

• Verá que los primeros 2 usuarios (rob, holly) array 0 y 1 coinciden con los dos certificados necesarios para el rol, por lo que toda su información, como id, imagen y nombre, vuelve a aparecer en la primera parte del conjunto.

• la matriz del tercer usuario (daniel) (2) solo coincide con una, por lo que no obtengo su nombre o detalles en la primera parte de la matriz.

• la 4ta matriz del usuario (katie) (3) no coincide con ninguna, así que devuelvo una matriz completamente en blanco al final.

• Necesito que Daniel y Katie al menos devuelvan sus nombres y idimage para que pueda imprimirlos en la pantalla.

Pensé que era algo relacionado con LEFT JOIN en el usuario, pero incluso si hago RIGHT JOIN, nada vuelve. Si pego un FULL JOIN, la consulta falla por completo.

 //$allcertificates contains ids of certificates required for role
 public function getStaffByProjectRoleCertificates($allCertificates)
{
    //get all active users
    $allUsers = $this->allActiveUser();
    $users = array();
    $temp = 0;

    //loop through the users to find matches
    for($i = 0; $i < count($allUsers); $i++)
    {
        $allRows = array();

        for($j = 0; $j < count($allCertificates); $j++)
        {
            $query = sprintf("
SELECT cert.certName, usercert.idusercert, usercert.usercertEnd, user.iduser, user.idimage, user.userForename, user.userSurname, certStatus.certStatusName FROM usercert 
LEFT JOIN user ON user.iduser = usercert.iduser
INNER JOIN cert ON usercert.idcert = cert.idcert
INNER JOIN certStatus ON certStatus.idcertStatus = usercert.idcertStatus


WHERE 
usercert.iduser=%s AND usercert.idcert=%s AND
NOT EXISTS
(SELECT * FROM `usercert` as u2 WHERE u2.iduser=%s AND u2.idcert=%s AND u2.idcert = usercert.idcert
AND u2.idusercert > usercert.idusercert)
",
            $this->db->GetSQLValueString($allUsers[$i], "int"),
            $this->db->GetSQLValueString($allCertificates[$j], "int"),
            $this->db->GetSQLValueString($allUsers[$i], "int"),
            $this->db->GetSQLValueString($allCertificates[$j], "int"));
            $result = $this->db->query($this->db->link, $query) or die($this->db->error($this->db->link));
            $numRows = $this->db->num_rows($result);
            $row = $this->db->fetch_assoc($result);

            array_push($allRows, $row);

            if($numRows !=0)
            {
                $temp += 1;
            }

            if($j == count($allCertificates) -1)
            {
                array_push($users, array("iduser" => $row[''iduser''], ''idimage''=>$row[''idimage''], ''forename''=>$row[''userForename''], ''surname''=>$row[''userSurname''], "cert_no" => $temp, "data"=>$allRows));
                $temp =0;
            }
         }
    }       
     function sortByOrder($a, $b)
    {
        return $b[''cert_no''] - $a[''cert_no''];
    }
    usort($users, ''sortByOrder'');
    print_r($users);
    return $users;
}

y la matriz superada

  Array
  (
[0] => Array
    (
        [iduser] => 90
        [idimage] => 
        [forename] => Holly
        [surname] => Bain
        [cert_no] => 2
        [data] => Array
            (
                [0] => Array
                    (
                        [certName] => Full UK Driving License
                        [idusercert] => 21
                        [usercertEnd] => 2016-05-31
                        [iduser] => 90
                        [idimage] => 
                        [userForename] => Holly
                        [userSurname] => Bain
                        [certStatusName] => Expiring In 2 Months
                    )

                [1] => Array
                    (
                        [certName] => test certificate
                        [idusercert] => 22
                        [usercertEnd] => 2016-05-31
                        [iduser] => 90
                        [idimage] => 
                        [userForename] => Holly
                        [userSurname] => Bain
                        [certStatusName] => Expiring In 2 Months
                    )

            )

    )

[1] => Array
    (
        [iduser] => 88
        [idimage] => 197
        [forename] => Robert
        [surname] => Bain
        [cert_no] => 2
        [data] => Array
            (
                [0] => Array
                    (
                        [certName] => Full UK Driving License
                        [idusercert] => 24
                        [usercertEnd] => 2017-07-01
                        [iduser] => 88
                        [idimage] => 197
                        [userForename] => Robert
                        [userSurname] => Bain
                        [certStatusName] => Expiring In 2 Months
                    )

                [1] => Array
                    (
                        [certName] => test certificate
                        [idusercert] => 19
                        [usercertEnd] => 2016-05-31
                        [iduser] => 88
                        [idimage] => 197
                        [userForename] => Robert
                        [userSurname] => Bain
                        [certStatusName] => Expiring In 2 Months
                    )

            )

    )

[2] => Array
    (
        [iduser] => 
        [idimage] => 
        [forename] => 
        [surname] => 
        [cert_no] => 1
        [data] => Array
            (
                [0] => Array
                    (
                        [certName] => Full UK Driving License
                        [idusercert] => 20
                        [usercertEnd] => 2016-05-31
                        [iduser] => 86
                        [idimage] => 196
                        [userForename] => Daniel
                        [userSurname] => Robinson
                        [certStatusName] => Expiring In 2 Months
                    )

                [1] => 
            )

    )

[3] => Array
    (
        [iduser] => 
        [idimage] => 
        [forename] => 
        [surname] => 
        [cert_no] => 0
        [data] => Array
            (
                [0] => 
                [1] => 
            )

    )

 )

Answer 1

Usted tiene un error lógico bastante importante en su consulta SQL. Para simplificarlo un poco:

SELECT *
FROM usercert 
LEFT JOIN user ON user.iduser = usercert.iduser
INNER JOIN cert ON usercert.idcert = cert.idcert
INNER JOIN certStatus ON certStatus.idcertStatus = usercert.idcertStatus
WHERE usercert.iduser=%s AND usercert.idcert=%s

Como entiendo, Katie no tiene ningún certificado. Por lo tanto, no hay filas en usercert , por lo que no hay nada para LEFT JOIN usercert , por lo que su información de usuario no se proporciona.

Cambiarlo a

SELECT *
FROM user
LEFT JOIN usercert 
    ON usercert.iduser = user.iduser
    AND usercert.idcert = %s
LEFT JOIN cert ON cert.idcert = usercert.idcert AND 
LEFT JOIN certStatus ON certStatus.idcertStatus = usercert.idcertStatus

WHERE user.iduser = %s

AND NOT EXISTS
(SELECT * FROM `usercert` as u2 
 WHERE u2.iduser = user.iduser 
 AND u2.idcert = cert.idcert
 AND u2.idcert = usercert.idcert
 AND u2.idusercert > usercert.idusercert)

Por lo tanto, FROM user para que los datos del usuario siempre estén incluidos. Y LEFT JOIN usercert con ese certificado ESPECÍFICO (si lo ponemos en el WHERE -clause todavía no devuelve ninguna fila).

Prima:

Y para hacerlo más eficiente (ahora ejecuta consultas count($allUsers) * count($allCertificates) , que se sumarán si tiene muchos usuarios):

Cambiarlo a:

LEFT JOIN usercert 
    ON usercert.iduser = user.iduser
    AND usercert.idcert = IN (%s, %s, %s)
[..snip..]
WHERE user.status = ''active''
AND NOT EXISTS ([..snip..])
ORDER BY user.iduser

Entonces tienes todos los datos en una sola consulta. Verifique que la consulta arroje los resultados correctos (phpmyadmin) y analícelos en PHP.

foreach($rows as $row) {
   if (same iduser as previous) {
       add certificate to item in $users
   } else {
       $user[] = ...
   }
}