[cgiapp] Re: Proper Way To Load Template Params

[Mark Stosberg]

On Sun, 15 Mar 2009 20:33:52 -0700
adam at spatialsystems.org wrote:

> My template is getting data via a SQL query.  There is exactly one row
> retrieved.
>  
> Currently I use the below method to show all records using a loop:
> $template->param(
>   RESULTS => $self->dbh->selectall_arrayref('
>     SELECT age, day FROM table WHERE id = ?',
>     { Slice => {} },
>     $self->session->param('cell')->{'sid'} )
> );
> 
> 
> Template:
> <TMPL_LOOP NAME="RESULTS">
>   <TMPL_VAR NAME="age"><TMPL_VAR NAME="day">
> </TMPL_LOOP>

This is not directly related, but you may want to look at DBIx::Simple
in combination with its support for DBIx::Interp as a simpler, interface
over top of raw DBI.

> But how should I load the template if I have exactly one row returned? 
> I could fudge it and use the loop to iterate of the one row, but I think
> that may be a sub optimal method.

> $self->dbh->selectall_arrayref('
>     SELECT age, day FROM table WHERE id = ?',
>     { Slice => {} },
>     $self->session->param('cell')->{'sid'} )

To get one row back, use "selectrow_hashref", it will return a single hashref.
( no "Slice" needed. )

You then can skip the loop tokens in a template:

   <TMPL_VAR NAME="age"><TMPL_VAR NAME="day">

To get an idea of what this would look like with
DBIx::Simple/SQL::Interp:

$result = $db->iquery("SELECT age, day FROM table WHERE id = ",\$sid);

# For an arrayref of hashrefs, for mutliple results
$LoH = $result->hashes;

# Or for a single result, a hash reference:

$row = $result->hash;

I have not yet converted my major application over to this style ( I
still use raw DBI, too), but I plan to.

    Mark

-- 
 . . . . . . . . . . . . . . . . . . . . . . . . . . . 
   Mark Stosberg            Principal Developer  
   mark at summersault.com     Summersault, LLC     
   765-939-9301 ext 202     database driven websites
 . . . . . http://www.summersault.com/ . . . . . . . .