Embperl - building dynamic websites with Perl

Database access
[ << Prev: Debugging ] [ Content ] [ Next: Security >> ]

Plain DBItop

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} ;

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


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

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

Search sets up a Recordset objecttop

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.

Data can accessed as array or via the current recordtop

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

Fields can be accessed by nametop

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.

PrevNextForm generates no/one/two buttons depending if there are more records to displaytop

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.

As for Search there are methods for Insert/Update/Deletetop

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)}) ; -]

Database table can also tied to a hashtop

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)

[ << Prev: Debugging ] [ Content ] [ Next: Security >> ]

© 1997-2023 Gerald Richter / actevy