MySQL Convert Currencies and Sum Group by branch

[Tom Higgins]

Hi,

I am trying to do some conversions on the fly with prices of products in a database.

I have this so far, but it is extremely slow and seems to load forever (I have a lot of data in the base):

<?PHP
	include("currency-convert.php") ;
	include("dbconnectlocal.php") ;
	
	// GET POUNDS...
	$result = mysql_query("SELECT branches.Country, users.userName, users.salesArea, enquiries.assignedToT, partbaskets.*, sum(partbaskets.total), sum(enquiries.theirShippingTotal), jobs.enquiryRef, jobs.isInvoiced
						   FROM branches, users, enquiries, partbaskets, jobs
						   WHERE partbaskets.enquiryRef = enquiries.id
						   AND enquiries.assignedToT = users.userName
						   AND users.salesArea = branches.Country
						   AND jobs.enquiryRef = enquiries.id
						   AND jobs.isInvoiced = '1'
						   AND partbaskets.currency = '£'
						   GROUP BY users.salesArea
						   ORDER BY users.salesArea ASC
						   ") ;
	while($row = mysql_fetch_assoc($result)) {
		$absTotalPounds = $row['sum(partbaskets.total)'] + $row['sum(enquiries.theirShippingTotal)'] ;
		
		$area = $row['salesArea'] ;
		// GET DOLLARS
		$resultd = mysql_query("SELECT branches.Country, users.userName, users.salesArea, enquiries.assignedToT, partbaskets.*, sum(partbaskets.total), sum(enquiries.theirShippingTotal), jobs.enquiryRef, jobs.isInvoiced
						   FROM branches, users, enquiries, partbaskets, jobs
						   WHERE partbaskets.enquiryRef = enquiries.id
						   AND enquiries.assignedToT = users.userName
						   AND users.salesArea = '$area'
						   AND jobs.enquiryRef = enquiries.id
						   AND jobs.isInvoiced = '1'
						   AND partbaskets.currency = '$'
						   GROUP BY users.salesArea
						   ORDER BY users.salesArea ASC
						   ") ;
		while($rowd = mysql_fetch_assoc($resultd)) {
			$absTotalDollars = $rowd['sum(partbaskets.total)'] + $rowd['sum(enquiries.theirShippingTotal)'] ;
				$resultd = mysql_query("SELECT branches.Country, users.userName, users.salesArea, enquiries.assignedToT, partbaskets.*, sum(partbaskets.total), sum(enquiries.theirShippingTotal), jobs.enquiryRef, jobs.isInvoiced
						   FROM branches, users, enquiries, partbaskets, jobs
						   WHERE partbaskets.enquiryRef = enquiries.id
						   AND enquiries.assignedToT = users.userName
						   AND users.salesArea = '$area'
						   AND jobs.enquiryRef = enquiries.id
						   AND jobs.isInvoiced = '1'
						   AND partbaskets.currency = '€'
						   GROUP BY users.salesArea
						   ORDER BY users.salesArea ASC
						   ") ;
		while($rowe = mysql_fetch_assoc($resulte)) {
			$absTotaEuros = $rowe['sum(partbaskets.total)'] + $rowe['sum(enquiries.theirShippingTotal)'] ;
						
			//echo "<br />" . $row['salesArea'] . $absTotalPounds . " " . $absTotalDollars ;	
		}		
				
		}
		
		echo "<br />" . $row['salesArea'] . $absTotalPounds . " " . $absTotalDollars ;	
	}
	
	?>

What I would like is for it to return the final value in pounds against each branch, converted from the dollar and euro rate which is provided in another table. I know there must be a way for this to be more efficient!

I have looked into IFs in mysql but can't get my head around it.

I was thinking something along the lines of "IF partsbaskets.currency = '$' use dollarRate FROM currency.rates sum(partsbaskets.total * dollarRate) AS totalInDollars ELSEIF partsbaskets.currency = '€' etc etc..."



Edited 1 time(s). Last edit at 04/29/2011 08:06AM by Tom Higgins.