title: SQL JOIN issue not bringing user details back from query and printing them in array
body: <p>I have a role that requires certain certificates needed before a user can be assigned to that role.</p>
  
  <p>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)</p>
  
  <p>The query works really well as long as a user actually has both of the certificates required for that role.</p>
  
  <p>If the user doesn't match all the certificates required the details of that users details are not coming back.</p>
  
  <ul>
  <li><p>First I will show you the method i'm running to match the users then I will show you the array output result</p></li>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  <li><p>the 4th user (katie) array(3) doesn't match any so I return a completely blank array at the end.</p></li>
  <li><p>I need both Daniel and Katie to at least return their names and idimage so I can print them to the screen.</p></li>
  </ul>
  
  <p>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.</p>
  
  <pre><code> //$allcertificates contains ids of certificates required for role
   public function getStaffByProjectRoleCertificates($allCertificates)
  {
      //get all active users
      $allUsers = $this-&gt;allActiveUser();
      $users = array();
      $temp = 0;
  
      //loop through the users to find matches
      for($i = 0; $i &lt; count($allUsers); $i++)
      {
          $allRows = array();
  
          for($j = 0; $j &lt; 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 &gt; usercert.idusercert)
  ",
              $this-&gt;db-&gt;GetSQLValueString($allUsers[$i], "int"),
              $this-&gt;db-&gt;GetSQLValueString($allCertificates[$j], "int"),
              $this-&gt;db-&gt;GetSQLValueString($allUsers[$i], "int"),
              $this-&gt;db-&gt;GetSQLValueString($allCertificates[$j], "int"));
              $result = $this-&gt;db-&gt;query($this-&gt;db-&gt;link, $query) or die($this-&gt;db-&gt;error($this-&gt;db-&gt;link));
              $numRows = $this-&gt;db-&gt;num_rows($result);
              $row = $this-&gt;db-&gt;fetch_assoc($result);
  
              array_push($allRows, $row);
  
              if($numRows !=0)
              {
                  $temp += 1;
              }
  
              if($j == count($allCertificates) -1)
              {
                  array_push($users, array("iduser" =&gt; $row['iduser'], 'idimage'=&gt;$row['idimage'], 'forename'=&gt;$row['userForename'], 'surname'=&gt;$row['userSurname'], "cert_no" =&gt; $temp, "data"=&gt;$allRows));
                  $temp =0;
              }
           }
      }       
       function sortByOrder($a, $b)
      {
          return $b['cert_no'] - $a['cert_no'];
      }
      usort($users, 'sortByOrder');
      print_r($users);
      return $users;
  }
  </code></pre>
  
  <p>and the outputed array</p>
  
  <pre><code>  Array
    (
  [0] =&gt; Array
      (
          [iduser] =&gt; 90
          [idimage] =&gt; 
          [forename] =&gt; Holly
          [surname] =&gt; Bain
          [cert_no] =&gt; 2
          [data] =&gt; Array
              (
                  [0] =&gt; Array
                      (
                          [certName] =&gt; Full UK Driving License
                          [idusercert] =&gt; 21
                          [usercertEnd] =&gt; 2016-05-31
                          [iduser] =&gt; 90
                          [idimage] =&gt; 
                          [userForename] =&gt; Holly
                          [userSurname] =&gt; Bain
                          [certStatusName] =&gt; Expiring In 2 Months
                      )
  
                  [1] =&gt; Array
                      (
                          [certName] =&gt; test certificate
                          [idusercert] =&gt; 22
                          [usercertEnd] =&gt; 2016-05-31
                          [iduser] =&gt; 90
                          [idimage] =&gt; 
                          [userForename] =&gt; Holly
                          [userSurname] =&gt; Bain
                          [certStatusName] =&gt; Expiring In 2 Months
                      )
  
              )
  
      )
  
  [1] =&gt; Array
      (
          [iduser] =&gt; 88
          [idimage] =&gt; 197
          [forename] =&gt; Robert
          [surname] =&gt; Bain
          [cert_no] =&gt; 2
          [data] =&gt; Array
              (
                  [0] =&gt; Array
                      (
                          [certName] =&gt; Full UK Driving License
                          [idusercert] =&gt; 24
                          [usercertEnd] =&gt; 2017-07-01
                          [iduser] =&gt; 88
                          [idimage] =&gt; 197
                          [userForename] =&gt; Robert
                          [userSurname] =&gt; Bain
                          [certStatusName] =&gt; Expiring In 2 Months
                      )
  
                  [1] =&gt; Array
                      (
                          [certName] =&gt; test certificate
                          [idusercert] =&gt; 19
                          [usercertEnd] =&gt; 2016-05-31
                          [iduser] =&gt; 88
                          [idimage] =&gt; 197
                          [userForename] =&gt; Robert
                          [userSurname] =&gt; Bain
                          [certStatusName] =&gt; Expiring In 2 Months
                      )
  
              )
  
      )
  
  [2] =&gt; Array
      (
          [iduser] =&gt; 
          [idimage] =&gt; 
          [forename] =&gt; 
          [surname] =&gt; 
          [cert_no] =&gt; 1
          [data] =&gt; Array
              (
                  [0] =&gt; Array
                      (
                          [certName] =&gt; Full UK Driving License
                          [idusercert] =&gt; 20
                          [usercertEnd] =&gt; 2016-05-31
                          [iduser] =&gt; 86
                          [idimage] =&gt; 196
                          [userForename] =&gt; Daniel
                          [userSurname] =&gt; Robinson
                          [certStatusName] =&gt; Expiring In 2 Months
                      )
  
                  [1] =&gt; 
              )
  
      )
  
  [3] =&gt; Array
      (
          [iduser] =&gt; 
          [idimage] =&gt; 
          [forename] =&gt; 
          [surname] =&gt; 
          [cert_no] =&gt; 0
          [data] =&gt; Array
              (
                  [0] =&gt; 
                  [1] =&gt; 
              )
  
      )
  
   )
  </code></pre>