Skip to main content
Known Participant
October 23, 2016
Question

Where is the Coldfusion forum?

  • October 23, 2016
  • 1 reply
  • 1291 views

I've not been posting to the Coldfusion forum in months.  I cannot find how to ask a question in the CFML department.  Please help.

This topic has been closed for replies.

1 reply

BKBK
Community Expert
October 23, 2016

Your current post is in the Coldfusion forum. So, ask your next question as you did this one.

EwokStudAuthor
Known Participant
October 25, 2016

I am trying to achieve having a list of customers displayed in the bottom portion of my webpage, filtered according to the cityID, located at the top of my page.  I understand the group method, but can I link two separate query resultsets, according to the name of the two CFTABLES or the linkage of two associated number fields (the other method)?

ie. CFTABLE QUERY=qry_cities    I'd like to use a dropdown to change the city, and the have the second table refresh.

CFTABLE QUERY=qry_customers

Dave Ferguson
Participating Frequently
October 26, 2016

As WolfShade says, cftable has been deprecated. Nevertheless, I can think of a very easy way to implement what you want. The demo is as follows.

1) Create the following database table, with columns cityID(varchar 10), custID(varchar 10) and custName(varchar 30). Call it customer:

2) Store the following 3 files within the same directory under the web root:

cities.cfm

<!--- Code at top of page --->

<cfif isdefined("form.city")>

    <cfset cityCustomerObject = createobject("component","CityCustomer")>

    <cfset customerQuery = cityCustomerObject.getCustomers(form.city)>

</cfif>

<cfoutput><form action="#cgi.script_name#" method="post"></cfoutput>

<select name="city" id="city" onchange="this.form.submit()">

    <option value="">Select city</option>

    <option value="ct1">city 1</option>

    <option value="ct2">city 2</option>

    <option value="ct3">city 3</option>

</select>

</form>

<!--- Code at bottom of page --->

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

<select name="customer" id="customer">

    <option value="">Select customer</option>

    <cfif isdefined("form.city")>

        <cfoutput query="customerQuery">

             <option value="#custID#">#custname#</option>

        </cfoutput>

    </cfif>  

</select><br>

<input type="submit" name="sbmt" value="Send">

</form>

CityCustomer.cfc

<cfcomponent>

    <cffunction name="getCustomers" returntype="query">

        <cfargument name="cityID" >

        <cfset var customers = queryNew("")>

         <!--- dsn = name of datasource in which customer table stored. dsn should be registered in the administrator--->

        <cfquery name="customers" datasource="dsn">

            select custID, custName

            from customer

            where cityID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.cityID#">

        </cfquery>

        <cfreturn customers>

    </cffunction>

</cfcomponent>

actionPage.cfm

Selected customer ID: <cfif isdefined("form.customer")><cfoutput>#form.customer#</cfoutput></cfif>

Get the ball rolling by opening the page cities.cfm in the browser. The rest follows from there.


Couple of points here..

Use structkeyexists instead of isDefined.  It is a lot more accurate and is less likely to throw false responses.

structKeyExists(form, 'city')

Form submits to an onchange on a select is not really a good idea.  If the user makes a mistake the form is submitted and they have to go back.  It is better to put a go button or icon next to the select to submit it.

I wouldn't do this below.  It creates excess overhead because cf has to create an empty query object that it will then just replace.

<cfset var customers = queryNew("")>

You can easily just do this instead and get the same result

<cfset var customers = ''>

Overall, for a better user experience, I would do the customer loading via ajax.  Just doesn't make sense to reload the whole page just because a select was changed.