Solved

Create Query For Total Dynamic Search Form

Posted on 2006-06-14
16
276 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
[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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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