cfloop and cfquery

[Cappi]

I have 2 tables.&nbsp;&nbsp;The first table (Drug) contains the following columns; Index, Name, Type.&nbsp;&nbsp;The second table (dreeval) contains CNS_Depr_list, CNS_Stim_list, Hallucinogens_list, PCP_list, NA_list, Inhalants_list, and Cannabis_list.&nbsp;&nbsp;The values in the fields above can contain one drug name or multiple drug names in a comma delimited list. (ie. Amphetamine,Cocaine,Methamphetamine). <br>I query the drug table for a list of all drugs, the I use those drug names to check the columns in the dreeval table for matches using the following code:<br><br>&lt;cfquery name=&quot;GetDepr&quot; datasource=&quot;dre_new&quot; dbtype=&quot;ODBC&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select *<br> from drug<br> Order by Type<br>&lt;/cfquery&gt;<br><br>&lt;cfloop query=&quot;GetDepr&quot;&gt;<br>&lt;cfset drugname = '#GetDepr.Name#'&gt;<br>&lt;cfquery name=&quot;GetDrugNum&quot; datasource=&quot;dre_new&quot; dbtype=&quot;ODBC&quot;&gt;<br> Select conf_CNS_Depr_list<br> from dreeval<br> where conf_CNS_Depr_list = '#Variables.drugname#'<br>&lt;/cfquery&gt;<br>&lt;/cfloop&gt;<br><br>&lt;table&gt;<br>&lt;cfoutput query=&quot;GetDepr&quot; group=&quot;Type&quot;&gt;<br>&lt;tr&gt;&lt;td colspan=&quot;2&quot;&gt;&lt;b&gt;&lt;div align=&quot;center&quot;&gt;#GetDepr.Type#&lt;/div&gt;&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;<br>&lt;cfoutput&gt;&lt;tr&gt;&lt;td&gt;#GetDepr.Name#td&gt;&lt;td&gt;#GetDrugNum.RecordCount#&lt;/td&gt;&lt;/tr&gt; &lt;/cfoutput&gt;&lt;/cfoutput&gt;<br>&lt;/table&gt;<br><br>I am doing somthing wrong in the output because it is only outputing the RecordCount for the first variable.drugname.&nbsp;&nbsp;What am I doing wrong here?<br>TIA,<br>Kim

 

[Sherylj]

Is this Kim in Texas?&nbsp;&nbsp;You're the only one I know who's username would be Cappi.&nbsp;&nbsp;;-)<br><br>What about doing an index?&nbsp;&nbsp;Are you trying to get a count or a list of all matches?&nbsp;&nbsp;<br><br>&lt;CFLOOP INDEX=&quot;LoopCount&quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>FROM=&quot;1&quot; TO=&quot;#Len(whatever)#&quot;&gt;<br>&lt;/CFLOOP&gt;<br><br>Sherylj&nbsp;&nbsp;

 

[Cappi]

Hey Sheryl,<br>Yes, it is me!&nbsp;&nbsp;I am trying to get a count but the only result I am getting is the last record.&nbsp;&nbsp;I had said in the previous post that it was the first record but it is actually the last record.&nbsp;&nbsp;I will try the cfloop index.<br><br>Thanks,<br>Kim

 

[Sherylj]

I knew it was you!&nbsp;&nbsp;Good luck!<br>Sherylj

 

[Guest_imported]

Try This!<br><br>&lt;cfquery name=&quot;GetDepr&quot; datasource=&quot;dre_new&quot; dbtype=&quot;ODBC&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select *<br>from drug<br>Order by Type<br>&lt;/cfquery&gt;<br><br>&lt;table&gt;<br><br>&lt;cfloop query=&quot;GetDepr&quot;&gt;<br>&lt;tr&gt;<br>&lt;td colspan=&quot;2&quot;&gt;&lt;b&gt;<br>&lt;cfoutput&gt;<br>&lt;div align=&quot;center&quot;&gt;#GetDepr.Type#&lt;/div&gt;&lt;/b&gt;&lt;/td&gt;<br>&lt;/tr&gt;<br>&lt;tr&gt;<br>&lt;td&gt;#GetDepr.Name#&lt;/td&gt;<br>&lt;/cfoutput&gt;<br><br>&lt;cfquery name=&quot;GetDrugNum&quot; datasource=&quot;dre_new&quot; dbtype=&quot;ODBC&quot;&gt;<br>Select conf_CNS_Depr_list<br>from dreeval<br>where conf_CNS_Depr_list = '#GetDepr.Name#'<br>&lt;/cfquery&gt;<br><br>&lt;cfoutput query=&quot;GetDrugNum&quot;&gt;<br>&lt;td&gt;#GetDrugNum.RecordCount#&lt;/td&gt;<br>&lt;/tr&gt;<br>&lt;/cfoutput&gt;<br>&lt;/cfloop&gt;<br><br>&lt;/table&gt;<br><br><br>Notes, you should display your records inside the loop and not outside the loop.

 

[Cappi]

Yes, I finally figured that out.&nbsp;&nbsp;Thank you for your input!<br>Kim