Page 1 of 1

PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 12:08 pm
by Juan1989
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?

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%'");
Any help is greatly appreciated.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 1:51 pm
by requinix
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.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 2:20 pm
by Juan1989
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.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 3:03 pm
by requinix
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.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 3:22 pm
by Juan1989
I'm absolutely positive those variables aren't used. Thank you though!

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 3:31 pm
by Juan1989
Oh and there are no other PaymentMethods.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 3:40 pm
by requinix
Here's the same code with a lot of stuff removed. Does it work the same way your current code does? If not, what is different?

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') {
			$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)) {
					$ActvRenewal += $row['ChargeAmount'];
				}
			} else {
				$ActvUpgrade += $row['ChargeAmount'];
			}
		}
	}

	$row = mysql_fetch_array(mysql_query("SELECT SUM(RenewTotal) AS SumRenewTotal FROM tblRenewalCalls WHERE OperatorID = '$operID' AND PayStatus='OK' AND DateSubmitted LIKE '$currentDate%' AND Charged"));
	$ActvRenewal += $row['SumRenewTotal'];
}

$upgradeEarned = $ActvUpgrade;
$renewalEarned = $ActvRenewal;
If it's the same then we can continue on to cutting down on the number of queries.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 3:58 pm
by Juan1989
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.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 4:35 pm
by requinix
But not $Actv, right?

Can't cut out as much as before, but still there's some improvement that can be made.

Try this:

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++;
			$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)) {
					$renewalCount++;
					$ActvRenewal += $row['ChargeAmount'];
				}
			} else {
				$ActvUpgrade += $row['ChargeAmount'];
			}
		} elseif($row['PaymentReason'] == 'ADDVEHICLE') {
			$ActvVehicleAdds += $row['ChargeAmount'];
		}
	}

	$row = mysql_fetch_array(mysql_query("SELECT COUNT(1) AS Count, SUM(RenewTotal) AS SumRenewTotal FROM tblRenewalCalls WHERE OperatorID = '$operID' AND PayStatus='OK' AND DateSubmitted LIKE '$currentDate%' AND Charged"));
	$renewalCount += $row['Count'];
	$ActvRenewal += $row['SumRenewTotal'];
}
if($ActvCount != 0) {
	$PerActv = ($ActvUpgrade + $ActvVehicleAdds) / $ActvCount;
} else {
	$PerActv = 0;
}

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

$AvgRenewal = ($ActvRenewal) / $renewalCount;

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

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 5:13 pm
by Juan1989
Okay this code, works, but the value changes for $AvgRenewal, this is the only difference I notice. I appreciate your help with this.

Re: PHP MySQL Join Queries

Posted: Tue Nov 11, 2014 8:42 pm
by Juan1989
I was trying to get it to work properly with $AvgRenewal holding the correct information, but I still can't figure it out.

Re: PHP MySQL Join Queries

Posted: Fri Nov 14, 2014 2:39 pm
by requinix
What about $upgradeEarned and $renewalEarned? Surely $AvgRenewal isn't the only value that's changed.

Re: PHP MySQL Join Queries

Posted: Tue Nov 18, 2014 10:03 am
by Juan1989
I guess you are right, those values indeed do change.

Re: PHP MySQL Join Queries

Posted: Thu Nov 20, 2014 12:36 pm
by requinix
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?