php - 重复循环 - PHP/MySQL

Question

基本上我遇到了一些我似乎无法弄清楚的问题。我正在运行一个循环来检查数据库中的特定值并将它们显示在表中。除了存在重复值之外，一切工作正常，表格显示正常？

名为 IR_Logs 的主表仅包含 2 行，用作以下代码的连接表。

$SQL     = "SELECT ID, ItemID FROM IR_Logs";
$data    = $db->getResults($SQL);

$count   = mysql_num_rows($data);
echo '<p>Returned Results: '.$count.'</p>';

while($row = mysql_fetch_array($data)){
$ItemID     = $row[1];
$LocationID = $db->getValue("SELECT LocationID FROM IR_Logs WHERE ItemID = $ItemID");

$SQL          = "SELECT SiteManager, CompanyID, Reference FROM Customer_Sites WHERE Customer_Sites.ID = $LocationID";
$LocationData = $db->getResults($SQL);

while($row2= mysql_fetch_array($LocationData)){
    $SiteManagerID = $row2[0];
    $CompanyID     = $row2[1];
    $SiteReference     = $row2[2];

    $SQL         = "SELECT Name, Telephone, Email FROM Customer_Users WHERE CompanyID = $CompanyID AND ID = $SiteManagerID";
    $ManagerData = $db->getResults($SQL);

    while($row3 = mysql_fetch_array($ManagerData))
    {
        $Manager_Name      = $row3[0];
        $Manager_Telephone = $row3[1];
        $Manager_Email     = $row3[2];

        $SQL               = "SELECT (SELECT Reference FROM Inventory WHERE ID = ItemID), (SELECT Company FROM Customer_Accounts WHERE ID = CompanyID), (SELECT Reference FROM Customer_Sites WHERE ID = LocationID), Type, DATE_FORMAT(Date, '%d %M %Y'), CompanyID, LocationID FROM IR_Logs WHERE LocationID = $LocationID ORDER BY CompanyID ASC";
        $LogData           = $db->getResults($SQL);

        if(mysql_num_rows($LogData) <> 0){
            $HQEmail = $db->getValue("SELECT PrimaryEmail FROM Customer_Accounts WHERE ID = $CompanyID");
            $message .= '<h3>Site Ref: '.$SiteReference.'</h3>';
            $message .= '<p>For the Attention of '.$Manager_Name.', in regards to Site Reference: '.$SiteReference.'</p>';
            $message .= $blk->GetBlock(6);

            $message .= '<table style="border:1px solid black; padding: 10px;" cellpadding="10">';
            $message .= '<tr>';
            $message .= '<td><strong>Reference</strong></td>';
            $message .= '<td><strong>Type</strong></td>';
            $message .= '<td><strong>Date</strong></td>';
            $message .= '</tr>';

            while($row4 = mysql_fetch_array($LogData)){
                $Reference = $row4[0];
                $Location  = $row4[2];
                $Company   = $row4[1];
                $Type      = $row4[3];
                $Date      = $row4[4];
                $CompanyID = $row4[5];
                $LocationID= $row4[6];

                $message .= '<tr>';
                $message .= '<td>'.$Reference.'</td>';
                $message .= '<td>'.$Type.'</td>';
                $message .= '<td>'.$Date.'</td>';
                $message .= '</tr>';
            }
            $message .= '</table>';
        }


    }
}

}

Answer 1

使用联接会更容易理解，并且担心您没有说出哪个字段给出了重复项。

例如，以下是对您的代码的快速破解(未经测试)，以消除一些单独的选择:-

<?php

$SQL     = "SELECT ID, ItemID FROM IR_Logs";
$data    = $db->getResults($SQL);

$count   = mysql_num_rows($data);
echo '<p>Returned Results: '.$count.'</p>';

while($row = mysql_fetch_assoc($data))
{
    $ItemID     = $row['ItemID'];
    $SQL          = "SELECT cs.SiteManager, cs.CompanyID, cs.Reference, cu.Name, cu.Telephone, cu.Email
                FROM IR_Logs ir 
                INNER JOIN Customer_Sites cs ON ir.LocationID = cs.ID
                INNER JOIN Customer_Users cu ON cs.CompanyID = cu.CompanyID AND cu.ID = cs.SiteManager";
    $LocationData = $db->getResults($SQL);

    while($row2= mysql_fetch_assoc($LocationData))
    {
        $SiteManagerID = $row2['SiteManager'];
        $CompanyID     = $row2['CompanyID'];
        $SiteReference     = $row2['Reference'];
        $Manager_Name      = $row3['Name'];
        $Manager_Telephone = $row3['Telephone'];
        $Manager_Email     = $row3['Email'];

        $SQL               = "SELECT i.Reference, ca.Company, cs.Reference, a.Type, DATE_FORMAT(a.Date, '%d %M %Y'), a.CompanyID, a.LocationID 
                                FROM IR_Logs a
                                LEFT OUTER JOIN Inventory i ON a.ItemID = i.ID
                                LEFT OUTER JOIN Customer_Accounts ca ON a.CompanyID = ca.ID
                                LEFT OUTER JOIN Customer_Sites cs ON a.LocationID = cs.ID
                                WHERE a.LocationID = $LocationID 
                                ORDER BY CompanyID ASC";
        $LogData           = $db->getResults($SQL);

        if(mysql_num_rows($LogData) <> 0){
            $HQEmail = $db->getValue("SELECT PrimaryEmail FROM Customer_Accounts WHERE ID = $CompanyID");
            $message .= '<h3>Site Ref: '.$SiteReference.'</h3>';
            $message .= '<p>For the Attention of '.$Manager_Name.', in regards to Site Reference: '.$SiteReference.'</p>';
            $message .= $blk->GetBlock(6);

            $message .= '<table style="border:1px solid black; padding: 10px;" cellpadding="10">';
            $message .= '<tr>';
            $message .= '<td><strong>Reference</strong></td>';
            $message .= '<td><strong>Type</strong></td>';
            $message .= '<td><strong>Date</strong></td>';
            $message .= '</tr>';

            while($row4 = mysql_fetch_array($LogData)){
                $Reference = $row4[0];
                $Location  = $row4[2];
                $Company   = $row4[1];
                $Type      = $row4[3];
                $Date      = $row4[4];
                $CompanyID = $row4[5];
                $LocationID= $row4[6];

                $message .= '<tr>';
                $message .= '<td>'.$Reference.'</td>';
                $message .= '<td>'.$Type.'</td>';
                $message .= '<td>'.$Date.'</td>';
                $message .= '</tr>';
            }
            $message .= '</table>';
        }
    }
}

?>