PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
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 and I'm 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%'");
I'm still going through the queries and code, trying to merge it all together into one query (which I think might be possible, or else two or three).
In the meantime can you confirm a couple things?
- You are only getting a couple grand total numbers, getting amounts for all operators combined and not each one individually
- Are there any variables listed in the code that you need later on, in code you haven't posted? $upgradeEarned and $renewalEarned, obviously, but any others? I'm looking specifically at $total, $PerActv, $Actv*, and $AvgRenewal.
- $months doesn't look like it's used for anything?
- At the end the variables get juggled around a bit. Any particular reason?
A description of how stuff is being calculated would be nice, in case it's easier just to rewrite the queries from that instead of extracting them from the code.
Yes, I am getting grand totals.
These variables are not used later on in the code.
You are correct, $months isn't used for anything. I just saw that, I've been looking at this code for days now lol.
Those calculations at the end are intended to put totals into separate categories for each type of sale.
Yes, I wish there was comments in this code, I just inherited this lol.
Are there any other PaymentMethods besides CREDITCARD and PAPERCHECK?
And you're sure those variables aren't used? I'm cutting out a lot of code based on the fact that only $upgradeEarned and $renewalEarned matter. Like $ActvVehicleAdds, $renewalCount, and $PerActv, are going away.
I apologize, I just overlooked this, but $total, $ActvRenewal, $AvgRenewal, and $PerActv are used in this code, waaaaay further down in the code. I'm sorry.
I keep looking but I still can't see where the problem is. Can you do some debugging? Output the values of a few variables in a few places (like the $Actv* variables after both while loops) to narrow down when they get out of sync?