Solved

Update one or more rows in one step.

Posted on 2011-03-04
7
327 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
Comment Utility
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
Comment Utility
I can't us cfdump on my shared hosting account.
0
 
LVL 10

Expert Comment

by:js_vaughan
Comment Utility
i had it there for testing, the line is not needed.  sorry for the confusion
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:aka_FATCAT
Comment Utility
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
Comment Utility
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
Comment Utility
Ahhh, my level of ignorance has been upgraded to master level.

Thank you.
0
 

Author Closing Comment

by:aka_FATCAT
Comment Utility
Simply elegant.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now