Home : Introduction : DBIx::Recordset
Google Web perl.apache.org

Embperl 2 Advanced
More infos
Add info about Embperl

    Stable 2.4.0
    Beta 2.5.0_3
Support the development of Embperl! More...
Multiple tables
[ << Prev: Basic Example ] [ Content ] [ Next: Modify the Database >> ]

Until now, we only have worked with one table. In real life, you often have to deal with mulitple tables. For this reason, DBIx::Recordset helps you to reduce the expense associated with dealing with multiple tables. The simplest way to do this is to use the parameters !TabJoin and !TabRelation to tell DBIx::Recordset to create an SQL-join between two or more tables. This will link the tables together and the result looks just like one great table.

More interesting is the possibility to create "links". As an example, we'll take the same table we used above and divide it into two tables: one table for the names and one table for the towns. As a link we add an id-field. If the fields are following some naming convention, DBIx::Recordset is able to find this link automatically. If fields are named in another way, you have to tell DBIx::Recordset manually how the tables belong together.

  Table name:	    firstname, name, town_id
  Table town:	    id, town

Here, every name has exactly one town and every town has a number of names assigned. With a simple modification of our first example, we could get the same result as above (except that we are now dealing with two tables instead of one):

  use DBIx::Recordset ;
  $db = DBIx::Database -> new ('dbi:mysql:test') ;
  $db -> TableAttr ('town', '!NameField', 'town') ;

  $fdat{'!DataSource'} = $db ;
  $fdat{'!LinkName'} = 3 ;
  *set = DBIx::Recordset -> Search(\%fdat) ;
  $names = $set -> Names ;

And the request would be:



The new thing here is the DBIx::Database object. It gathers meta-information about the database and stores it for later use. Because of the names of the fields the object can detect that the field town_id in the table name points to field id in the table town. Additionally, we tell the DBIx::Database object which column(s) contain the human-readable name of the table town. These initialisations only have to be executed once. If you use mod_perl, for example, you should be able to move these lines into a common startup file.

Also new is the parameter !LinkName. It tells DBIx::Recordset to return the human-readable name (in our example, town) instead of the field which links the two tables together (town_id in our example). Internally, DBIx::Recordset generates an SQL-join, so there is only one SELECT command necessary and the result is just the same as in the last example.


But what to do if we have the id of a town and want to display all the names that belongs to it? An Embperl-page that does this job might look something like this:

<body> [- use DBIx::Recordset ; $fdat{'!DataSource'} = 'dbi:mysql:test' ; $fdat{'!Table'} = 'town' ; *set = DBIx::Recordset -> Search(\%fdat) ; -]

  town: [+ $set{town} +]<br>
      [- $rec = $set{-name}[$row] -]
      <td>[+ $rec -> {name} +]</td><td>[+ $rec -> {firstname} +]</td>

A request to that page might look like this:


In this example, we specify the name of the table directly inside the page, so it can't be overwritten from outside. The call to Search returns the town for the given query parameters. In our example, it will select the town with the id 5. The command [+ $set{town} +] shows the value of the field town in the current record. After the call to Search, this is the first selected record. Next, we need to display all the names. This is very easy using the special field -name. -name contains a sub-object for the table name. The query parameters for this sub-object are set by DBIx::Recordset in such a way that it contains all names which meet the link-condition. We just wrap it in a table and we are already done.

[ << Prev: Basic Example ] [ Content ] [ Next: Modify the Database >> ]

© 1997-2012 Gerald Richter / ecos gmbh