Solved

How to use checkbox data in a query

Posted on 2007-11-28
9
830 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
[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
  • 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
Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

 
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

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 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