How to get table values from template to subroutine?

Question

I have one table with table_id="mytable"

i need to send all the rows of the table in the action subroutine after clicking on submit. In the subroutine, I need to insert the rows one by one into a table after modifying some values.

What is the easiest way to do this?

---

My real template:

<form method="post" action="/savesmop" >
<section class="wrapper">
  <h1>Select Items and add components</h1>
  <ul class="tabs">
<% FOREACH Component_Id IN components.keys.nsort %>
    <li><a href="#tab<% components.$Component_Id.Component_Id %>"><% components.$Component_Id.Component_Name %></a></li>
<% END %>
  </ul>
  <div class="clr"></div>
  <section class="block">
<% FOREACH Component_Id IN **components.keys.nsort** %>
    <article id="tab<% components.$Component_Id.Component_Id %>">
 <% FOREACH ACTIVITY_ID IN activities.keys.nsort %>
  <% IF activities.$ACTIVITY_ID.Component_Id == components.$Component_Id.Component_Id  %>
         <input class="toggle-box" id="t<% activities.$ACTIVITY_ID.ACTIVITY_ID %>" name="t<% activities.$ACTIVITY_ID.ACTIVITY_ID %>" type="checkbox" >
   <label for="t<% activities.$ACTIVITY_ID.ACTIVITY_ID %>"><% activities.$ACTIVITY_ID.ACTIVITY_NAME %>
     <input type="button" class="btnsmall1" onclick="addRow('<% activities.$ACTIVITY_ID.ACTIVITY_ID %>')" value="+" />
              <input type="button" class="btnsmall2" onclick="deleteRow('<% activities.$ACTIVITY_ID.ACTIVITY_ID %>')" value="X" />
   </label>
   <div>
   
   <table id="<% activities.$ACTIVITY_ID.ACTIVITY_ID %>" name="<% activities.$ACTIVITY_ID.ACTIVITY_ID %>">
   <tr><td><input type="checkbox" name="chk"/></td>
   <% FOREACH ATTRIBUTE_ID IN attributes.keys.nsort %>
    <% IF attributes.$ATTRIBUTE_ID.ACTIVITY_ID == activities.$ACTIVITY_ID.ACTIVITY_ID %>
     
     <td>
     <% IF attributes.$ATTRIBUTE_ID.ATTRIBUTE_NAME == 'Object Type' %>
     <select id="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_ID %>" name="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_ID %>">
     <option value="" disabled selected>Select Object Type</option>
     <option value="TABLE">TABLE</option>
     <option value="VIEW">VIEW</option>
     <option value="OTHER">OTHER</option>
     </select>
     <% ELSIF attributes.$ATTRIBUTE_ID.ATTRIBUTE_NAME == 'SVN Path' %>
     <input size="90" type="TEXT" placeholder="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_NAME %>" id="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_ID %>" name="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_ID %>"/>
     <% ELSE %>
     <input type="TEXT" placeholder="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_NAME %>" id="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_ID %>" name="<% attributes.$ATTRIBUTE_ID.ATTRIBUTE_ID %>"/>
     <% END %>
     </td>
    <% END %>
   <% END %>
   </tr>
   </table>
   </div>
   <br>
  <% END %>
 <% END %>
     </article>
<% END %>
</section>
</section>

<input class="btn2" type="submit" value="SAVE" />

</form>

There are 3 hashes i am selecting from 3 different database tables, and looping through them to dynamically generate this template.

As you can see there are multiple tables in each list item and number of columns are also different in each table. What I did in the javascript is to clone the 1st row of the table while adding one row. I could not figure out how to increment the name tag of each text input by 1 while adding a new row in the javascript addrow function.

Below is my javascript function:

function deleteRow(tableID) 
    { 
        try
        { 
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;

            for(var i=0; i<rowCount; i++) 
            {

                var row = table.rows[i];
                var chkbox = row.cells[0].childNodes[0];

                if(null != chkbox && true == chkbox.checked)
                {
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }
            }
        }
        catch(e){alert(e);}
    }

function addRow(tableID) 
    {

      var table = document.getElementById(tableID); // find table to append to

      var row = document.getElementById(tableID).rows.item(0); // find row to copy

      var clone = row.cloneNode(true); // copy children too
      clone.id = "newID"; // change id or other attributes/contents
      table.appendChild(clone); // add new row to end of table
       }

