Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Help needed creating SQL statement for search interface in coldfusion

Was wondering if someone can help me with my SQL statements. I am trying to replicate the search interface that appears on this site http://www.nzbizbuysell.co.nz/
using dreamweavermx and coldfusion mx.

Ive created a form containing three seperate menus and use the followeing sql statement

SELECT *
FROM business
WHERE Region LIKE '#FORM.regionMenu#' OR Business_Type
LIKE '#FORM.business_typeMenu#' OR Asking LIKE '#FORM.askingMenu#'

This works fine until I want to generate a search that combines two of the menus. For example, a wildcard search using one menu (giving 20 results) and another menu (giving 4 results) would give me 20 results rather than 4 results from the 20.

Any suggestions would be appreciated.

Steve  
0
steve257
Asked:
steve257
1 Solution
 
ActiveMediaCommented:
Try this SQL as a start:
(Pls check the Coldfusion bits carefully because that's not my area.)

SELECT * FROM business
WHERE (
(Region LIKE '#FORM.regionMenu#' OR '#FORM.regionMenu#'='Any')

AND
(Business_Type LIKE '#FORM.business_typeMenu#'
OR '#FORM.business_typeMenu#'='Any')

AND
(Asking LIKE '#FORM.askingMenu#'
OR '#FORM.askingMenu#' = 'Any')  
)

Also, you will need to deal with the price aspects more subtly.  The asking price of an item in the database is likely to be a specific figure ( e.g. 30,000 ) but the page visitor will select a price range ( or min/max).

Consequently, you will need to translate the selected price range into a form that SQL can handle.

For example:
minPrice=0
maxPrice=9999999999999
if selectedPriceRange="A" then
   minPrice=0
   maxPrice=10000
end if
if selectedPriceRange="B" then
   minPrice=10000
   maxPrice=50000
end if
if selectedPriceRange="C" then
   minPrice=50000
   maxPrice=100000
end if

...  and so on (NB you will need to use correct language for your system, e.g a CASE statement would be more elegant.)

Then the price range part of the SQL can be something like:

..... WHERE asking BETWEEN minPrice AND maxPrice


BTW, make sure that the asking price is defined in the database table as a currency or numeric value (NOT text) even though the drop down box for price range on your webpage will be in text form.

HTH
0
 
shovavnikCommented:
1. It looks like what you need is an AND query and not an OR query.  As I understand it, you're looking for all records that match ONLY the values selected, but in BOTH (or ALL THREE) fields.  So the query should look more like:

SELECT *
FROM business
WHERE
Region LIKE '#FORM.regionMenu#'
AND Business_Type LIKE '#FORM.business_typeMenu#'
AND Asking LIKE '#FORM.askingMenu#'

2. Another question goes to the implementation.  Is this query in a stored procedure or is it hard-coded in the server-side code?  If it's hard-coded, I strongly recommend you move to a stored procedure, the script for which would look something like this:

CREATE PROCEDURE sp_Search_Query

@region nvarchar(4000) = null,
@business_type nvarchar(4000) = null,
@min_price numeric(9,2) = null,
@max_price numeric(9,2) = null

AS

SELECT *
FROM business
WHERE
(@region is not null and Region LIKE '#' + @region + '#'
AND
(@business_type is not null and Business_Type LIKE '#' + @business_type + '#')
AND
(@min_price is not null and Asking >= @min_price)
AND
(@max_price is not null and Asking <= @max_price)

GO


The advantage of this script is that it does an AND check on all criteria - thus filtering out all the data that doesn't match on all fields you request.  However, it does mean that you need to specify a range (as ActiveMedia suggested) for the Asking price.

This can be further refined - like moving the ranges, regions, and business_types to the database and sending int values to the stored procedure instead of strings (which tend to change).

If you need more help, just ask.

shovavnik
0
 
shovavnikCommented:
oh, btw, you would call this stored procedure thus:

sp_Search_Query N'MidWest', null, 50.25, null

This query will request all results that answer both criteria:
1. Their region is the MidWest.
2. Their cost is at least $50.25.

As you see, this method is rather flexible.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ActiveMediaCommented:
Shovanik's method has some good points but the reason I took a price range code such as "Any", "A", "B", "C", etc is that he said he wanted to have something like:
http://www.nzbizbuysell.co.nz/ 

The "price range" selection list there is something like:
  "Any"
  "$0-$150,000"
  "$150,000-$500,000"
  and so on.

These values have to be translated in some way to numeric min and max, so you may as well use a simple price category code like "A", "B", "C" for communicating with the server even though you would of  course have dollar price ranges (or "Any") showing on screen.

Null values can't normally occur in this situation.  If the visitor doesn't select anything it's interpreted as "Any".

HTH

0
 
steve257Author Commented:
Wow thanks for the feedback and preanswering what was going to be my next question.

Using AND in the query instead of OR solved the problen once I had included a wildcard (i.e, %) on the default labels that appeared initially in the browser.

SELECT *
FROM business
WHERE
Region LIKE '#FORM.regionMenu#'
AND Business_Type LIKE '#FORM.business_typeMenu#'
AND Asking LIKE '#FORM.askingMenu#'

Still need some help on writing the SQL for the price range values though ...

If the menu labels/values are
Any                    %
$0 - $100,000          A
$100,001 - $200,000    B

Should my SQL look something like this

FROM business
WHERE
Region LIKE '#FORM.regionMenu#'
AND Business_Type LIKE '#FORM.business_typeMenu#'
AND Asking LIKE '#FORM.askingMenu#'(BETWEEN 0 AND 100000 = 'A'BETWEEN 100000 AND 100000 = 'B')
etc

Or do I write the statement without the '#FORM.askingMenu#'. If you could give me an example of the competed SQL Id appreciate it as Im way lost on this.

Cheers,
steve

0
 
ActiveMediaCommented:
Steve257

I suggest that you need to do a simple tranlation from the values returned from the list select box on the HTML page (whether these are letter codes or actual dollar price ranges) into separate numeric minPrice and maxPrice values that can be used in the SQL.

Have a look at the previous comment I provided above on 02/22/2003 04:57AM pst.   The price codes do NOT appear in the SQL.  Remember that each record in the database table will have an ACTUAL FIGURE for a price, not a price code.

An alternative would be to have two separate dropdown list boxes in your HTML.  One called minPrice and the other called maxPrice, each of which simply returns a number.  This reduces the amount of translation you need to do and also gives the page visitor greater choice as to the exact range they are choosing.

HTH
0
 
ActiveMediaCommented:

Steve257

If you found the answers given helpful, please could you allocate the points?

0
 
COBOLdinosaurCommented:
This question has been classified abandoned. I will make a recommendation to the
moderators on its resolution in a week or two. I appreciate any comments
that would help me to make a recommendation.

<note>
Unless it is clear to me that the question has been answered I will recommend delete.  It is possible that a Grade less than A will be given if no expert makes a case for an A grade. It is assumed that any participant not responding to this request is no longer interested in its final disposition.
</note>

If the user does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp


Cd&

0
 
shovavnikCommented:
COBOLdinosaur,

It seems that the questioner in fact asked two questions.

Both ActiveMedia and myself answered the first question.  However, based on Steve257's reply, it seems that both answers work for him.  It's only fair therefore to assign the points to the first to answer: ActiveMedia.

As to the second question, only ActiveMedia responded.  So on this count, too, it seems that the points should be alloted to ActiveMedia.

So, as this question seems to have been abandoned by the QUESTIONER, I would recommend allotting the points fairly to ActiveMedia.

shovavnik
0
 
shovavnikCommented:
Oh, and to use the questioner's own words:

"Wow thanks for the feedback and preanswering what was going to be my next question"

The questioner clearly feels that at least the answers given to the initial question (which is the one that counts, isn't it) are satisfactory.

I would think it appropriate to assign an A grade, if only based on the enthusiasm of the questioner's acceptance of the replies.

shovavnik
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now