• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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