Question
Two Related Selects Problem
Hi, I have adopted a version of the two selects related code
that ben forta wrote below: However if there is data in the first
table that does not have a related record in the second table, I
get bind errors. I would also like the second select to show a
blank option at the top of each related option. Does anyone know
how to do this? Code is shown below...
<cfcomponent output="false">
<cfset THIS.dsn="cfartgallery">
<!--- Get array of media types --->
<cffunction name="getMedia" access="remote" returnType="array">
<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT mediaid, mediatype
FROM media
ORDER BY mediatype
</cfquery>
<!--- Convert results to array --->
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result [1]=data.mediaid>
<cfset result [2]=data.mediatype>
</cfloop>
<!--- And return it --->
<cfreturn result>
</cffunction>
<!--- Get art by media type --->
<cffunction name="getArt" access="remote" returnType="array">
<cfargument name="mediaid" type="numeric" required="true">
<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT artid, artname
FROM art
WHERE mediaid = #ARGUMENTS.mediaid#
ORDER BY artname
</cfquery>
<!--- Convert results to array --->
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result [1]=data.artid>
<cfset result [2]=data.artname>
</cfloop>
<!--- And return it --->
<cfreturn result>
</cffunction>
</cfcomponent>
<cfform>
<table>
<tr>
<td>Select Media Type:</td>
<td><cfselect name="mediaid"
bind="cfc:art.getMedia()"
bindonload="true" /></td>
</tr>
<tr>
<td>Select Art:</td>
<td><cfselect name="artid"
bind="cfc:art.getArt({mediaid})" /></td>
</tr>
</table>
</cfform>
<cfcomponent output="false">
<cfset THIS.dsn="cfartgallery">
<!--- Get array of media types --->
<cffunction name="getMedia" access="remote" returnType="array">
<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT mediaid, mediatype
FROM media
ORDER BY mediatype
</cfquery>
<!--- Convert results to array --->
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result [1]=data.mediaid>
<cfset result [2]=data.mediatype>
</cfloop>
<!--- And return it --->
<cfreturn result>
</cffunction>
<!--- Get art by media type --->
<cffunction name="getArt" access="remote" returnType="array">
<cfargument name="mediaid" type="numeric" required="true">
<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT artid, artname
FROM art
WHERE mediaid = #ARGUMENTS.mediaid#
ORDER BY artname
</cfquery>
<!--- Convert results to array --->
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result [1]=data.artid>
<cfset result [2]=data.artname>
</cfloop>
<!--- And return it --->
<cfreturn result>
</cffunction>
</cfcomponent>
<cfform>
<table>
<tr>
<td>Select Media Type:</td>
<td><cfselect name="mediaid"
bind="cfc:art.getMedia()"
bindonload="true" /></td>
</tr>
<tr>
<td>Select Art:</td>
<td><cfselect name="artid"
bind="cfc:art.getArt({mediaid})" /></td>
</tr>
</table>
</cfform>
