Search code examples
coldfusion

Compare mysql query to sql query

I am a lot more at home manipulating arrays and objects in php or javascript, and do next to no work with coldfusion.

I have one query pulling data from mysql. I would like to store results list or array ( or simpler approach)

WHile looping over a second query from msql that generates html output I need to see if one value is contained in prior mysql array or list.

Have tried playing with varierty of cf array/list methods and really not sure what best approach is to resolve following if. CF version is 9

<cfquery datasource="Mysql" name="premium_submitters">
  SELECT submitter_id from table  
</cfquery>
<!--- Need loop to store to array or list??--->

<cfquery datasource="Sql" name="main_data">
    SELECT * from table
</cfquery>

<cfoutput query="main_data">
    <cfif #submiiter_id# <!---is in Mysql array/list return boolean---> >

    </cfif>
</cfoutput>
Solution

  • Use ValueList to get the value of one column of a query into a list.

    <cfquery datasource="Mysql" name="premium_submitters">
  SELECT submitter_id from table  
</cfquery>

<cfset submittersList = ValueList(premium_submitters.submitter_id)>

<cfquery datasource="Sql" name="main_data">
    SELECT * from table
</cfquery>

<cfoutput query="main_data">
    <cfif ListFind(submittersList, main_data.submitter_id) gt 0>
      <!--- is in list, do whatever --->
    </cfif>
</cfoutput>