Embperl - building dynamic websites with Perl

• Plain DBI
• DBIx::Recordset
• Search Example
• Search sets up a Recordset object
• Data can accessed as array or via the current record
• Fields can be accessed by name
• PrevNextForm generates no/one/two buttons depending if there are more records to display
• As for Search there are methods for Insert/Update/Delete
• Database table can also tied to a hash

This is another example of using plain DBI within Embperl. In opposition to the example I gave in the chapter about dynamic tables, this example works with explicit loops.

 [-
 # connect to database
 $dbh = DBI->connect($DSN) ;
 # prepare the sql select
 $sth = $dbh -> prepare ("SELECT * from $table") ;

 # execute the query
 $sth -> execute ;

 # get the fieldnames for the heading in $head
 $head = $sth -> {NAME} ;
 -]

 <table>
    <tr>
    [$ foreach $h (@$head) $]
        <th>[+ $h +]</th>
    [$ endforeach $]
    </tr>
    [$ while $dat = $sth -> fetchrow_arrayref $]
        <tr>
            [$ foreach $v (@$dat) $]
                <td>[+ $v +]</td>
            [$ endforeach $]   
        </tr>
    [$ endwhile $]
 </table>

DBIx::Recordset is a module for easy database access.

 [-*set = DBIx::Recordset -> Search ({%fdat,
				     ('!DataSource'   => $DSN,
				      '!Table' => $table,
				      '$max'   => 5,)}) ; -]
 <table>
  <tr><th>ID</th><th>NAME</th></tr>
  <tr>
    <td>[+ $set[$row]{id} +]</td>
    <td>[+ $set[$row]{name} +]</td>
  </tr>
 </table>
 [+ $set -> PrevNextForm ('Previous Records',
			  'Next Records',
			  \%fdat) +]

Search will take the values from %fdat and use them to build a SQL WHERE expression. This way, what you search for depends on what is posted to the document. For example, if you request the document with http://host/mydoc.html?id=5 the above example will display all database records where the field 'id' contains the value 5.

The result of the query can be accessed as an array (this does not mean that the whole array is actually fetched from the database). Alternative, you can directly access the current record just by accessing the fields.

    set[5]{id}	 access the field 'id' of the sixth found record
    set{id}      access the field 'id' of the current record

While normal DBI let you access your data by column numbers, DBIx::Recordset uses the field names. This makes your program easier to write, more verbose and independent of database changes.

The PrevNextButtons function can be used to generate button for showing the previous record or the next records. PrevNextButton generates a small form and includes all necessary data as hidden fields. To get it to work, it's enough to feed this data to the next request to Search.

Example for Insert

If %fdat contains the data for the new record, the following code will insert a new record into the database.

 [-*set = DBIx::Recordset -> Insert ({%fdat,
				      ('!DataSource'   => $DSN,
				       '!Table' => $table)}) ; -]

DBIx::Recordset can also tie a database table to a hash. You need to specify a primary key for the table, which is used as key in the hash.

    $set{5}{name}    access the name with the id=5
		     (id is primary key)