Query to retrieve price

[design in progress]

Hi thanks for reading my message.

I am totally a novice in coldfusion, just started learning couple of weeks
back.
I wanted to do a query but I just can't figure out how to do, would truly
appreciate if you could help...

What I want:
I got a database with a column in currency (price).
I got a search interface that allows user to key in lowest and highest price
and the price will then display.

How should I write the query in the action page to retrieve what has been
keyed in the FORM?
Eg. If one enters 50 as lowest price in the Form, the cfoutput will display
data with price of 50 and above.

Pls help...

Many thanks in advance.
Rgds,
Cold and Confusion soul




Mark this message as the answer.
Print this message
Report this to a Moderator
The ScareCrow
User is offline
View Profile

Senior Member Posts: 3789
Joined: 07/22/2002
05/11/2005 06:39:24 AM
Reply | Quote | Top | Bottom

Assuming both lower and upper price fields are required

Select *
From MyTable
Where price Between <cfqueryparam value="#form.lowerprice#"
cfsqltype="CF_SQL_MONEY"> And <cfqueryparam value="#form.upperprice#"
cfsqltype="CF_SQL_MONEY">


Assuming only the lower price is required

Select *
From MyTable
Where price >= <cfqueryparam value="#form.lowerprice#"
cfsqltype="CF_SQL_MONEY">
<cfif IsDefined("form.upperprice") And Len(Trim(form.upperprice)>
And price <= <cfqueryparam value="#form.upperprice#" cfsqltype="CF_SQL_MONEY">
</cfif>

Assuming niether field is required
Select *
From MyTable
Where 0=0 <!-- zero equals zero --->
<cfif IsDefined("form.lowerprice") And Len(Trim(form.lowerprice)>
And price >= <cfqueryparam value="#form.lowerprice#" cfsqltype="CF_SQL_MONEY">
</cfif>
<cfif IsDefined("form.upperprice") And Len(Trim(form.upperprice)>
And price <= <cfqueryparam value="#form.upperprice#" cfsqltype="CF_SQL_MONEY">
</cfif>

Ken

[design in progress]

Ken,

What I want:
if people keyed in lowest price, it will show lowest price.
if people keyed in highest price, it will highest lowest price.
if people keyed in both price, it will show the price in between lowest and
highest price.

All three conditions should be fulfilled.
Pls help, and thanks for your earlier reply.

Anyone knows, pls help me...really appreciate.

Rgds,
Desperate

[The ScareCrow]

Well, this is interesting. My post has disapeared and I did not get the email
to notify me of your post.

Your post is confusing

Do both price fields have to be entered ?

Can you give an example(s) of what you expect the user to enter and the value
you want to be returned in the query ?

Ken