extracting list from a database

I have a table that holds responses to a questionaire, several of the questions ask respondents to provide several pieces of data seperated by a comma or pipe and ending with a return. recruiting for example the user lists name, activity, date and return. the db entry in the response field appears as such:

Michael Tarantino | Transferred to another agency | 9/28/2009 Tyre Lewis Jr. | Transferred to another agency | 7/16/2008 Joshua Shapiro | Intern - Separated | 8/18/2007

my customer now wants the data displayed in a table as part of a report.
What is the best way to extract this data so that it cam be displayed in a table?

I have tried using listgetat and temp vars, however the return value is causing me fits, the code below works fine if there is only set of data in the field. but with a field value like above it only gives the first set of values and the last temp var would have the date and name in stead of just the date.

<cfloop query="rpt_GetBackground">
		<cfif question_id EQ 1279 and isDefined('response')>
			<cfset temp_tln = ListGetAt(response, 1,',')>
			<cfset temp_tlt = ListGetAt(response, 2,',')>
			<cfset temp_tll = ListGetAt(response, 3,',')>
			<!--- <cfset tem_rtn =ListGetAt(response, 4,'')> --->
			<tr><td>#temp_tln#</td><td>#temp_tlt# </td><td>#temp_tll#<!--- #tem_rtn# ---></td>	</tr>
		<cfelseif question_id EQ 1315 and isDefined('response')>
		<cfset temp_tmn = listGetAt(response, 1,',')>
			<cfset temp_tmt = listGetAt(response, 2,',')>
			<cfset temp_tml = listGetAt(response, 3,',')>
			<!--- <cfset tem_rtn =ListGetAt(response, 4,'')> --->
		<tr><td>#temp_tmn#</td><td>#temp_tmt# </td><td>#temp_tml#<!--- #tem_rtn# ---></td>	</tr>
		
		</cfif>
</cfloop>

Code (markup):

any ideas would be greatly appreciated.


tia

J

 

Can you provide a working code sample? The one above can't be run because it needs that query, and when you change it so that it doesnt, it throws an Invalid Index error. If I could execute the code and see the problem, I could help you.

Off the top, seems like you could use listlen with the delimiter option to detect the pipe formatted answer, then just loop over it using different delimiters. So, if each row was delimited by a return character and each item in the row was delimited with a pipe, you could display it in a table like this:

<cfset response = "Michael Tarantino | Transferred to another agency | 9/28/2009 #chr(13)#Tyre Lewis Jr. | Transferred to another agency | 7/16/2008 #chr(13)#Joshua Shapiro | Intern - Separated | 8/18/2007">

<cfoutput>
<table border="1">
<cfloop list="#response#" index="i" delimiters="#chr(13)#">
<tr>
<cfloop list="#i#" index="j" delimiters="|"><td>#j#</td></cfloop>
</tr>
</cfloop>
</table>
</cfoutput>

 

thanks rob, I had tried the chr(13) but had it coded wrong I had &ch13; this worked beautifully.

thanks again.

J