MySQL Join Queries

Question

I've been given this code to work with, and I know that mysql_* is deprecated, but I'm trying to figure out a way to join all of these queries, because these while loops and queries are hogging resources and killing load time. Any suggestions?

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

Here is my code so far for the joined query, but it's not correct because I am still missing certain bits of information. It is much faster for mysql to handle the mathematics, than for the database to pass the information to php, then have php process it. I'm just not sure as to how to approach this:

$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%'");

Any help is greatly appreciated.

Answer 1

Try this:: You have missed the JOIN Criteria for Table tblRenewalCalls

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%'")