Stock on a Query

[shamsm]

I am designing a property web site, using mysql on dreamweaver, I have four tables, one with property details, one with property location, one with property type and another with current exchange rates.

As you can probably see from the query below I have managed to creat a recordset with various fields from these tables, however I am stock on the last part. Now, I would like to display USD aswell as GBP on the results pages, so far I have managed to have GBP calculated and related to its specific record but what I would like to do is also somehow incorporate USD in to this query also, can this be done, if so what function should I use, I am a complete beginner and this is my first site.


SELECT property.*,location.Location,proptype.PType,round(property.Euro*cer.CER) as 'GBP'
FROM property,location,proptype,cer
WHERE property.LocationID=location.LocationID AND property.PTypeID=proptype.PTypeID AND property.StatusID=1 AND cer.ExchangeID=1 AND CategoryID LIKE 'varCat' AND Location LIKE 'varLoc' AND PType LIKE 'varType' AND Euro BETWEEN varPrcmin AND varPrcmax AND Beds>= varBedmin
ORDER BY Euro

Notes: the cer table has two columns ExchangeID which is the Primary Key and CER which is the Current Exchange Rate.

Many thanks in advance.

 

[KarveR]

Your CER table has how many values?

really it should be set with a base level (Euro) and all other echange rates

for example (no idea on exchange rates)
CER Table
--------------
id cur rate
1 euro 1.5
2 USD 1.2
3 GBP 2

etc etc
round(property.Euro*cer.GBP) as 'GBP',round(property.Euro*cer.USD) as 'USD' blah blah

Hope this helps abit more.

//karv

Innit nice learning stuff the hard way ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

actually that was a bit crap and won't work as intended but was very close. If you are doing this with PHP, you can do the math on the GBP/USD conversion from there or call it in the same query and have mysql do the donkey work. This would slow you database considerably and would be better run as a second query.

I'll get my brain into this a little further, now I'm thinking its definatley harder than it seems ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[shamsm]

Thanks for your reply.

I am using ASP, I have looked at running a second query but its kind of difficult relating the prices to the properties(it becomes all jumbled up), as I can't think of a way to incorporate the CER primary key(id) into the property table.
I was also thinking along the lines of what you said and that's how I came up with the present query. the problem with this query is though that although I can relate one of the currencies to the relevant Euro value so it can be multiplied and display the correct price, I can't relate with the other.

Is there a way where within the where clause a value can be related to a field using a unique identifier i.e. cer.ExchangeID=1 for GBP and cer.ExchangeID=2 for USD?

Thanks again for trying to help, it is greatly appreciated.

 

[KarveR]

OK heres that query - I added returns to clarify it but take them out again.

What this tries to accomplish is to get the figures from cer where cur = GBP or USD, and you get Euros from your property table.

Now all you need to do is euros = euros * 1.
USD = euros * 1.2 (example)
GBP = euros * 2 (example)

so your calculation on the page remains fixed and multiplies euros by GBP or USD values according to your cer table.

SELECT property.*,
location.Location,
proptype.PType,
propert.euro,
cer.rate
FROM property,location,proptype,cer
WHERE property.LocationID=location.LocationID
AND property.PTypeID=proptype.PTypeID
AND property.StatusID=1
AND cer.ExchangeID=1
AND CategoryID LIKE 'varCat'
AND Location LIKE 'varLoc'
AND PType LIKE 'varType'
AND Euro BETWEEN varPrcmin AND varPrcmax
AND Beds>= varBedmin
AND cer.cur = ('USD' OR 'GBP')
ORDER BY Euro

hope it works, looks about right but its getting rather late ;-) ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[shamsm]

Thanks for your response, I manage to cracj it eventualy, as you mentioned in one of your posts I finally decided to incorporate an ASP code in the page which will do the mutipliction using the Euro value from the property tableand the CER from the cer table.

Thanks for all your time and effort.

Cheers