Skip to main content
lovewebdev
lovewebdev
Inspiring
February 5, 2009
Question

Deleting multiple records via html checbkoxes?

  • February 5, 2009
  • 7 replies
  • 1355 views
I'm trying to delete multiple records in a table based on the selection of checkboxes.

I have an html form with 4 checkboxes. If a user checks any box, I want to delete the associated records. and if they ucnheck something I want to insert multiple records. This form does not hold the primary keys but other unique parameters. Here is my sql statement.

This topic has been closed for replies.

7 replies

T
Tones411
Participating Frequently
February 11, 2009
I have modified your code. See the last line... you need to put the actual database column name where I put the word ColumnName.

DELETE FROM table
WHERE userid = <cfqueryparam cfsqltype="cf_sql_integer" value="#COOKIE.ckuserid#">
AND channelid = <cfqueryparam cfsqltype="cf_sql_integer" value="#varc#"> AND preferenceid = <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.pid#">
AND ColumnName IN <cfqueryparam cfsqltype="cf_sql_bit" value="#FORM.checbox#" list="true">

D
Dan_Bracuk
Inspiring
February 5, 2009
insert into yourtable
(field1, field2)
<cfloop list="#form.checkboxfield# index = "abc">
select #abc#, #form.textfied#
from some_small_table
union
</cfloop>
select #abc#, #form.textfied#
from some_small_table
where 1 = 2

but with cfqueryparam of course.

Also, at some point you have to ensure that at least one box was checked.
lovewebdev
lovewebdevAuthor
Inspiring
February 5, 2009
Is it possible to do the opposite. I have 2 fields. One field is a textfield. The other field is multiple checkboxes whose names & values come from a query. I want to insert field one multiple times according to how many checkboxes are selected. I also need to insert the checkbox value as a column along with each record.

The result of the form is one textfield that needs to be inserted into a table as many times as there are checkboxes selected along with the value of the checkbox that needs to be submitted with as a column value.
lovewebdev
lovewebdevAuthor
Inspiring
February 5, 2009
Can you put it together for me. I'm a little confused about the syntax.
-
-__cfSearching__-
Inspiring
February 5, 2009
> and that keyword is not mentioned in the DELETE statement chapter.

IN is not specific to DELETE statements. It can also be used with SELECT or UPDATE statements. It is a way of specifying multiple values in a single statement, instead of using multiple OR statements:

http://www.w3schools.com/sql/sql_in.asp

Since you are using cfqueryparam, you also need to use list="true" (ie you are passing in a list of values, not just one)

<cfset listOfValues = "1,2,5,6,8">
WHERE ColumnName IN
(
<cfqueryparam value="#listOfValues#" list="true" cfsqltype="...">
)
-
-__cfSearching__-
Inspiring
February 5, 2009
> the sql keyword, "in"

Well darn. That would certainly help too ;-)
D
Dan_Bracuk
Inspiring
February 5, 2009
If you are not familiar with the sql keyword, "in", I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
lovewebdev
lovewebdevAuthor
Inspiring
February 5, 2009
quote:
Originally posted by: Dan Bracuk
If you are not familiar with the sql keyword, "in", I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.


Hmm. That's weird I actually have that book and that keyword is not mentioned in the DELETE statement chapter.

D
Dan_Bracuk
Inspiring
February 5, 2009
quote:
Originally posted by: lovewebdev
quote:
Originally posted by: Dan Bracuk
If you are not familiar with the sql keyword, "in", I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.


Hmm. That's weird I actually have that book and that keyword is not mentioned in the DELETE statement chapter.


cfsearching has given you the answer. I'm curious as to whether or not the keyword "in" is in the index of that book. I have a couple of "Teach Yourself" books, but not that one.
-
-__cfSearching__-
Inspiring
February 5, 2009
> Invalid data 1,3,4,2 for CFSQLTYPE CF_SQL_INTEGER.
> <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.pid#">

To pass in multiple values, use cfqueryparam's list="true".
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices