you - php query function



SQL JOIN問題不會將用戶的詳細信息從查詢中返回並將其打印在數組中 (1)

我有一個角色,需要某些證書才能將用戶分配給該角色。

我試圖從數據庫中建立一個用戶列表,這個用戶列表有一個或多個匹配的特定角色的證書。 (我也只是看著用戶獲得的最後一個證書,並且只比較那個角色)

只要用戶實際上擁有該角色所需的兩個證書,查詢就可以很好地工作。

如果用戶不匹配所有需要的證書,那麼用戶詳細信息的細節就不會回來。

  • 首先,我將向您展示我正在運行的匹配用戶的方法,然後向您展示數組輸出結果

  • 您將看到前兩名用戶(rob,holly)數組0和1匹配角色所需的兩個證書,因此他們的所有信息(如id,圖像和名稱)都會返回到數組的第一部分。

  • 第三個用戶(丹尼爾)數組(2)只匹配一個,所以我沒有得到他的名字或細節在數組的第一部分。

  • 第四個用戶(katie)數組(3)不匹配,所以我在最後返回一個完全空白的數組。

  • 我需要丹尼爾和凱蒂都至少返回他們的名字和idimage所以我可以打印到屏幕上。

我覺得這是與用戶的左連接,但即使我做了一個正確的加入沒有回來。 如果我堅持一個FULL JOIN查詢完全失敗。

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

和成功的數組

  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] => 
            )

    )

 )

你的SQL查詢中有一個非常重要的邏輯錯誤。 為了簡化一下:

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

據我了解,凱蒂沒有任何證書。 所以在usercert中沒有行,所以沒有任何東西可以LEFT JOIN ,所以她的用戶信息沒有提供。

改成它

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)

因此, FROM user這樣的用戶數據總是包括在內。 和LEFT JOIN usercert與該特定證書(如果我們把它放在WHERE它仍然沒有返回行)。

獎金:

為了使它更有效率(現在你執行count($allUsers) * count($allCertificates)查詢,如果你有很多用戶,這些查詢會加起來):

將其更改為:

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

所以你有一個單一的查詢所有的數據。 驗證查詢是否給出了正確的結果(phpmyadmin)並用PHP解析。

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