Writing Apache Modules with Perl and C

F.6. Querying a Database

Often it's necessary to query a database when generating a dynamic page. We have already seen in our discussion of dynamic tables how this can be done using the DBI database interface. Since the tasks needed in a web page are often the same, there is a module called DBIx::Recordset, which simplifies commonly needed tasks:

[-*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) +]

The Search() method in this example 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). Alternatively, you can directly access the current record just by accessing the fields, as shown here:

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 lets you access your data by column numbers, DBIx::Recordset uses the field names. This makes your program easier to write, more ...