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

x
?
Solved

Create Query For Total Dynamic Search Form

Posted on 2006-06-14
16
Medium Priority
?
288 Views
Last Modified: 2013-12-24
I am using CFMX7 to query a Access database and I want my search form to be totally dynamic.  I have created the following tables in my database.

MLS (Contains all the MLS information)
tblAreaID (assigns areaID values to areas)
tblCity (assigns cityID value to city)
tblState (assign stateID to states)
tblZip (assigns zipID to zipcodes)

How would I create a query in Coldfusion that could read the following code?

<form action="results.cfm" method="post">

<!---Search mls number--->
MLS:
<td>
  <p>
    <input type="text" name="mlsid" size="10" maxlength="5">
</p>
 
<!---Select City drop down list using city table that contains just cities--->

<tr>
<td>
 <select name="city" size="1">
      <option  value="" selected>--SELECT A CITY--</option>
 <option  value="">--select a city--</option>
                      <cfoutput query="city">
                                <option value="#cityID#">#city#</option>

<!--- Select a state --->
td> What state:</td>
                  <td><select name="State" size="1">
                      <option  value="">--select a state--</option>
                      <cfoutput query="state">
                                <option value="#StateID#">#state#</option>
                                </cfoutput>
                    </select>          
                        <!---Select zip--->

<td> What zip:</td>
                  <td><select name="zip" size="1">
                      <option  value=""></option>
                      <cfoutput query="zip">
                                <option value="#zipID#">#zip#</option>
                                </cfoutput>
                    </select>  

<td> What area:</td>
                  <td><select name="areaID" size="1">
                      <option  value="">--select a area--</option>
                      <cfoutput query="area">
                                <option value="#area#">#area#</option>
                                </cfoutput>
                    </select>  

  Also all the ID's are assigned in single increments (except MLS where the MLS number is the primary key.)

For example
zipID        zip
   1         12345
    2        11111

And so forth (I don't believe this would cause problems in the search and search_results pages but I am not exactly sure)

How do I create a <cfquery> to read the individual tables and assign the ID values to equal the values in the mls table?
0
Comment
Question by:rudodoo
  • 9
  • 6
16 Comments
 
LVL 7

Expert Comment

by:aseusainc
ID: 16910712
Does an MLS record have columns for tblAreaID, tblCity, tblState, tblZip that have a relation to each table?
0
 

Author Comment

by:rudodoo
ID: 16911183
Yes, the Area, City, State, Zip
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16911356
Next question.  Are you validating the form?  IE: does the user HAVE to select an option for each drop down or can they leave one unchanged?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

Author Comment

by:rudodoo
ID: 16911531
no but I would like to do it like that
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16911783
You'd want to do something like this for your query:

SELECT      *
FROM      (((MLS INNER JOIN tblAreaID ON MLS.areaID = tblAreaID.areaID)
       INNER JOIN tblZip ON MLS.zipID = tblZip.zipID)
       INNER JOIN tblState ON MLS.stateID = tblState.stateID)
       INNER JOIN tblCity ON MLS.cityID = tblCity.cityid
WHERE      1=1
<cfif #mlsid# NEQ "">AND mlsid = #mlsid#</cfif>
<cfif #areaid# NEQ "">AND areaid = #areaid#</cfif>
<cfif #zip# NEQ "">AND zipid = #zip#</cfif>
<cfif #state# NEQ "">AND stateid = #state#</cfif>
<cfif #city# NEQ "">AND cityid = #city#</cfif>
0
 

Author Comment

by:rudodoo
ID: 16912015
ok well I will try it later and see what happens I don't have access to it right now
0
 

Author Comment

by:rudodoo
ID: 16916929
Shoud I put this query on the search page or the results page?
0
 

Author Comment

by:rudodoo
ID: 16916944
Because that code looks like it belongs on the results page,  and I already have a code that I use to display the results
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16916995
I suppose is misread what you were trying to do.  Please elaborate.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16917403
just do your mls query
select whatever
from mls
where whatever
use what aseusainc said if that's correct
<cfoutput query="mslqueryname">
then for your select boxes here is an ex:
<select name="areaID">
<option  value="">--select a area--
<cfloop query="areaquery"> areaquery would be a separate query that selects all areas from area table
<option value="#areaID#" <cfif areaquery.areaID is mls.areaID>selected</cfif>>#area#
</cfloop>
</select>  
</cfoutput>

hope this is what you refer to ...
0
 
LVL 7

Accepted Solution

by:
aseusainc earned 1500 total points
ID: 16921931
Is this what you wanted?

They'd all be the same theory, just use this as an example:

For City:
<cfquery name="city">
SELECT        *
FROM           tblCity
ORDER BY     city ASC
</cfquery>

<select name="city" size="1">
  <option  value="" selected>--SELECT A CITY--</option>
  <option  value="">--select a city--</option>
<cfoutput query="city">
<option value="#cityID#">#city#</option>
</cfoutput>
0
 

Author Comment

by:rudodoo
ID: 16965344
Sorry, I have been real busy with work and this is a personal project.  How will the the form be read on the results page?  Can I use the same code that worked before?  B/C my original code is reading off of one table, where the code that I am using in this example is reading off of several different tables.  Also, should I use a cfform or regular HTML form?
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16965381
Just use regular HTML forms, use my example above and adjust where needed to repeat the same code for each field.
0
 

Author Comment

by:rudodoo
ID: 16965416
Ok, but shouldn't I have a datasource
0
 

Author Comment

by:rudodoo
ID: 16965455
Nevermind I seem to have it. let me do some more testing
0
 

Author Comment

by:rudodoo
ID: 16965473
Ok, it doesn't seem to be reading the query on the results page, should I use the following code that aseusainic suggested earlier

SELECT     *
FROM     (((MLS INNER JOIN tblAreaID ON MLS.areaID = tblAreaID.areaID)
      INNER JOIN tblZip ON MLS.zipID = tblZip.zipID)
      INNER JOIN tblState ON MLS.stateID = tblState.stateID)
      INNER JOIN tblCity ON MLS.cityID = tblCity.cityid
WHERE     1=1
<cfif #mlsid# NEQ "">AND mlsid = #mlsid#</cfif>
<cfif #areaid# NEQ "">AND areaid = #areaid#</cfif>
<cfif #zip# NEQ "">AND zipid = #zip#</cfif>
<cfif #state# NEQ "">AND stateid = #state#</cfif>
<cfif #city# NEQ "">AND cityid = #city#</cfif>
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

571 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