Solved

Update one or more rows in one step.

Posted on 2011-03-04
7
330 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 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