• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Problem Quering Multiple selections in a select box

I have three tables
Cutomer:  Has all the usuall customer info
Product: this has a list of 730 products and topics that could be searched on

Custprod: has the both primary keys of the previous 2 tables as FK.

Now I would like to be able to search for a topic then populate a select list based on the privious search string(done that successfully)

now my problem is when I select multiple(single choices work fine)topics I come up with nothing, no record returned but I know that there are record related to all topics selected, can anyone help.  I am in a tight spot.
0
jriver12
Asked:
jriver12
  • 9
  • 6
1 Solution
 
YogCommented:
If multiple messages are selected , for eample 3 are selected , after the form submission they will appear in a comma seperated value
example

#form.mylistbox# will be hello1,hello2,hello3

so when u do futher queries (selects or inserts based on that

if its a select then instead of "=
' you can use "IN"

forexample - usual
select * from table1
where productlist = #form.mylistbox#
now you do
select * from table1
where productlist IN #form.mylistbox#
OR Loop oi

<cfloop from="1" to="#listlen(form.mylistbox)#" index"iIndex">

Now whatever you do, but instead or form.mylistbox, use the index #iIndex#

</cfloop>

Only pasting a snapshot of your code might help..

cheers.
yog
0
 
jriver12Author Commented:
here are the codes for the selection box:

<cfoutput query="ProductSearch">
<option value="#productSearch.DESCRIPTION#">
#productsearch.DESCRIPTION#</option>
</cfoutput>

here is the code that pulls the values from the select.

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE '#form.results#')
</CFQUERY>


this is what I get when the code above is ran in CF :

SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE 'Bearings, Sanitary,Tables, Debeaking & Inoculation')
Bearings, Sanitary is one topic and
Tables, Debeaking & Inoculation is the second

it runs the query but returns no record sets.
0
 
YogCommented:
Solution 1.

<cfloop list="#form.results#" index="i_result" delimiter=",">

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,
dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,
dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
 dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
 (dbo.PRODUCTS.DESCRIPTION LIKE '#i_result#')
</CFQUERY>

</cfloop>
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
YogCommented:
<!---Make ready for using IN--->
<cfset i = 1>
<cfloop list="#form.results#" index="i_result" delimiter=",">
   <cfif i eq 1>
       <cfset mylist = "'" & "#i_result#" & "',">
   <cfelseif i neq listlen(form.results)>
       <cfset mylist = mylist & "'" & "#i_result#" & "',">
   <cfelse>
       <cfset mylist = mylist & "'" & "#i_result#" & "'">
   </cfif>
<cfset i = i + 1>
</cfloop>

Now need not loop the query - use IN

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,

dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,

dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
dbo.PRODUCTS.DESCRIPTION IN (#mylist#)
</cfquery>

cheers..


0
 
YogCommented:
sorry second comment is solution 2.

for solution one, you have too put th output also inside loop accordingly..

cheers.
0
 
jriver12Author Commented:
here are the codes for the selection box:

<cfoutput query="ProductSearch">
<option value="#productSearch.DESCRIPTION#">
#productsearch.DESCRIPTION#</option>
</cfoutput>

here is the code that pulls the values from the select.

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE '#form.results#')
</CFQUERY>


this is what I get when the code above is ran in CF :

SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE 'Bearings, Sanitary,Tables, Debeaking & Inoculation')
Bearings, Sanitary is one topic and
Tables, Debeaking & Inoculation is the second

it runs the query but returns no record sets.
0
 
YogCommented:
Ah did you try the above one i said

---------------
<cfset i = 1>
<cfloop list="#form.results#" index="i_result" delimiter=",">
  <cfif i eq 1>
      <cfset mylist = "'" & "#i_result#" & "',">
  <cfelseif i neq listlen(form.results)>
      <cfset mylist = mylist & "'" & "#i_result#" & "',">
  <cfelse>
      <cfset mylist = mylist & "'" & "#i_result#" & "'">
  </cfif>
