Separating values from check list into query

Hi I am new to coldfusion and I have been stucked doing a function for 2 days without avail. To put it short, I am trying to create a queries in table format and with each record having a checkbox. When the users click and tick on any of the checkbox, the query will be able to edit all the records that have been ticked.

This is the code i use to display my queries



<cfoutput query="qi">




<tr>

<td>


<input type="checkbox" name="check" value="#qi.ic#">


</td>

<td> #qi.fname#</td>

<td> #qi.ic#</td>
<td> #qi.contact#</td>
<td> #qi.position#</td>
<td> #qi.grade#</td>
</tr>

</cfoutput>





And this is my coding of what action should take if the user click on the checkbox and submit the form



<cfif isdefined("form.accept")>

<cfoutput query="qi">
<cfif isdefined("form.check") AND form.check EQ "on">
<cfquery name="qu" datasource="#application.fw.config.dsn#">

UPDATE applicant
SET status = 'accepted'
where ic = '#form.check#'


</cfquery>






I have tried all possibilities. The problem is whatever that has been ticked will be output as record1,record2

which is separated by a comma. I cant separate the result in which i can update the database on the ticked records only. I found out that whenever you tick any of the checkbox, the system will treat it as "As long as one box is ticked the whole query record will be updated" instead of "Only update those record which is ticked". Please help me out im really clueless

 

I have thought of an idea which is :



<cfoutput query="qi">
<cfparam name="number" default="1">

<cfif currentrow mod 2>
<cfset rowcolor = "##A6D2DD">
<cfset fontcolor = "##000000">

<cfelse>
<cfset rowcolor = "##E2E6F2">
<cfset fontcolor = "##000000">
</cfif>


<tr bgcolor="#rowcolor#" onMouseOver="this.bgColor='##259C41';" onMouseOut="this.bgColor='#rowcolor#'; ">

<td>


<input type="checkbox" name="check#number#" value="#qi.ic#">


</td>

<td> #qi.fname#</td>

<td> #qi.ic#</td>
<td> #qi.contact#</td>
<td> #qi.position#</td>
<td> #qi.grade#</td>
</tr>
<cfset number = number + 1>
</cfoutput>


By including the #number# this will specified that each check list is listed as check1, check2, check3 ...so on to uniquely distinguish each checkbox.


But the problem is the action that is going to take afterwards which is





<cfif isdefined("form.accept")>


<cfparam name="nombor" default="1">

<cfoutput query="qi">
<cfif isdefined("form.check#nombor#") AND form.check#nombor# EQ "on">
<cfquery name="qu" datasource="#application.fw.config.dsn#">

UPDATE applicant
SET status = 'terima'
where ic = '#form.check#nombor##'


</cfquery>


<cfscript>
application.fw_notice("Added #qi.ic#");
</cfscript>

</cfif>

<cfset nombor = nombor + 1>

</cfoutput>
<cfreturn>

</cfif>



The real problem is this line :

<cfif isdefined("form.check#nombor#") AND form.check#nombor# EQ "on">

I dont know how to put a variable inside a CF tag so it remains as a variable.. My purpose is to make it loop
from form.check1, form.check2, form.check3 until the end of the query... Please help

 

The checkbox value should be the id, not "on".

FORM is a structure. You can use array notation to access dynamically named fields:

<cfif FORM["check"& nombor] ...>
...
</cfif>

Code (markup):

Yes, that is a good approach if each record may be updated with different values. But it looks like you are updating a bunch of records with the same value. For example, marking all of the checked records with the status "accepted":

UPDATE applicant
SET status = 'accepted'
where ic = '#form.check#'

If that is the case I would use your original code, with one change. In your query, use an IN clause. It will allow you to pass in a list of id's, instead of just one. So any record matching one of those ids will be updated.

<cfset form.check = "1,15,6,8,9">
<cfif isDefined("form.check") and listLen(form.check)>
   <!--- change the cfsqltype to match your column type --->
   <cfquery ...>
   UPDATE applicant
   SET status = 'accepted'
   where ic IN 
   (
   <cfqueryparam value="#form.check#" list="true" 
         cfsqltype="cf_sql_integer">
   )
   </cfquery>
</cfif>

Code (markup):

 

Hi, I sincerely thank you for your help!

I was amazed how only two pieces of code can make a difference!

What does they do? Please do enlighten me and thanks again

 

1. Array notation lets you access dynamically named fields
2. The sql IN (...) clause lets you update multiple records in a single query.

 

Thanks for the previous help... How about if i would like to separate these values (where the form is checked) and insert into a new table?

I try to search the net and somehow people are using arrays but i dont quite get it. Can someone give me a simple example using my coding?

Sorry for the trouble I learn faster by example

 

You can use arrays, but a simple method is to use <cfloop list="...">. It is used to loop through a _list_ of values. Inside the loop you can do your INSERT:


<cfparam name="form.check" default="">
<cfloop list="#form.check#" index="currentValue">

<!--- change the cfsqltype to match your column type --->
<cfquery ...>
INSERT INTO YourTable ( TheColumnName )
VALUES
(
<cfqueryparam value="#currentValue#" cfsqltype="cf_sql_integer">
)
</cfquery>
</cfloop>

 

Ah thanks alot !!!! I truly appreciate that... Though im still blur what is all the code is doing... When does the insert examples and the update examples that you show actually separate the values? From what I know is that the always group in a single value like "value1,value2,value3" but how did you separate them using the <cfloop>??

You are really a genius and a good samaritan

 

Cfloop does it auto-magically ;-) When you use "list" ColdFusion assumes the value is a list of elements separated by a comma. It loops through the list, one-by-one, and each time it populates the "index" variable with the value of the current element.

For example, run this code. It will print out 3 lines, one for each element in the list:

<cfset form.check = "value1,value2,value3">

<cfoutput>
<cfloop list="#form.check#" index="currentValue">
cfloop is now processing : #currentValue#<br>
</cfloop>
</cfoutput>

You can also use different delimiters (ie something than a comma). But comma is the default.

 

Ok my last question is using the cfloop, how do i insert 2 values into the same query?

as in

Insert into table 1
(column1, column2)
values
(value1, value1)


As i believe the cfloop cannot have both two list and index

 

I suppose you could do it with some more complicated list logic, but why? I suspect you are no longer talking about the original case where: form.check = "item1,item2,item3".

Normally, when you have multiple pieces of information that are related and must be inserted together you would structure your form differently.

 

Oh actually at 1st i only wanted to insert one column to another table but now i realised i need to insert 2.


Its something like I query 4 columns for a single row

Example:

Firstname
Jackson


LastName
Smith


Phone
32424234

address
England


Ok so any of the row is checked on the check list, two values of first and last name must inserted to another table. As for the unchecked list just leave the rows there.

I can only get one column to the new table but not more than that

 

Relate the form field names by using a common suffix. For example, assuming your database table has a unique ID, you could use the recordID as the checkbox value

<input type="checkbox" value="#RecordID#" ..>

Then use the recordID again to name the textboxes. That will let you relate those fields to a specific id.

<input type="text" name="firstName_#RecordID#"...>
<input type="text" name="lastName_#RecordID#"...>

On your action page, the checked recordID's will be passed as a comma separated list. Loop through the ID's and use array notation to extract the related first and last names. All that is left is to insert those values into your table


<cfloop list="#form.yourCheckboxName#" index="recordID">
<cfset variables.firstName = form["firstName_"& recordID]>
<cfset variables.lastName = form["lastName_"& recordID]>
<cfquery ...>
.... insert the values ...
</cfquery>
</cfloop>