Solved

How to use checkbox data in a query

Posted on 2007-11-28
9
823 Views
Last Modified: 2013-12-24
I have a form which i need to select the user from, this uses a query to populate the checkboxes.

This works fine, submits data to the next page and then I need to use the names selected in the checkboxes to do another query.

The problem is the names are submitted as:
name1,name2,name3,name4

How do I seperate these names to use them as individual variables?

I understand I will need to create a query for each name, and that is fine, how do I seperate them in the first place though?
0
Comment
Question by:unitedmp
  • 5
  • 4
9 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20370529
An easier option is to give all of the checkboxes the same name.

<cfoutput query="yourQuery">
   <input type="checkbox" name="name" ...>
</cfoutput>

Then the values will be submitted as a comma separated:

form.name = value1, value2, value3

You can then use the values with an IN (....) clause in your query.  Though you must check that the list is not empty first.

<cfquery ....>
     SELECT   TheColumns
     FROM     YourTable
     WHERE   TheColumn IN (  <cfqueryparam value="#form.name#" list="true" cfsqltype="your type"> )
</cfquery>

0
 
LVL 52

Expert Comment

by:_agx_
ID: 20370535
ie.  You could run one query to get all of the selected names, instead of separate queries.
0
 
LVL 1

Author Comment

by:unitedmp
ID: 20370586
Here is what I have so far.

FORM PAGE:

<CFOUTPUT Query="selectgroup">
  <INPUT Type="Checkbox" Name="prjgroup"
    Value="#username#">#fullname#<br />
</CFOUTPUT>

PROCESSING PAGE:

<cfquery datasource="#Application.DSN#" dbtype="odbc" name="findusers">
SELECT *
FROM users
WHERE username IN(#form.prjgroup#)
</cfquery>

This does seem to seperate the values, however i get a different error:
Invalid column name 'second checkbox option'
0
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
LVL 1

Author Comment

by:unitedmp
ID: 20370593
also, not sure what you mean by  cfsqltype="your type">
0
 
LVL 1

Author Comment

by:unitedmp
ID: 20370602
now have
<cfquery datasource="#Application.DSN#" dbtype="odbc" name="findusers">
SELECT *
FROM users
WHERE username IN(<cfqueryparam list="yes" value="#form.prjgroup#" cfsqltype="cf_sql_varchar">
</cfquery>

ERROR:
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P2'.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20370639
> not sure what you mean by  cfsqltype="your type">

Just replace it with the correct type for your column:  cf_sql_varchar, cf_sql_integer, etc..

> [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P2'.
      
You left out the closing parenthesis ).   Try

    WHERE username IN
     (
        <cfqueryparam list="yes" value="#form.prjgroup#" cfsqltype="cf_sql_varchar">
       )

Don't forget - validate the list size.  If the list is empty the query will throw an error.
0
 
LVL 1

Author Comment

by:unitedmp
ID: 20370648
my mistake, forgot to close the IN with  )

Cheers for your help, works a treat.

0
 
LVL 1

Author Closing Comment

by:unitedmp
ID: 31411569
Greatr work, thanks for your help
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20370661
Glad to help
0

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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