Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Logical question-to upate DB records

by thisisperl (Novice)
on Jun 01, 2004 at 18:02 UTC ( [id://358327]=perlquestion: print w/replies, xml ) Need Help??

thisisperl has asked for the wisdom of the Perl Monks concerning the following question:

I am displaying records from a DB using HTML:Template:
my $rows = $dbh->selectall_arrayref($SQL,{Columns =>{}}) ; $template->param(ROWS => $rows || []);
Fields that can be modified are displayed as text boxes. In addition, next to each row, an update button is also displayed. So a user could modify the value in a text box and click the Update button for that row. eg:
<!-- TMPL_LOOP NAME=ROWS --> <tr> . . . <td> <input type="text" value = "<!-- TMPL_VAR NAME=Detail -->" size="43" n +ame="Detail"> </td> <td> <input type="submit" value="Update"> </td> </tr> <!-- /TMPL_LOOP -->
If 5 rows are displayed, there would be 5 Update buttons-one for each row. What is happening is that the Update button works only for the first row. How can I associate each Update button with the corressponding row? Here's the Perl script called when the Update button is clicked(scaled down version):
$ticket = $query->param('TicketNo'); $detail = $query->param('Detail'); . . . my $temp1 = "UPDATE TSIssuesTable "; my $temp2 = "SET Detail = ?"; my $temp3 = "WHERE "; my $temp4 = "TicketNo = $ticket"; $SQL = $temp1.$temp2.$temp3.$temp4; $sth1 = $dbh->prepare( $SQL ); $sth1->execute($details); ...
Thanks!

Replies are listed 'Best First'.
Re: Logical question-to upate DB records
by etcshadow (Priest) on Jun 01, 2004 at 18:25 UTC
    Well, you'd need to either tie the inputs to the buttons, somehow... so that your html comes out something like:
    <input type=text name=Detail1 ...> <input type=submit name=Submit1 value=Update> ... <input type=text name=Detail2 ...> <input type=submit name=Submit2 value=Update>
    etc. And then check (in the code that receives the submit) which of the submit buttons was pressed... And then proceed accordingly (looking for Detail$x in the form data if Submit$x is also in the form data).

    --OR--

    Just wrap each pair of input/submit-button in its own <form> tag. This, however, may not be feasible, depending on the constraints of what else you are trying to do with this page.

    ------------ :Wq Not an editor command: Wq
Re: Logical question-to upate DB records
by gowen (Initiate) on Jun 01, 2004 at 20:09 UTC

    It would be helpful to see a couple of rows as rendered into HTML. However, just looking at what I see here, it looks like you're creating a form with a large number of text inputs named "Detail" and a large number of submit buttons. Only one "Detail" will make it to the form processor; you can't overload text inputs with multiple options like you can with, say, checkboxes.

    You can, however, differentiate between "Submit" buttons using their name attribute. Another way to do what you're trying to do would be something like this:

    <td> <!-- TMPL_VAR NAME=Detail --> </td> <td> <input type="submit" name=<!-- TMPL_VAR NAME=Detail --> value="Update" +> </td>
    That way, whichever "Update" button gets clicked, you'll end up with a variable named "Whatever" set to "Update" (where "Whatever" was the Detail variable). So you can, for example,
    foreach ($query->param) { if ($query->param($_) eq "Update") { # If $Detail was "red", we'll change to red &change_Color($_); last; } }
    HTH
      Only one "Detail" will make it to the form processor; you can't overload text inputs with multiple options like you can with, say, checkboxes.
      Um, really? Are you sure of this? I was under the impression that "overloading", as you put it, was purely a function of the CGI module, that the html would just send a bunch of foo=bar;foo=baz;foo=qux in the querystatement then the module would handle collapsing it?
        I was under the impression that "overloading", as you put it, was purely a function of the CGI module, that the html would just send a bunch of foo=bar;foo=baz;foo=qux in the querystatement then the module would handle collapsing it?

        You are correct - the actual HTTP submission by the browser will contain the repeated entries even for text inputs and the like. I wouldn't recommend relying on that, though, because it forces you to handle bottom-up form processing (including handling the URL encoding and crap like that). Even if you don't mind today, you'll be in a hole when you decide to just use CGI.pm or whatever tomorrow because it has that one added feature you'd like.

        I speak as someone who once tried to save some time doing this and found it wasn't worth it in the end 8)

      Thanks. Yeah I already made changes similar to the ones you suggested-I have modified the design now and have multiple checkboxes and a single Update button.


      The changes I made:

      -I am now using CGI to retrieve "arrays of values"
      -The form now has an update column with check boxes and a single update button for all rows.
      -The value of update check box is set to TicketNo, which is my primary autonumber key so the value tells me which records need to be updated:
      In the template:
      ...... <td> <input type="checkbox" name="Update" value="<!-- TMPL_VAR NAME=TicketN +o -->"></td> <td> <!-- TMPL_VAR NAME=TicketNo --></td> <td> <!-- TMPL_VAR NAME=Customer --></td> <td> <!-- TMPL_VAR NAME=UserName --></td> .... <td> <input type="text" value = "<!-- TMPL_VAR NAME=AssignTo -->" name= +"Detail" size="11"></td> .....

      So the template will display records returned from the DB-the user can modify the fields displayed in a text box. (mulitple modifiable fields per record)
      In the script:
      ..... @update = $query->param('Update'); @detail = $query->param('Detail'); ........... print "@update\n"; #Returns the ticket numbers for records that need t +o be updated print "@detail\n";#Returns details for ALL displayed records

      Sample output is:
      @update prints 40001 40003 40006 40007
      @detail prints detail1 detail2 detail3 detail4 detail5 detail6 detail7

      The question now:
      Given two arrays of different lengths, I cannot use the same array index to access corresponding elements from the two arrays.

      So how do I associate the right elements (40003 with detail3 and not detail 2)?
        You could use your TicketNo to modify the name of your value so you have a unique key to search for.
        #!/usr/bin/perl -w use strict; =cut XHTML <td><input type="checkbox" name="update" value="<TMPL_VAR NAME=TicketN +o>" /></td> <td><!-- TMPL_VAR NAME=TicketNo --></td> <td><!-- TMPL_VAR NAME=Customer --></td> <td><!-- TMPL_VAR NAME=UserName --></td> .... <td><input type="text" name="detail_<TMPL_VAR NAME=TicketNo>" value="< +TMPL_VAR NAME=AssignTo>" size="11" /></td> =cut ... my @update = $query->param('update'); foreach my $ticket_no (@update) { my $assign_to = $query->param('detail_'.$ticket_no); next unless defined $assign_to; # do something with $ticket_no => $assign_to; print qq|assign ticket number $ticket_no to $assign_to \n|; }

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://358327]
Approved by Happy-the-monk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (6)
As of 2024-03-28 23:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found