SQL query for multiple selects.

[ahksar]

Hi,
I am using CFSELECT and populating a select box, with multiple selections enabled. If I do make multiple selections, how do I write the SQL to obtain the different records associated with each selection?The SQL works fine for a single select, but if I click on more than one, no records are obtained.Could anyone please let me know how I can correct this.
Thanks
ahksar

 

[ingernet]

This is fairly simple to do with a CFLoop statement:

1. do the query. we'll name it "myquery."

<cfquery name=&quot;myquery&quot; datasource=&quot;mydb&quot;>
select thingy from mytable
</cfquery>

2.then write out your html:

<select name=&quot;myselect&quot;>
<cfloop query=&quot;myquery&quot;>
<cfoutput><option value=&quot;#thingy#&quot;>#thingy#</option>
</cfloop>
</select>

hope this helps!
inger

 

[ahksar]

Inger,
Thanks for your response. But I managed to get populate the select list using this:
<SELECT NAME=&quot;SourceID&quot; MULTIPLE size=3>
<CFOUTPUT QUERY=&quot;GetSource&quot;>
<OPTION VALUE=&quot;#GetSource.SourceID#&quot;>
#GetSource.SourceName#
</OPTION>
</CFOUTPUT>
</SELECT>

The problem im having is after the form is submitted.
My query is:
<CFQUERY
NAME=&quot;MakeList&quot;
DATASOURCE=&quot;Contacts&quot; DEBUG>
SELECT DISTINCTROW Address
FROM tblList
WHERE SourceIDFK IN ('#form.SourceID#')
</CFQUERY>

When I submit the form, if I just select one entry in the list, it works fine, as shown below after debugging, where 3 records are selected.


MakeList (Records=3, Time=3ms)
SQL =
SELECT DISTINCTROW Address
FROM tblList
WHERE SourceIDFK IN ('2')



But if I select more than one entry in the form, it does not select any records as shown below.

MakeList (Records=0, Time=2ms)
SQL =
SELECT DISTINCTROW Address
FROM tblList
WHERE SourceIDFK IN ('2,4')

Any help will be appreciated.
Thanks
ahksar

 

[ingernet]

Ahksar, I apologize for misreading your previous message. Low blood sugar was a definite culprit there.

The problem lies in the creation of your &quot;form.SourceID&quot; variable, I believe. I was looking through the Oracle 8i SQL reference, and I found something you might find interesting. (I'm not sure which DB you're using, but SQL is pretty much SQL when it comes to SELECT statements.)

Select * from feh
where buh in
('option 1', 'option 2');

In other words, the SourceID variable needs to be a comma-delimited list of values that are each in their own set of single quotes. To use your code above, ('2','4'). So you'll probably have to create a new variable using a cfloop statement (gotta love them loops!).

Best of luck!
Inger

 

[strantheman]

hi ahksar

Well, ingernet is correct in that you will need to have quotes around each element of the list.. but thats only if those elements are strings. Since they are numbers you need only commas.

Just take the quotes out altogether.

SQL =
SELECT DISTINCTROW Address
FROM tblList
WHERE SourceIDFK IN (2,4)

let me know if this helped