<cfset i = i + 1>
</cfloop>
<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,
dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,
dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
dbo.PRODUCTS.DESCRIPTION IN (#mylist#)
</cfquery>

Looks like you didn't
0
 
jriver12Author Commented:
Thanks

Sorry about the last comment added I made the mistake of refreshing my pc and it resent the same comment.

here are the results of codes that you sent.

Trying solution # 1 I got:

Error Diagnostic Information
Just in time compilation error

An unknown attribute 'delimiter' has been encountered at document position (9:48) to (9:56) while processing tag CFLOOP. This tag can only take the following attributes:

COLLECTION
CONDITION
DELIMITERS
ENDROW
FROM
INDEX

Using Solution 2:

Error Diagnostic Information
Just in time compilation error

An unknown attribute 'delimiter' has been encountered at document position (11:48) to (11:56) while processing tag CFLOOP. This tag can only take the following attributes:

COLLECTION
CONDITION
DELIMITERS
ENDROW
FROM
INDEX
ITEM
LIST
QUERY
STARTROW
STEP
TO
The last successfully parsed CFML construct was a CFLOOP tag occupying document position (11:1) to (11:7).

The specific sequence of files included or processed is:
C:\Inetpub\wwwroot\Products\matchco.cfm      


Date/Time: 06/06/01 15:11:11
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)
Remote Address: 127.0.0.1
HTTP Referer: http://127.0.0.1/Products/exhibitProdList.cfm

 




--------------------------------------------------------------------------------



--------------------------------------------------------------------------------
Execution Time

4 milliseconds
4 ms STARTUP, PARSING, & SHUTDOWN



--------------------------------------------------------------------------------
Parameters
Form Fields:

FIELDNAMES=RESULTS,RESULTS,RESULTS



ITEM
LIST
QUERY
STARTROW
STEP
TO
The last successfully parsed CFML construct was a CFLOOP tag occupying document position (9:1) to (9:7).

The specific sequence of files included or processed is:
C:\Inetpub\wwwroot\Products\matchco.cfm      


Date/Time: 06/06/01 15:07:42
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)
Remote Address: 127.0.0.1
0
 
YogCommented:
sorry that is a spelling mistake

Instead of
<cfloop list="#form.results#" index="i_result" delimiter=",">
USE
<cfloop list="#form.results#" index="i_result" delimiters=",">

cheers
0
 
jriver12Author Commented:
that works,  it is now finding the records but it still is not showing all of them:

here is the code for the page can you tell me why they are not comming up.  :




<cfloop list="#form.results#" index="i_result" delimiters=",">

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,

dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,

dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
(dbo.PRODUCTS.DESCRIPTION LIKE '#i_result#')
</CFQUERY>

</cfloop>

<body  >


<cfoutput query="searchit2">

   Company: #COMPANYNAME# <br>
Address:#CompanyMailingAddress#<br>
CITY, STATE ZIP #CompanyCity#, #CompanyState# #ZIP1#<br>
PHONE:#CompanyPhone#<br>
FAX:#CompanyFax#<br>
EMAIL: <a href=mailto:"#CompanyEmail#"> #CompanyEmail#</a><p>
</font>
</cfoutput>  
0
 
YogCommented:
Likewise you need o loop the results also - sorry

<cfloop list="#form.results#" index="i_result" delimiters=",">

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,


dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,


dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
(dbo.PRODUCTS.DESCRIPTION LIKE '#i_result#')
</CFQUERY>

<cfoutput query="searchit2">

  Company: #COMPANYNAME# <br>
Address:#CompanyMailingAddress#<br>
CITY, STATE ZIP #CompanyCity#, #CompanyState# #ZIP1#<br>
PHONE:#CompanyPhone#<br>
FAX:#CompanyFax#<br>
EMAIL: <a href=mailto:"#CompanyEmail#"> #CompanyEmail#</a><p>
</font>
</cfoutput>  

</cfloop>
0
 
YogCommented:
If you use solution 2, you need not loop the result
0
 
jriver12Author Commented:
Thank you very much it worked like a charm and unlike anything I was trying to write It has no errors.

here is an lil extra for the final twist on the results.

again thanks
0
 
jriver12Author Commented:
like always you guys are flawless..
0
 
YogCommented:
Glad that worked :)

cheers..
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now