PHP MySQL Join Queries
Posted: Tue Nov 11, 2014 12:08 pm
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?
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:
Any help is greatly appreciated.
Code: Select all
$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:
Code: Select all
$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%'");