Solved

Create Query For Total Dynamic Search Form

Posted on 2006-06-14
16
259 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

17 Experts available now in Live!

Get 1:1 Help Now