Deleting multiple records via html checbkoxes?

[lovewebdev]

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.

[-__cfSearching__-]

> 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".

[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.

[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.

[Dan_Bracuk]

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__-]

> the sql keyword, "in"

Well darn. That would certainly help too 😉

[lovewebdev]

Can you put it together for me. I'm a little confused about the syntax.

[-__cfSearching__-]

> 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="...">
)

[lovewebdev]

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.

[Dan_Bracuk]

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.

[Tones411]

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