php - MySQL 连接查询

Question

我已经得到了这段代码，并且我知道 mysql_* 已被弃用，但我正在尝试找出一种方法来加入所有这些查询，因为这些 while 循环和查询正在占用资源，并且杀死加载时间。有什么建议吗？

$result2         = mysql_query("SELECT * FROM tblOperators WHERE (Team = 'SALES' OR Team = 'RENEWALS' OR Team = 'CSR') AND OperatorLocale='USA' AND OperatorStatus='ACTIVE'");
while ($row2 = mysql_fetch_array($result2)) {
$operID = $row2['OperatorID'];
$result = mysql_query("SELECT * FROM tblUserPayments WHERE OperatorID = '$operID' AND PaymentStatus='OK' AND PaymentDate LIKE '$currentDate%'");

while ($row = mysql_fetch_array($result)) {
    if ($row['PaymentReason'] == 'ACTIVATION') {
        $ActvCount++;
        if ($row['PaymentMethod'] == 'CREDITCARD' || $row['PaymentMethod'] == 'PAPERCHECK') {
            $ActvUpgrade += $row['ChargeAmount'];
        }
    } elseif ($row['PaymentReason'] == 'UPGRADE') {
        $userid      = $row['UserID'];
        $paymentdate = $row['PaymentDate'];
        $result1     = mysql_query("SELECT * FROM tblRenewalInvoices WHERE UserID='$userid' AND ('$paymentdate' >= DATE_SUB(DueDate, INTERVAL 90 DAY) AND '$paymentdate' < DATE_ADD(DueDate, INTERVAL 15 DAY)) AND ParentInvoiceID IS NULL ORDER BY InvoiceNum DESC LIMIT 1");
        if ($row1 = mysql_fetch_array($result1)) {
            $packageid = $row['PackageID'];
            $pack      = mysql_query("SELECT * FROM tblUserPackages WHERE PackageID='$packageid';");
            if ($pack1 = mysql_fetch_array($pack)) {
                $expDate   = $pack1['ExpirationDate'];
                $dueDate   = $row1['DueDate'];
                $days      = mysql_fetch_row(mysql_query("SELECT TO_DAYS('$expDate')-TO_DAYS('$dueDate');"));
                $months    = (int) (((int) $days + 14) / 30.4);
                $years     = (int) (((int) $days + 182) / 365);
                $Intervals = 0;
                if ($years > 0) {
                    $Intervals = $years;
                } if (($pack1['Package'] or 'GPS-SVL') or ($pack1['Package'] == 'GPS-1') or ($pack1['Package'] == 'GPS-1PLUS')) {
                    if ($Intervals > 1) {
                        if ($row['PaymentMethod'] == 'CREDITCARD' || $row['PaymentMethod'] == 'PAPERCHECK') {
                            $renewalCount++;
                            $Actv += $row['ChargeAmount'];
                        }
                    } else {
                        if ($row['PaymentMethod'] == 'CREDITCARD' || $row['PaymentMethod'] == 'PAPERCHECK') {
                            $renewalCount++;
                            $ActvRenewal += $row['ChargeAmount'];
                        }
                    }
                } else {
                    $renewalCount++;
                    $Actv += $row['ChargeAmount'];
                }
            } else {
            }
        } else {
            if ($row['PaymentMethod'] == 'CREDITCARD' || $row['PaymentMethod'] == 'PAPERCHECK')
                $ActvUpgrade += $row['ChargeAmount'];
        }
    } elseif ($row['PaymentReason'] == 'ADDVEHICLE') {
        if ($row['PaymentMethod'] == 'CREDITCARD' || $row['PaymentMethod'] == 'PAPERCHECK')
            $ActvVehicleAdds += $row['ChargeAmount'];
    }

}

$result = mysql_query("SELECT * FROM tblRenewalCalls WHERE OperatorID = '$operID' AND PayStatus='OK' AND DateSubmitted LIKE '$currentDate%'");

while ($row = mysql_fetch_array($result)) {
    if ($row['Charged']) {
        if ((int) $row['RenewYears'] > 1) {
            $renewalCount++;
            $Actv += $row['RenewTotal'];
        } else {
            $renewalCount++;
            $ActvRenewal += $row['RenewTotal'];
        }
    }
}
} if ($ActvCount != 0) {
$PerActv = ($ActvUpgrade + $ActvVehicleAdds) / $ActvCount;
} else {
$PerActv = 0;
}

$total = $Actv + $ActvRenewal + $ActvUpgrade + $ActvVehicleAdds;

// Fix to show proper renewal dollars
$ActvRenewal = $total - ($ActvVehicleAdds + $ActvUpgrade);

$AvgRenewal = ($ActvRenewal) / $renewalCount;

$upgradeEarned = $ActvUpgrade;
$renewalEarned = $ActvRenewal;

这是到目前为止我的连接查询代码，但它不正确，因为我仍然缺少某些信息。 mysql处理数学比数据库将信息传递给php，然后让php处理它要快得多。我只是不确定如何解决这个问题:

$result          = mysql_query(
    "SELECT p.PaymentReason AS PaymentReason, 
                    p.PaymentMethod AS PaymentMethod, 
                    p.ChargeAmount AS ChargeAmount, 
                    p.UserID AS UserID,
                    p.PaymentDate AS PaymentDate,
                    r.PackageID AS PackageID
    FROM tblOperators AS o JOIN tblUserPayments AS p JOIN tblRenewalInvoices 
                      AS r JOIN tblUserPackages AS k JOIN tblRenewalCalls
                      AS c ON o.OperatorID=p.OperatorID 
                      AND r.UserID=p.UserID AND r.PaymentDate=p.PaymentDate
                      AND r.PackageID=k.PackageID
    WHERE (o.Team='SALES' OR o.Team='RENEWALS' OR o.Team='CSR') AND 
            o.OperatorLocale='USA' AND 
            o.OperatorStatus='ACTIVE' AND 
            p.PaymentStatus='OK' AND 
            p.PaymentDate LIKE '$currentDate%'");

非常感谢任何帮助。

Answer 1

试试这个::您错过了表 tblRenewalCalls 的 JOIN 标准

SELECT p.PaymentReason AS PaymentReason, 
                    p.PaymentMethod AS PaymentMethod, 
                    p.ChargeAmount AS ChargeAmount, 
                    p.UserID AS UserID,
                    p.PaymentDate AS PaymentDate,
                    r.PackageID AS PackageID
    FROM tblOperators AS o  
JOIN  tblUserPayments AS p ON o.OperatorID=p.OperatorID
JOIN tblRenewalInvoices AS r ON r.UserID=p.UserID AND r.PaymentDate=p.PaymentDate
JOIN tblUserPackages AS k ON r.PackageID=k.PackageID
JOIN tblRenewalCalls AS c  // JOIN CRITERIA
    WHERE (o.Team='SALES' OR o.Team='RENEWALS' OR o.Team='CSR') AND 
            o.OperatorLocale='USA' AND 
            o.OperatorStatus='ACTIVE' AND 
            p.PaymentStatus='OK' AND 
            p.PaymentDate LIKE '$currentDate%'")