[websky]

Hi All,
I really need help on this. Can someone help with SQL that will update the database using dyanimic radio button. See my script below:

<cfquery name="qItem" datasource="req">
SELECT id, 
Item,
ItemQty,
name,
FROM REQUISITION
ITEMSTATUS='P'
</cfquery>
something like this for the form

<form>
<cfoutput query="qItem">
 #currentrow#
 #id#
#Name#
#Item#
#ItemQty#
<input type="radio" name="radio_#currentrow#" value="approve" id="approve" />
<input type="radio" name="radio_#currentrow#" value="decline" id="decline" />
</cfoutput>
<input type="submit" name="approveBtn" id="button" value="Click to Approve" />
</form>

So I need SQL that will update the database based on the radio buttion chosen for the records.
Thanks

Edit: Please use the code button when posting blocks of CF code

This post has been edited by Craig328: 29 November 2010 - 03:25 PM

[Craig328]

Welcome to D.I.C. websky. What you'll need is a little more than the SQL to update the database record. You'll also require the form's action page (place where you're sending the form information) that will contain the SQL query that does the update.

Of course, for anyone to accurately assist you we'd need to know the table you'd be wanting to perform the insert or update on, as well as the column names and datatypes of those columns.

You look like you're kind of a beginner to CF and that's okay. How about you tell us a little something about your experience level and the project you're working on while you go get that database info?

For starters though, the form elements you have there, the radio buttons, the way you have it set now, you won't be able to tell which record you're wanting to update on the action page. The currentRow attribute is simply an incremental counter signifying the number of records you've looped over in the CFLOOP. On the receiving page, that value (as held in the field's name attribute) won't do you much good. Better to replace the currentRow value with the #qItem.id# value...if that is a unique record identifier in your requisition table.

[websky]

I am beginner in CF (sorry I didn't mention that at the onset) and thanks Craig328 for your response and your advice.

Kindly find below the modified script:

<cfquery name="qItem" datasource="req">  

SELECT id,   

Item,  

ItemQty,  

name,  

FROM REQUISITION  

ITEMSTATUS='P' 

</cfquery> 

something like this for the form

<form action="updateitem.cfm">  

<cfoutput query="qItem">  

#currentrow#  

#id#  

#Name#  

#Item#  

#ItemQty#  

<input type="radio" name="radio_#qItem.id#" value="approve" id="approve" />  

<input type="radio" name="radio_#qItem.id#" value="decline" id="decline" />  

</cfoutput>  

<input type="submit" name="approveBtn" id="button" value="Click to Approve" />  

</form>

Then the action page will look something like this:

<CFLOOP QUERY="qItem">

<CFQUERY name="apprvd" DATASOURCE="requisition">
UPDATE REQUISITION  
SET ITEMSTATUS = (how do I update this column to either "approve" or "decline" from the radio button option)
WHERE ID = #qitem.ID#
</CFQUERY>

</CFLOOP>

I hope this is more clearer. I look forward to your feedback

This post has been edited by Craig328: 30 November 2010 - 06:45 AM

[Craig328]

Okay. So, since what you're looking for is the bare bones SQL for the action page, I think I can help.

Here's what you currently have:

<CFLOOP QUERY="qItem">
	 
<CFQUERY name="apprvd" DATASOURCE="requisition">
UPDATE REQUISITION 
SET ITEMSTATUS = (how do I update this column to either "approve" or "decline" from the radio button option)
WHERE ID = #qitem.ID#
</CFQUERY>	 

</CFLOOP>

Let me start by asking: have you tried this? I suspect you haven't yet because you're not asking "what does this error mean?" and what you posted contains two errors. Since you're new to D.I.C. we'll continue but in the future when you need code assistance, please show some initiative and actually try it yourself before asking others to do it for you.

To continue: the code above will be on your action page. The action page is a separate page (or may not be but the way you have it here, it probably is) and as such, queries done on previously requested pages won't be available. So, your code that loops over the query, qItem, will fail because the query result set from qItem won't exist for the action page request. What you need instead is a way to loop over ids that the user clicked either approved or declined for the radio buttons. Luckily, you have this information but maybe don't know it. When you submit a form to an action page (presumably like you'll do here) you will have available to you all the name value pairs from that form on the action page. There is a CF tag you can use to see this and that's called CFDUMP.

What you'll want to do at the top of the action page is this:

<cfdump var="#form#"><cfabort>

That will dump the entire form scope (the variable scope that contains all submitted form data) to the screen for you to see. What you should see is a array of form elements in a blue matrix with the form variable name on the left and the value for that variable to the right. Anyway, in there you should see a number of variables that look like this: radio_1, radio_2, radio_4, radio_10 and so on and they'll have either "approve" or "decline" values. Note that you won't see every ID you might have pulled for qItem that you looped over to create the form matrix on your form page. This is because unless the user selects either approve or decline (they select neither) the form element won't be passed and, thus, you won't have it on action page.

Now, the thing to do is to create a loop of some kind, use the index of that loop (a sequencing numerical value) to check for the existence of a form element and if it exists, do your update query with the value of that existent form element. Sounds easy, right? Consider though: how do you know the values of the possible ID's that you might use? The action page has no idea what they could be...in fact, they may not even be numbers at all. There are a couple of ways around that dilemma but the easiest in this case is to amend your form page to pass a list of all possible ID values and then on the action page, loop over that list.

So, on the form page after the query you'll want to do something like this:

<cfset variables.listOfIds = ValueList(qItem.ID)>

Now, that uses a CF function called ValueList and it's quite handy for what you're needing (a list of IDs to pass to your action page). So, on your form page you now have a variable that holds a comma delimited list of you IDs from the qItem query. You can pass that to the action page in a hidden form field like so:

<cfoutput><input type="hidden" name="IDs" value="#variables.listOfIds#"></cfoutput>

You'll want to place that after the <form> tag and before the <cfoutput> tag you have on your form page. So, with that done, back to the action page.

Now, on the action page, if you run the CFDUMP of the form scope again you'll see a variable there called IDs and the value for it will look something like this: 1,2,3,4,5,6,7,8,9,10. You can CFLOOP over that list of IDs to do your form variable evaluations for your update queries rather than guess at what values you should use to build the radio form element names you might have passed in.

So, try all that first with what you have. Post back here and tell us how you're doing and whether you need still more assistance. As a hint: you'll need to make use of this CF function to properly create the values you'll need to do the update SQL: Evaluate().

Good luck!