CFLoop through form fields problem

[tromanNMV]

Hi all,

Maybe someone may know this answer but it's driving me nuts.

I am looping through form fields from another page and trying to gather form field name values from dynamic checkboxes.

I want to extract the ID values built into the names...and delete those records.

Each checkbox name begins with "img_". (For example : img_59)

This is the code I am using :

----------------------------------------------------------------------------------------------------

<cfloop index="i" list="#Form.FieldNames#" delimiters=";">

<cfif LEFT(i, 4) IS "img_">
<CFSET selectImageID = RemoveChars(i, 1, 4)>
<CFSET selectImageID = #Evaluate(selectImageID)#>
</cfif>

<!--- Delete Query for selected image IDs --->
<cfquery name="deleteImages" datasource="ctmprojects">
DELETE
FROM photos
WHERE ID = #selectImageID#
</CFQUERY>
</cfloop>

<cflocation url="edit.cfm?ID=#Form.ID#">

----------------------------------------------------------------------------------------------------

The form has one hidden field with an "ID" value.... but most are dynamic checkboxes that have names that start with "img_".

I need to loop through these to capture the Record numbers of the images to delete.

The problem is : it does not seem to be looping a single name value at a time....

If I do a <cfoutput>#i#<br></cfoutput>  at the beginning or end of the loop I get a full list of form values each time ... such as...

ID,IMG_61,IMG_63

This is causing it to not evaluate the first 4 characters of each.

The code works if the form only has one checkbox because the result is one value... but with multiple form fields it's always one long string with the delimiters...

Does anyone have a clue as to what is going on?.... Or is there a better method of doing this?

Thanks so much

Tim Roman

[Adam Cameron.]

Hint: what's the list delimiter of form.fieldnames, and what delimiter are you actually using...?

--

Adam

[tromanNMV]

Hi Adam,

I tried using a comma as the delimiter first.......  but that failed to work also. Sorry the example shows a semi-colon.... But, it is now a comma again in my code. However, when I use a comma it still does not loop through individual names... If I <cfoutput> the value of the list inside the loop.... it's a list of all the values separated by a space....?

Thanks for your response though..

Tim

[Adam Cameron.]

If you just have this:

<cfloop index="i" list="#Form.FieldNames#" delimiters=";">

<cfoutupt>#i#<br /></cfoutput>

</cfloop>

What do you see?

Also, is the value that selectImageID holds the name of another form field?  If that's the case you can ditch the evaluate() call and just use form[selectImageID].  It's better form (no pun intended).

--

Adam

[tromanNMV]

<cfloop index="i" list="#Form.FieldNames#" delimiters=";">

<cfoutput>#i#<br /></cfoutput>

</cfloop>

RESULT :

ID,IMG_57,IMG_58

With a comma :

<cfloop index="i" list="#Form.FieldNames#" delimiters=",">

<cfoutput>#i#<br /></cfoutput>

</cfloop>

RESULT :

ID
IMG_61
IMG_63

And, no, it is not the only form field to be evaluated. I also have one hidden form field with the recordID (not for the images).....

I am using this to return the person to the edit page ...with the proper record number.

The full code :

---------------------------------------------------------------------------------------

<cfloop index="i" list="#Form.FieldNames#" delimiters=",">

<cfif LEFT(i, 4) IS "img_">
<CFSET selectImageID = RemoveChars(i, 1, 4)>
<CFSET selectImageID = #Evaluate(selectImageID)#>
</cfif>

<cfquery name="deleteImages" datasource="ctmprojects">
DELETE
FROM photos
WHERE ID = #selectImageID#
</CFQUERY>
</cfloop>

<cflocation url="edit.cfm?ID=#Form.ID#">

---------------------------------------------------------------------------------------

Error :

Variable SELECTIMAGEID is undefined.

Thanks for your time

Tim

[Adam Cameron.]

OK, so with the comma as the delim, it does iterate over the form fields fine.

Don't you want your <cfquery> to be inside you <cfif/> block though?

--

Adam

[-__cfSearching__-]

With a comma :

....

RESULT :

ID
IMG_61
IMG_63

Notice the first field that will be processed is "ID"?  That means the first time the loop runs your CFIF is not processed. So #selectedImageID# will not be defined.  Since the query is not inside the CFIF, it will be executed on every loop.  Since #selectedImageID# is not defined .. that is why you get the error.

Having said that, why not just give the checkboxes the same name, like "imageID". Then make the checkbox "value" the image ID (ie 61,63..)?  Then the selections will be passed as a single comma delimited list and you can easily delete the records in one fell swoop using an IN clause?  Ignoring validation ...

<!--- NOT tested --->
DELETE
FROM photos
WHERE ID = <cfqueryparam value="#form.imageID#" cfsqltype="cf_sql_integer" list="true">

Message was edited by: -==cfSearching==-

[tromanNMV]

Thanks for the reply CFSearching...

I think it's really close :

I now have the following code in the file.. :

I know it is sending the correct values.... but not sure how the syntax is wrong...

------------------------------------------------------------------------------------------------------------

<cfquery name="deleteImages" datasource="ctmprojects">
DELETE
FROM photos
WHERE ID = <cfqueryparam value="#Form.imageID#" cfsqltype="CF_SQL_INTEGER" list="yes">
</cfquery>

------------------------------------------------------------------------------------------------------------

The error I get is :

------------------------------------------------------------------------------------------------------------

Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '63' at line 3

The error occurred in C:\Websites\ctmconstruction.net\admin\editImages.cfm: line 7

5 : DELETE
6 : FROM photos
7 : WHERE ID = <cfqueryparam value="#Form.imageID#" cfsqltype="CF_SQL_INTEGER" list="yes">
8 : </cfquery>
9 :

SQLSTATE        42000
SQL        DELETE FROM photos WHERE ID = (param 1) , (param 2)
VENDORERRORCODE       1064
DATASOURCE       ctmprojects

------------------------------------------------------------------------------------------------------------------------------------------------------

I changed the value of list to "Yes" per the CF documentation but cannot figure out how the syntax is wrong...

The one thing I'm not sure of is...

If in the form being sent the checkbox fields look like :

<input type="checkbox" name="imageID" value="#ID#">

Could it be that the value of the checkbox is not sent as an Integer? I would assume it would have a different error message though... like wrong datatype....if that was the problem.

But, can't figure anything else it could be....

Thanks again...

Tim

[Adam Cameron.]

What's the SQL syntax for seeing whether a value IN a list?

--

Adam

[tromanNMV]

Thanks Adam and CfSearching... you guys rock!

Changed it to :

----------------------------------------------------------------------------------------------------------

<cfquery name="deleteImages" datasource="ctmprojects">
DELETE
FROM photos
WHERE ID IN (<cfqueryparam value="#Form.imageID#" cfsqltype="CF_SQL_INTEGER" list="yes">)
</cfquery>

<cflocation url="edit.cfm?ID=#Form.ID#">

----------------------------------------------------------------------------------------------------------

And it works great!

Thanks!

Tim

[-__cfSearching__-]

I think it's really close :

No. My example was not even in the same ball park.  I am headed to get some direly needed sleep

Here is a real example.  Obviously substitute the <cfqueryparam...> for the value1,... part.

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