PHP MySQL Join Queries

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!

Moderator: General Moderators

Post Reply
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

PHP MySQL Join Queries

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP MySQL Join Queries

Post 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.
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP MySQL Join Queries

Post 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.
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post by Juan1989 »

I'm absolutely positive those variables aren't used. Thank you though!
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post by Juan1989 »

Oh and there are no other PaymentMethods.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP MySQL Join Queries

Post 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.
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP MySQL Join Queries

Post 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;
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post 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.
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP MySQL Join Queries

Post by requinix »

What about $upgradeEarned and $renewalEarned? Surely $AvgRenewal isn't the only value that's changed.
Juan1989
Forum Newbie
Posts: 8
Joined: Tue Nov 11, 2014 11:59 am

Re: PHP MySQL Join Queries

Post by Juan1989 »

I guess you are right, those values indeed do change.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: PHP MySQL Join Queries

Post 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?
Post Reply