Solved

Problem Quering Multiple selections in a select box

Posted on 2001-06-05
15
154 Views
Last Modified: 2013-12-24
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
Comment
Question by:jriver12
  • 9
  • 6
15 Comments
 
LVL 5

Expert Comment

by:Yog
ID: 6158310
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
 

Author Comment

by:jriver12
ID: 6160166
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
 
LVL 5

Expert Comment

by:Yog
ID: 6160234
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:Yog
ID: 6160287
<!---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
 
LVL 5

Expert Comment

by:Yog
ID: 6160293
sorry second comment is solution 2.

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

cheers.
0
 

Author Comment

by:jriver12
ID: 6160790
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
 
LVL 5

Expert Comment

by:Yog
ID: 6160869
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
 

Author Comment

by:jriver12
ID: 6161018
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
 
LVL 5

Expert Comment

by:Yog
ID: 6161036
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
 

Author Comment

by:jriver12
ID: 6161238
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
 
LVL 5

Accepted Solution

by:
Yog earned 125 total points
ID: 6161306
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
 
LVL 5

Expert Comment

by:Yog
ID: 6161313
If you use solution 2, you need not loop the result
0
 

Author Comment

by:jriver12
ID: 6161326
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
 

Author Comment

by:jriver12
ID: 6161327
like always you guys are flawless..
0
 
LVL 5

Expert Comment

by:Yog
ID: 6161338
Glad that worked :)

cheers..
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question