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.

it's not correct .... in what way? are you getting any errors? are you not getting the desired reuslt? if results are differing, then how? — itachi
– itachi, Commented Nov 11, 2014 at 16:37
While you're in there you probably want to add some SQL escaping to avoid those dangerous injection bugs. Hopefully you can update this to a more modern database layer. PHP 5.5 deprecates that method and it will probably be removed completely in newer versions, leaving this code non-functional. — tadman
– tadman, Commented Nov 11, 2014 at 16:38
I just updated the code, please refresh page. I know the mysql_* functions are deprecated and will be removed, this code was given to me, and at the moment, I 'm just trying to join the queries so that it is only selecting what I need from the tables. The code that I have made is not correct because I am not getting the desired results that I need. — Juan
– Juan, Commented Nov 11, 2014 at 16:40

Sashi Kant · Accepted Answer · 2014-11-11 16:40:58Z

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

answered Nov 11, 2014 at 16:40

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juan Over a year ago

The only problem is, I'm still needing to pull extra information from the tables which is used further down in the code. I know it is much better and faster to let mysql handle this than php, but I just can't seem to figure this one out.

Collectives™ on Stack Overflow

MySQL Join Queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related