[ic] possible to update multiple records with a single form

[Bill Carr]

On Jan 22, 2009, at 11:11 AM, DB wrote:

>>> I added a field named 'notes' to my orderline table. I want to
>>> create a
>>> form which list the code and notes fields for each orderline record
>>> with
>>> a given order_number, which I can easily to with a mysql query.
>>>
>>> But, I want the notes field to be editable for each record and then
>>> have
>>> all records updated when I submit the form.
>>>
>>> Does anyone have any experience or advice?
>> I've just done something similar using script.aculo.us inplace  
>> editor.
>> It's not quite exactly what your asking for but the end user
>> experience is similar. If you're interested in that approach I'll  
>> post
>> details.
>>
>> Bill Carr
>> Bottlenose - Wine & Spirits eBusiness Specialists
>> (413) 584-0400
>> http://www.bottlenose-wine.com
>
> Sure - I'd like to see all possibilities. Thanks!
First of all get http://script.aculo.us/ installed.

Make your page and add something like this:

[query
	your query tag stuff
	make sure you select orderline_id and note
]
	[list]
		<div id="orderline_id_[sql-param orderline_id]">[sql-param note]</div>
		<script type="text/javascript" charset="utf-8">
			var opt = { callback: function(form,value) { return  
'mv_todo 
= 
set 
&mv_data_table 
= 
orderline 
&mv_data_function 
=update&mv_data_fields=note&mv_data_key=orderline_id&orderline_id=[sql- 
param orderline_id]&note=' + escape(value)} };
			new Ajax.InPlaceEditor('orderline_id_[sql-param  
orderline_id]','[process]', opt);
		</script>
	[/list]
[/query]

The above is untested. I just modified my stuff to hopefully fit your  
application.

I did not use the IC 'process' page in my application. I made a new  
page that I'll post below. You need the page called that does the  
update to return the value updated. There might be a way to do that  
within Interchange but I didn't know it. Update the page below to get  
your Database handle. I do it in a way that is specific to our setup.

[tag op=header]Content-Type: text/html; charset=utf-8;[/tag]
[perl global=1]
	# $Id$
	use feature 'switch';
	my $dbh = ### GET YOUR DATABASE HANDLE HERE ####
	given ($CGI->{mv_data_function}) {
		when ('insert') {
			my $values = &get_values($CGI->{mv_data_fields});
			my $sql = qq{INSERT $CGI->{mv_data_table} ($CGI->{mv_data_fields})  
VALUES("};
			$sql .= join '","', @$values;
			$sql .= q{")};
			$dbh->do($sql) || return 'Error';
			return $values->[0];
		};
		when ('update') {
			my $return_value;
			my $sql = qq{UPDATE $CGI->{mv_data_table} SET };
			for (split ',', $CGI->{mv_data_fields}) {
				$return_value ||= $CGI->{$_};
				$sql .= qq{$_="$CGI->{$_}", };
			}
			$sql =~ s/, $//;
			$sql .= qq{ WHERE $CGI->{mv_data_key}=$CGI->{$CGI->{mv_data_key}}};
			$dbh->do($sql) || return 'Error';
			return $return_value;
		};
		when ('delete') {
			my $sql = qq{DELETE FROM $CGI->{mv_data_table} WHERE $CGI- 
 >{mv_data_key}=$CGI->{$CGI->{mv_data_key}}};
			$dbh->do($sql);
			return;
		}
		when ('sort') {
			my $i = 0;
			for (@{$CGI_array->{$CGI->{sortable_key}}}) {
				my $sql = qq{UPDATE $CGI->{mv_data_table} SET $CGI- 
 >{mv_data_sort_field}=$i WHERE $CGI->{mv_data_key}=$_};
				$dbh->do($sql);
				$i++;
			}
		};
		when ('select') {
			my $sql = qq{SELECT $CGI->{mv_data_fields} FROM $CGI- 
 >{mv_data_table} WHERE $CGI->{mv_data_key}=$CGI->{$CGI- 
 >{mv_data_key}}};
			return $dbh->selectrow_array($sql);
		};
	}
	return;
	
	sub get_values {
		my $fields = shift;
		my $data;
		for (split ',', $fields) {
			push @$data, $CGI->{$_};
		}
		return $data;
	}
[/perl]

Good luck.

Bill Carr
Bottlenose - Wine & Spirits eBusiness Specialists
(413) 584-0400
http://www.bottlenose-wine.com