Solved

Create Query For Total Dynamic Search Form

Posted on 2006-06-14
16
251 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

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

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 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now