Answer 1

Since you are not saying which Dancer version you are using, I will assume you are using the first version of Dancer (and not Dancer2).

It looks like you are unsure about how forms work when they are submitted via GET or POST in general. That's not specific to Dancer, but very generic to how the HTTP protocol works, and tied to HTML.

If there is a form in your HTML document, the form elements like <input> and <textarea> need to have the name attribute. That is what will go into the request. The id attribute is only for CSS and JS (this is simplified).

<form action="/save" method="GET">
  <input type="text" name="foo" id="input-foo" />
  <input type="submit" value="save" />
</form>

If you enter bar and hit that button, your browser will navigate to:

/save?foo=bar

If there is no name attribute on an <input>, it will be ignored by the browser.

<form action="/save" method="GET">
  <input type="text" id="input-foo" />
  <input type="submit" value="save" />
</form>

This will lead you to:

/save

That is also useful if you don't want the value of your submit button to show up. Just don't give it a name attribute.

Ok, now you have multiple rows of input fields. Those are dynamic. You assigned integer numbers as ids for the <table> and the <input> elements. That is weird, but should not be a problem. But there are no name attributes.

You should give each <input> a distinct name. For example, use names related to your placeholders.

<table id="101">
    <tr>
        <td><input type="checkbox" name="chk" /></td>
        <td><input type="text" placeholder="Database Name" name="dbname-1" /></td>
        <td><input type="text" placeholder="Database Appx Size" id="appx-1" /></td>
        <td><input type="text" placeholder="Rate of growth" id="growth-1" /></td>
    </tr>
</table>

Now there is one row with three distinct names: dbname-1, appx-1 and growth-1.

Let's look at the JavaScript to add new rows. I'm not going to show it here, but what it should do is include the name attribute and increment the numbers. The next row should have these names: dbname-2, appx-2 and growth-2. You get the pattern here.

Finally, we need to do stuff in the Perl code. We'll get all the parameters, filter them down to only the three field-types we are interested in, and then iterate those to insert them. We'll also make sure they are not empty.

post '/savesmop' => sub {

    # get all the parameters
    my %values = params;

    # remove unwanted ones that don't belong to the rows
    foreach my $key ( keys %values ) {
        delete $values{$key} unless $key =~ m/^(?:dbname|appx|growth)-/;
    }

    my $dbh = DBI->connect( 'dbid', 'user', 'pwd' ) or die $DBI::errstr;
    my $sth = $dbh->prepare("INSERT INTO TABLE_Fact VALUES (?,?,?)") or die $dbh->errstr;

    # iterate over the dbnames only
    foreach my $dbname ( grep {m/^dbname/} sort keys %values ) {

        # field should not be empty
        next unless $values{$dbname};

        # fetch the number from the dbname...
        ( undef, my $id ) = split /-/, $dbname;

        # ... and the other two should exist and not be empty also
        next unless my $appx = $values{ 'appx-' . $id };
        next unless my $pwd  = $values{ 'pwd-' . $id };

        # now insert
        $sth->execute( @values{ $dbname, $appx, $pwd } ) or die $dbh->errstr;
    }
    template "savesmop";

};

The point about this is that you do not need to know how many there are. A while loop is not the best approach to do insert stuff into a database. Ususally you iterate data with a for loop. Perl's foreach my $item (@list) syntax is especially useful, because you do not have to care how long the array/list is.

---

A few side-nodes:

• Have you considered using DBIx::Class? There is a good plugin for that called Dancer::Plugin::DBIC that makes it easy to use in your Dancer app. DBIx::Class has some learning curve, but it is worth it!
• If you are intimidated by an object relation mapper or think it's overkill, take a look at Dancer::Plugin::Database instead. It helps abstract the $dbh away and you don't need to care about connecting.

• If you don't want plugins at all (which would not be a smart decision, it's always nice if others have done the work and it is well-tested!), at least don't make a fresh connection to the db in your route handler. Dancer runs persistently (unless you run it as a CGI) so it will benefit from keeping the connection open. Connect to the DB at the top of the application code and keep it in the config. It's weird, but more efficient.

  use Dancer;
  use DBI;
  
  config 'dbh' => DBI->connect('...') or die $DBH::errstr;
  
  get '/foo' => sub {
    config->dbh->prepare('select ...');
  }