Answered
CFFUNCTION implementation
For a project I am currently working on, I inherited a MySQL
database which is full of fields of the data type ENUM or SET. This
is not how I have normally structured my dbs, so I was struggling
with a way to extract the ENUM/SET values in order to present them
as SELECT lists in cfforms.
I managed to successfully complete this task, and in an effort to reuse the code across the entire application, have been working on converting the ENUM extraction into a cffunction (code below):
<cffunction name="enumSelect" returnType="query">
<cfargument name="table" type="string" required="true">
<cfargument name="column" type="string" required="true">
<cfquery name = "getEnumValues" dataSource = "#myDB#">
SHOW COLUMNS FROM #table# LIKE '#column#'
</cfquery>
<cfset temp = ValueList(getEnumValues.Type)>
<cfset temp = ReplaceList(#temp#, "enum(,),'", "")>
<cfset queryArray = listToArray(temp)>
<cfset myEnumValues = queryNew("selectOption")>
<cfloop from="1" to="#ArrayLen(queryArray)#" index="x">
<cfset queryAddRow(myEnumValues)>
<cfset querySetCell(myEnumValues, "selectOption", queryArray)>
</cfloop>
<cfreturn myEnumValues>
</cffunction>
Now, my question is, how do I implement this in a real world cfm application? I know the function is working, because if I change the following lines:
<cffunction name="enumSelect" returnType="string"> <!--- changed returnType from "query" to "string" --->
and
<cfreturn myEnumValues.selectOption[2]><!--- added .selectOption[2] --->
and then call the function:
<cfoutput>#enumSelect("table_name","column_name")#</cfoutput>
I successfully print to screen the 2nd option in the ENUM data field. Obviously, if I change the [2] to a [3] I get the third option, etc.
However, I need to implement this function in a CFSELECT. I just don't have an idea how to call it. When I change the function return type back to query, and eliminate the .selectOption[ ] from the cfreturn, and try:
<cfoutput query=#enumSelect("tbgmax_organization_details","Have_Own_Newsletter")#>#selectOption#</cfoutput>
I get the COMPLEX OBJECT TYPES CANNOT BE CONVERTED TO SIMPLE VALUES error.
What am I missing?
I managed to successfully complete this task, and in an effort to reuse the code across the entire application, have been working on converting the ENUM extraction into a cffunction (code below):
<cffunction name="enumSelect" returnType="query">
<cfargument name="table" type="string" required="true">
<cfargument name="column" type="string" required="true">
<cfquery name = "getEnumValues" dataSource = "#myDB#">
SHOW COLUMNS FROM #table# LIKE '#column#'
</cfquery>
<cfset temp = ValueList(getEnumValues.Type)>
<cfset temp = ReplaceList(#temp#, "enum(,),'", "")>
<cfset queryArray = listToArray(temp)>
<cfset myEnumValues = queryNew("selectOption")>
<cfloop from="1" to="#ArrayLen(queryArray)#" index="x">
<cfset queryAddRow(myEnumValues)>
<cfset querySetCell(myEnumValues, "selectOption", queryArray
</cfloop>
<cfreturn myEnumValues>
</cffunction>
Now, my question is, how do I implement this in a real world cfm application? I know the function is working, because if I change the following lines:
<cffunction name="enumSelect" returnType="string"> <!--- changed returnType from "query" to "string" --->
and
<cfreturn myEnumValues.selectOption[2]><!--- added .selectOption[2] --->
and then call the function:
<cfoutput>#enumSelect("table_name","column_name")#</cfoutput>
I successfully print to screen the 2nd option in the ENUM data field. Obviously, if I change the [2] to a [3] I get the third option, etc.
However, I need to implement this function in a CFSELECT. I just don't have an idea how to call it. When I change the function return type back to query, and eliminate the .selectOption[ ] from the cfreturn, and try:
<cfoutput query=#enumSelect("tbgmax_organization_details","Have_Own_Newsletter")#>#selectOption#</cfoutput>
I get the COMPLEX OBJECT TYPES CANNOT BE CONVERTED TO SIMPLE VALUES error.
What am I missing?
AdChoices