If you wanted to use a naming convention like a conventional spreadsheet (A1 - A9, B1 - B9, etc.) then you could try something like so:

my ($row,$col) = ('a',0);
my $total_cols = $table->get_col_count();

$table->map_cell( sub {
   my $old = shift;

   $col++;
   my $new = qq|<input type="text" name="_$row$col" value="$old">|;

   $row++ and $col = 0 unless $col % $total_cols;
   return $new;
});
[download]

After the form has been submitted, you can pick apart the rows from the cols by running each element from CGI.pm's param() method through a regex. The _ at the beginning of each name is used to seperate the 'spreadsheet' <input>'s from the other form elements. Not necessarily the best way, but it works.

At the time the idea of creating a spreadsheet out of my module was presented to me by Jim Cromie, I thought my above solution was pretty slick - for about 3 seconds. Then I realized that I don't have a corresponding B52 or A1 in my database, and hard coding some kind of mapping between database rows and columns to spreadsheet rows and columns is rather tedious and well, silly.

In order to put the proper information into the name, that is, in order to be able to know which row to update with what, I added two new methods that are meant to be used inside the map_cell() callback:

• get_current_row() - primary key of row being processed
• get_current_col() - name of current column processed

That should do it. Now each individual <input> tag can be identified by a primary key and a column name. But . . . . }:(

DBIx::XHTML_Table version 0.95 prints out every column you select, and displaying primary keys so prominently (and allowing the user to CHANGE them!!) just isn't good. Alas, time to add another method, but this new method can do one of two things:

1. allow a user to say "hey! i know i said select these 5 columns, but i really only wanted to see 3 of them"
2. allow the user to say "hey! this here column is really the primary key - don't display it, but do something special with it in the background instead"

Well, I chose the second option for better or worse. My reasoning is that all of this extra stuff is just for the new 'spreadsheet' feature (and any thing new to be concocted from the beauty of callbacks), and if the user doesn't want to see a column - then don't select it!

The new method is set_pk(), and you send it the name of your primary key column (the next version will accept no arg and default to 'id'). By doing so, you mark that column as the primary key and it is not displayed with the other columns. The cool part is that each row can be tagged with the primary key value, which means we can now know which database row our spreadsheet row corresponds with.

The result is the following script - replace <INSERT_TABLE_NAME> with the desired database table name, replace <INSERT_PK_NAME> and supply the proper DB connection credentials and it should work without a hitch. Don't forget the 'shebang' line if you are on *nix.

---

Receipe #42 Code

use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);

use DBI;
use DBIx::XHTML_Table;

print header(), start_form();

my $DBH = DBI->connect(
   qw(DB CONNECT INFO HERE),
   {RaiseError => 1, AutoCommit => 1}
);

if (param('go')) {
   my %hash;
   foreach (param()) {
      my ($pk,$field) = $_ =~ /_(\d+)=(\w+)/;
      next unless $pk;
      $hash{$pk}->{$field} = param($_);
   }

   while (my($pk,$row) = each %hash) {
      $DBH->prepare(
         'update <INSERT_TABLE_NAME> set ' . 
         join(',', map {"$_=?"} keys %$row) . 
         ' where id=?'
      )->execute((values %$row),$pk);
   }
}

my $table = DBIx::XHTML_Table->new($DBH) or die 'whoops';

$table->set_pk(<INSERT_PK_NAME>);
$table->exec_query('select * from <INSERT_TABLE_NAME>');

$table->map_cell(sub {
   my $datum = shift;
   my $row   = $table->get_current_row();
   my $col   = $table->get_current_col();
   return qq|<input type="text" name="_$row=$col" value="$datum">|;
});

print $table->output(), submit(-name=>"go"), 
   end_form(), end_html();

$DBH->disconnect();
[download]

---

The first thing is to connect to your database via DBI (not DBIx::XHTML_Table) so you can re-use the handle. (and yes, Apache::DBI would be much better!).

Next is a simple check to see if the form was submitted. If not, then a new DBIx::XHTML_Table object is created, modified, and displayed. Nothing new here, except the naming convention i chose for each input - an _ to sererate it from the other form controls, and an = sign to delimit $row from $col so i can split it later. It's the 'was submitted' part that's tricky:

The foreach loop munges the params into a data structure. To paint a picture: (and please forgive my blantent abuse of the 2nd normal form, by the way)

param() = ( '_926=title', '_926=artist', '_926=album', '_926=year', '_926=genre', '_927=title', '_927=artist', '_927=album', '_927=year', '_927=genre', 'Submit Button' ); [download]

becomes:

%hash = ( '926' => { 'artist' => 'Rush', 'genre' => 'rock', 'album' => 'Fly By Night', 'title' => 'Making Memories', 'year' => '1975' }, '927' => { 'artist' => 'Rush', 'genre' => 'rock', 'album' => 'Fly By Night', 'title' => 'Anthem', 'year' => '1975' } ); [download]

...which is what the while loop uses to actually create the SQL update statement. Using bind vars and ? is good for portability among different databases, by the way. (big thanks to maverick for reminding me)

The next big thing that this puppy needs is CACHING!! Every row is updated, even the ones that didn't change - if the user only changes one cell, then every row that was selected will be updated. Ughh. I'll leave caching as an 'exercise for the reader' (aka 'pass the buck'). I also did not address the issue of joins, but in this case I was aiming for simplicity. Besides, that's receipe #43 ...

Conclusion

Is this the next best thing since slice bread? Is this the be-all end-all way to to make a spreadsheet app? Of course not - it's just another Cool Use For Perl. ;)

jeffa