?
Solved

Problem Quering Multiple selections in a select box

Posted on 2001-06-05
15
Medium Priority
?
165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 500 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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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