|
Up to this point, we have only discussed the retrieval and display of data.
But of course it's also possible to modify data. The simplest way to do this
is to assign new values to the result of a Search call. For example, you
may write $set{town} = 'Frankfurt' to change the name of the town.
DBIx::Recordset converts this into a vaild SQL-Update-command. While this is very useful in normal Perl scripts, you probably won't use it very
often in a cgi script.
The methods Insert/Update/Delete will probably prove more useful.
Just like Search, these directly accept query parameters posted to the page.
The method Execute combines all four of these together,
making it possible to control the type of action via the CGI-parameters. Here is an example: <html>
<head>
<title>Database Access with HTML::Embperl and DBIx::Recordset</title>
</head>
<body> [-
### Database-parameter ###
use DBIx::Recordset ;
$fdat{'!DataSource'} = 'dbi:mysql:test' ;
$fdat{'!Table'} ||= 'town' ;
$fdat{'!PrimKey'} = 'id' ;
$fdat{'$max'} = 10 ;
### Execute action according to the query parameters ###
*set = DBIx::Recordset -> Execute (\%fdat) ;
-]
[$if $DBI::errstr $]
<h1>Database Error [+ $DBI::errstr +]</h1>
[$else$]
[-$names = $set -> AllNames ; -]
[$if $set[0] && $set -> MoreRecords $]
[### We found more then one record ###]
[### -> display as a table ###]
<table>
<tr> [### Display header ###]
<th>[+ ucfirst ($names -> [$col]) +]</th>
</tr>
<tr> [### Display record -> Table will be expanded by Embperl ###]
[- $rec = $set[$row] -]
<td>
[- $name = $names -> [$col] -]
[$if $name eq $fdat{'!PrimKey'} $]
[### Generate HTML link to edit this record ###]
<a href="example4.htm?!Table=[+ $fdat{'!Table'} +]&[+ $fdat{'!PrimKey'} +]=[+ $rec ->{$fdat{'!PrimKey'}} +]">[+ $rec -> {$name} +]</a>
[$elsif $set -> Link4Field($name) $]
[### Link to other table -> generate HTML link ###]
[- $link = $set -> Link($set -> Link4Field($name)) -]
<a href="example4.htm?!Table=[+ $link -> {'!Table'} +]&[+ $link -> {'!LinkedField'} +]=[+ $rec -> {$link -> {'!MainField'}} +]">[+$rec -> {$name}+]</a>
[$else$]
[### Display contents of field ###]
[+ $rec -> {$names->[$col]} +]
[$endif$]
</td>
</tr>
</table>
[+ $set -> PrevNextForm ('\<\<Prev', 'Next\>\>', \%fdat) +]
<hr>
<a href="example4.htm?!Table=[+ $fdat{'!Table'} +]&%3dempty=1">Search</a> record in table '[+ $fdat{'!Table'} +]'
[$else$]
[### We found no/one record(s) ###]
[### -> Display form ###]
<form>
<table>
<tr>
[- $name = $names -> [$row] -]
<td> [### Display fieldname ###]
[+ ucfirst ($name) +]
</td>
<td> [### Display content of field ###]
<input type=text name="[+ $name +]" value="[+ $set{$name} +]">
[$if $set -> Link4Field($name) $]
[### Link to other table -> generate HTML link ###]
[- $link = $set -> Link($set -> Link4Field($name)) -]
<a href="example4.htm?!Table=[+ $link -> {'!Table'} +]&[+ $link -> {'!LinkedField'} +]=[+ $set{$link -> {'!MainField'}} +]">Show record from table '[+ $link -> {'!Table'} +]'</a>
[$endif$]
</td>
</tr>
</table>
[### Buttons for the different actions, the "name" attribute determinates ###]
[### which action should be taken ###]
<input type=submit name="=search" value="Search">
<input type=submit name="=empty" value="New">
<input type=submit name="=insert" value="Add">
<input type=submit name="=update" value="Update">
<input type=submit name="=delete" value="Delete">
<input type=hidden name="!Table" value="[+ $fdat{'!Table'} +]">
</form>
[$endif$]
[$endif$]
</body>
</html>When you first request this page, it will show the contents of the
preset table. Alternatively, you can supply a tablename with the
parameter !TC<able>. The link, which is shown at the bottom of the
page, leads you to an imput form. There, you can fill in one or
more fields and press the Search button. This invokes the page
itself and Execute will be instructed by the parameter =sC<earch>
(Name of the button "Search") to retrieve all records which match
the entered values. If the query finds more then one record, a table with all records found
will be shown. If there are more records than specified by the parameter $max, only
$max records are displayed. If this is the case, the PrevNextForm method
adds a "Previous" and a "Next" button to the page, allowing you to browse
through the whole table.
In the example above, we assume that every table has a primary key,
which is passed
to DBIx::Recordset by the line $fdat{'!PrimKey'} = 'id' ;. The column which
contains this primary key will be displayed as an HTML link containing the
parameters to execute a search for just this record. As you can see in
example4.htm, this can be used to display a form which includes some of
the data from the found record (see below).
Columns which are links to other tables will also be shown with an HTML-link. A click
on that link will open the linked table or record. If the search only selects one record, the same form is shown, but with the data from
the record filled in. Now it's possible to change the content. The changes are
written to the database when you press the button Update (parameter =update).
A new, empty form could be shown with the button New (parameter =empty) and if
you have written data into this empty form, you can add it as a new record with the
Add button (parameter =insert). Last but not least, there is a Delete button
(parameter =delete). In all of these cases, the content of the form is sent to the page
itself, and the Execute method at the start of the page executes the desired action. More comments can be found inside the source ([# #] blocks).
|
