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

Please forgive the double post, this is my first time here.

I have a database that tracks CD's (when they were made, shipped, who currently has them, etc.) I am trying to create a form that will let users update multiple records from one page. I want to return all the records in a range in table format with the current value of the column being the default for the textfield. I need to update all the records with one submit button.

EXAMPLE:

db 

cd col1 col2
1   A    NULL
2   B    C

[download]

form 

cd  col1                       col2
1   $q->textfield (-default=>A) $q->textfield (-default=>"")</br><br>2
+   $q->textfield (-default=>B) $q->textfield (-default=>C)

[download]

Your help is greatly appreciated!

JLD

Replies are listed 'Best First'.
Re: muli-records db update using cgi
by neniro (Priest) on Apr 17, 2004 at 21:49 UTC
    Please give us some more information.
  • Is your Database a flatfile, or based on MySQL or a similar RDBMS?
  • Do you use DBI to access your database?
  • Explain your Example, using more describing fieldinformation.
[reply]
      It is an oracle database that I access throught DBI and DBD::oracle.
      What I am trying to do is retrieve multiple records from the database for updating. I want the existing values in the database to be the default values in the respective textfields (and blank if the db value is null). I want to display the records in a table format so that each row is a different record, and each column is an attribute in the database.
      The database contains (for example) the following fields: cd_vol_num, ship_date, tracking_num, notes.
      If the user wants to update the notes of 10 cd's I want the form to print a table that has 10 rows and 4 columns.
      The cd_vol_num is static so it is just text. The other fields can be updated so I want them to appear as textfields. I was trying to do this using an array of hashes.
      If the 4th cd in the series already has notes I want those be the default value for that cell. So that they appear when the page is rendered. This way the user can append to the notes or remove the previous note. I need the form to only have one submit button so that when pressed all the fields will be updated in the database

      Thanks for taking a look at this.
      JLD
[reply]
        I suggest you to use HTML::Template and build your editable table like this (untested): 
        <form>
<TMPL_LOOP NAME=entries>
<table id="CD" class="default">
  <tr class="grey">
    <td class="small">ID:</td>
    <td><TMPL_VAR NAME=id></td>

    <td class="small">Title:</td>
    <td>
      <input type="Text" name="title" value="<TMPL_VAR NAME=title>" />
    </td>

    <td class="small">Comment:</td>
    <td>
      <input type="Text" name="comment" value="<TMPL_VAR NAME=comment>
+" />
    </td>
  </tr>
</table>
<br />
</TMPL_LOOP>
<input type="Submit" name="Update" value="Update">
<input type="reset">
</form>

        [download]
        If your script is called with an action 'find', you query your database and fill the table. If the action is 'update' you use the params and update-query the db.
        neniro
[reply]
[d/l]
        Note that while your project will have no trouble with 10 CDs, it will become unwieldly with 100 CDs, and completely useless with 500.

        You need to think of ways to limit the page size (by displaying only some of the CDs at the same time). You might find Data::Page helpfull in this regard...

[reply]
Re: multi-record db update using cgi
by matija (Priest) on Apr 18, 2004 at 04:40 UTC
    Use the -name=> parameter of the textfield routine. Name each parameter in such a way, that you can easily deduce which column and which CD it belongs to.

    For instance, you could use:

    $q->textfield (-default=>A,-name=>"title_$cdnum"),$q->textfield (-defa
+ult=>""-name=>"artist_$cdnum")

    [download]
    Then, as you iterate through param(), you can determine for each field where it belongs, and process it accordingly.
[reply]
[d/l]
[select]
Re: multi-record db update using cgi
by matija (Priest) on Apr 17, 2004 at 19:53 UTC
    Well, you need a way to distinguish the textfields from different rows and columns, right?

    I suggest you use the -name=> to textfield, and use a name you can easily divide up into parts, like rowname_cdnumber (example: col1_cd7)

[reply]
[d/l]

Back to Seekers of Perl Wisdom