Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Query For Total Dynamic Search Form

Posted on 2006-06-14
16
Medium Priority
?
286 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
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 

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

Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

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…
What You Need to Know when Searching for a Webhost Provider
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

609 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