MySQL Convert Currencies and Sum Group by branch

[imperium2335]

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..."

[imperium2335]

Please could an admin move this to the mysql forum? Figured I might get more replies there. Thanks.