SQL JOIN issue not bringing user details back from query and printing them in array

Question

I have a role that requires certain certificates needed before a user can be assigned to that role.

I'm trying to build a list of users from the DB that have 1 or more matching certificates for this particular role. (I'm also only looking at the last certificate the user gained and comparing only that one to the role)

The query works really well as long as a user actually has both of the certificates required for that role.

If the user doesn't match all the certificates required the details of that users details are not coming back.

• First I will show you the method i'm running to match the users then I will show you the array output result

• you will see the first 2 users (rob, holly) array 0 and 1 match both certificates required for the role so all of their information like id, image and name come back in the first part of the array.

• the 3rd user (daniel) array(2) only matches one so I don't get his name or details in the first part of the array.

• the 4th user (katie) array(3) doesn't match any so I return a completely blank array at the end.

• I need both Daniel and Katie to at least return their names and idimage so I can print them to the screen.

I figured it was something to do with the LEFT JOIN on user but even if I do a RIGHT JOIN nothing comes back. If I stick a FULL JOIN the query fails completely.

 //$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;
}

and the outputed array

  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

You have a quite important logical error in your SQL-query. To simplify it a bit:

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

As I understand Katie doesn't have any certificates. So there are no rows in usercert, so there is nothing to LEFT JOIN on, so her user-information isn't provided.

Change it into

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)

Thus FROM user so that the user-data is always included. And LEFT JOIN usercert with that SPECIFIC certificate (if we put that in the WHERE-clause it still returns no rows).

---

Bonus:

And to make it more efficient (you now execute count($allUsers) * count($allCertificates) queries, which will add up if you have a lot of users):

Change it into:

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

So you have all data in one single query. Verify that the query gives the correct results (phpmyadmin) and parse it in PHP.

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