Populating a web form from a lookup table

Report · Mar 23, 2011

Hi

I am looking for some expert help please

I have a lookup query

select * from employee

where empid = '#form.empid#'

</cfquery>

and what I would like to do is when a user enters their employeeid in a text input field and clicks "find" it triggers the query and brings back that employees details and populates the other form fields on the pages (name, address etc) without a page refresh.

What I can't understand is when they click the "find" button after they have entered an employee ID how does it trigger the query (and where would the query sit within the page) and how would the results get returned to populate the form without a page refresh?

If anybody has some code examples this would be great, I am using CF7

TIA

Report · Mar 23, 2011

Put the query in a separate page, and include that page in a very small iframe on the first page. Use javascript to call the query page and use innerHTML to write back to the form fields.

Report · Mar 23, 2011

You shouldn't use innerHTML to write to form elements. You can use it to create the form elements, although there are better ways.

AJaX would be a good way to do it. Have it access a .cfm file that contains the query and outputs the data and nothing else, preferably in a delimited list format, then you can use JavaScript to parse the string and populate the elements.

^_^

Report · Mar 23, 2011

Thanks but do you have any code examples / walkthroughs that will help me get started,?

I have come across the following example, but how would I adapt this to enable it so when an employeeid is entered and the lookup button is pressed then the rest of the form fields are completed with the data gathered from the record in the db?

</head>

<body>

<div align="center" id="databox">
<select id="idm">
<option value="0" SELECTED>What's for Dinner?...</option>
<option value="1">Monday</option>
<option value="2">Tuesday</option>
<option value="3">Wednesday</option>
<option value="4">Thursday</option>
<option value="5">Friday</option>
<option value="6">Saturday</option>
</select>
<p id="contentdiv"> </p>
</div>

-------------

request_processor.cfm page

<cfset odata = createObject("component","menudata")>
<cfset thedata = odata.getMenuData(FORM.idm)>
<cfset ojson = createObject("component","cfjson")>
<cfset theresults = ojson.encode(thedata)>
<cfoutput>#theresults#</cfoutput>

----------------

menudata.cfc page

<cfcomponent displayname="menudata">
<cffunction name="getMenuData">
<cfargument name="menu_id" required="yes">
<cfquery name="get_menu" datasource="menudb">
   SELECT menu.day,
     menu.main_dish
   FROM menu
   WHERE menu.qsid = #ARGUMENTS.menu_id#
</cfquery>
<cfset pstruct = structNew()>
<cfset pstruct.day = get_menu.day>
<cfset pstruct.main_dish = get_menu.main_dish>
<cfreturn pstruct>
</cffunction>
</cfcomponent>