MySQL Queries

[Juan1989]

I'm trying to figure out how to combine these queries, to decrease server load and load time, but I'm not so good with MySQL queries. Does anyone have any ideas, any help is greatly appreciated, I've been tinkering with this for days now.

$Actv = 0;
$ActvCount = -1;
$ActvUpgrade = 0;
$ActvRenewal = 0;
$ActvVehicleAdds = 0;

$result2 = mysql_query("SELECT * FROM tblOperators WHERE OperatorLocale = 'USA' and OperatorStatus = 'ACTIVE' and Team = 'RENEWALS'");
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'){
			$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'){
							$Actv += $row['ChargeAmount'];
						//}
					}
					else{
						//if($row['PaymentMethod'] == 'CREDITCARD'){
							$ActvRenewal += $row['ChargeAmount'];
						//}
					}
				}
				else{
					$Actv += $row['ChargeAmount'];
				}
			}
			else{
			}
		}
		else{
			//if($row['PaymentMethod'] == 'CREDITCARD')
				$ActvUpgrade += $row['ChargeAmount'];
		}
	}

	elseif($row['PaymentReason'] == 'ADDVEHICLE'){
		//if($row['PaymentMethod'] == 'CREDITCARD')
			$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) {
			$Actv += $row['RenewTotal'];
		}
		else{
			$ActvRenewal += $row['RenewTotal'];
		}
	 }
  }
 }
$total = $Actv+$ActvRenewal+$ActvUpgrade+$ActvVehicleAdds;

$ActvRenewal = $total - ($ActvVehicleAdds + $ActvUpgrade);

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

[Barand]

Here's an example of how your first two queries would be combined.

SELECT * 
FROM tblOperators o
    LEFT JOIN tblUserPayments p
        ON p.OperatorID = 0.OperatorID 
            AND PaymentStatus='OK' 
            AND PaymentDate LIKE '$currentDate%'
WHERE OperatorLocale = 'USA' 
    and OperatorStatus = 'ACTIVE' 
    and Team = 'RENEWALS'

Do not use SELECT * in your queries, specify the columns you need.

 

You could combine the others too, probably, but that would mean my reverse engineering your table structures and all I know is that all your tables contain " * ".

[Psycho]

Yeah, I started to combine the queries and saw that you could probably combine most of them to also include the conditional calculations you are making, such as for $ActvUpgrade. But, since you have absolutely no comments to even help someone understand what you are doing I decided it wasn't worth my time. The better the information you provide the more likely we are to invest our time to help.

[Juan1989]

Yes, I'm sorry about that. I was actually just thrown into this. This code was written long before I took over, so I'm having trouble thoroughly understanding what I'm doing as well. I apologize for the lack of comments. MySQL is definitely not something I'm proficient at.