?
Solved

Help needed creating SQL statement for search interface in coldfusion

Posted on 2003-02-21
11
Medium Priority
?
211 Views
Last Modified: 2013-12-16
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
Comment
Question by:steve257
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 2

Accepted Solution

by:
ActiveMedia earned 200 total points
ID: 7998564
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
 
LVL 8

Expert Comment

by:shovavnik
ID: 8002758
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
 
LVL 8

Expert Comment

by:shovavnik
ID: 8002772
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
WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

 
LVL 2

Expert Comment

by:ActiveMedia
ID: 8003629
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
 

Author Comment

by:steve257
ID: 8006117
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
 
LVL 2

Expert Comment

by:ActiveMedia
ID: 8007398
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
 
LVL 2

Expert Comment

by:ActiveMedia
ID: 8138420

Steve257

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

0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 9305693
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
 
LVL 8

Expert Comment

by:shovavnik
ID: 9306322
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
 
LVL 8

Expert Comment

by:shovavnik
ID: 9306336
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

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question