Solved

Update one or more rows in one step.

Posted on 2011-03-04
7
332 Views
Last Modified: 2012-05-11
First page queries the database for open invoices, the user selects one or more invoices and presses submit.

The second page displays the invoice numbers and the total for all invoices selected.

The user then selects the payment form and submits.

Im having trouble figuring out how to update the invoice status to paid.

UPDATE invoice
SET invoice_status = '1'
SET balance = '0'
WHERE inv_id = '#FORM.invoice_id#'

How can i make it loop through all the selected invoices and update them all.

Thanks.
0
Comment
Question by:aka_FATCAT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 10

Accepted Solution

by:
js_vaughan earned 500 total points
ID: 35042032
Below is some sample code.  Basically, make sure all your invoice fields are the same name.  When the form posts, the result will be a comma delimited list of all of the values.  Then, it is a simple matter of using SQL's IN() to accept multiple values.
<cfif structKeyExists(FORM,"invoice_id")>
	<cfdump var="#FORM.invoice_id#" />
	<cfquery name="myQuery" datasource="#application.db#">
		UPDATE invoice
		SET invoice_status = <cfqueryparam cfsqltype="cf_sql_integer" value="1" />
		SET balance = <cfqueryparam cfsqltype="cf_sql_integer" value="0" />
		WHERE inv_id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.invoice_id#" list="yes" />
	</cfquery>
</cfif>

<form name="myForm" method="post">
	<input type="checkbox" name="invoice_id" value="1" /> Invoice 1 <br />
	<input type="checkbox" name="invoice_id" value="2" /> Invoice 2 <br />
	<input type="checkbox" name="invoice_id" value="3" /> Invoice 3 <br />
	<input type="submit" />
</form>

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35042047
I can't us cfdump on my shared hosting account.
0
 
LVL 10

Expert Comment

by:js_vaughan
ID: 35042050
i had it there for testing, the line is not needed.  sorry for the confusion
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

Author Comment

by:aka_FATCAT
ID: 35042126
That sort of works.

The problem is that the form is on one page that when submitted displays the information on a conformation page. The user then chooses the payment method and submits the form.

So I will need to store the invoice_id's somewhere for it to be used when the second form is submitted.

Thanks
0
 
LVL 10

Expert Comment

by:js_vaughan
ID: 35042176
put a hidden formfield on your confirmation page.
<input type="hidden" name="invoice_id" value="#FORM.invoice_id#" />

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35042184
Ahhh, my level of ignorance has been upgraded to master level.

Thank you.
0
 

Author Closing Comment

by:aka_FATCAT
ID: 35042186
Simply elegant.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question