View on
MetaCPAN is shutting down
For details read Perl NOC. After June 25th this page will redirect to
Adi Fairbank > CGI-Widget-DBI-Search-0.27 > CGI::Widget::DBI::Search



Annotate this POD


Open  1
View/Report Bugs
Module Version: 0.27   Source   Latest Release: CGI-Widget-DBI-Search-0.31


CGI::Widget::DBI::Search - Database search widget


  use CGI;
  use CGI::Widget::DBI::Search;

  my $q = CGI->new;
  my $ws = CGI::Widget::DBI::Search->new(q => $q);

  # database connection info
  $ws->{-dbi_connect_dsn} = 'DBI:Pg:dbname=my_pg_database;host=localhost';
  $ws->{-dbi_user} = 'pguser';
  $ws->{-dbi_pass} = 'pgpass';

  # what table to use in the SQL query FROM clause
  $ws->{-sql_table} = 'table1 t1 inner join table2 t2 using (key_col)';

  # optional WHERE clause
  $ws->{-where_clause} = 't1.filter = ? OR t2.filter != ?';
  # bind params needed for WHERE clause
  $ws->{-bind_params} = [ $filter, $inverse_filter ];

  # what columns to retrieve from query
  $ws->{-sql_retrieve_columns} =
    [ qw/ t2.long_description/, '(t1.price + t2.price) AS total_price'];
  # what columns to display in search results (with header name)
  $ws->{-display_columns} =
    { id => "ID", name => "Name", long_description => "Description", total_price => "Price" };

  $ws->{-numeric_columns} = { id => 1 };
  $ws->{-currency_columns} = { total_price => 1 };
  $ws->{-column_align} = { name => 'center' };

  #$ws->{-show_total_numresults} = 1; # set by default

  # execute database search

  # output search results to browser
  print $q->header;
  print $q->start_html;

  # show search results as HTML
  print $ws->display_results();

  print $q->end_html;


Encapsulates a DBI search in a Perl class, including all SQL statements required for performing the search, query results, HTML display methods, and multi-column, sortable result set displayed page-by-page (using HTML navigation links).



Creates and initializes a new CGI::Widget::DBI::Search object. Possible configuration options:

Database connection options
  -dbi_connect_dsn      => DBI data source name (full connection string)
  -dbi_user             => database username
  -dbi_pass             => database password
  -dbi_host             => host to connect to database (overridden by -dbi_connect_dsn)
  -sql_database         => database to connect to (overridden by -dbi_connect_dsn)
Database retrieval options
  -sql_table            => Database table(s) to query,
  -sql_table_columns    => [ARRAY] List of all columns in sql_table,
  -sql_retrieve_columns => [ARRAY] List of columns for retrieval,
  -sql_search_columns   => [ARRAY] (optional) List of columns to retrieve in
                           initial search, results to be saved to a temporary table.
                           (has no effect without -sql_join_for_dataset)
  -sql_join_for_dataset => (optional) SQL join clause which will be appended -
                           in the FROM clause - to the temporary table generated
                           with columns from -sql_search_columns.
                           (has no effect without -sql_search_columns)
  -opt_precols_sql      => Optional SQL code to insert between 'SELECT' and
                           columns to retrieve (-sql_retrieve_columns).
                           This is commonly something like 'DISTINCT',
  -where_clause         => Literal SQL WHERE clause to use in SELECT state-
                           ment sent to database (may contain placeholders),
  -default_orderby_columns => [ARRAY] Default list of columns to use in ORDER BY
                           clause.  If 'sortby' cgi param is passed (e.g. from
                           user clicking a column sort link), it will always be
                           the first column in the ORDER BY clause, with these
                           coming after it.
  -bind_params          => [ARRAY] If -where_clause used placeholders ("?"),
                           this must be the ordered values to use for them,
  -fetchrow_closure     => (CODE) A code ref to execute upon retrieving a
                           single row of data from database.  First arg to
                           closure will be calling object; subsequent args
                           will be the values of the retrieved row of data.
                           The closure's return value will be push()d onto the
                           object's results array, which is unique to a search.
                           It should be a hash reference with a key for each
                           column returned in the search, and values with the
                           search field values.
  -dry_run              => Do a dry run: just build SQL without actually running
                           it and building 'results' array.  SQL statement that
                           would have been executed is in '_sql' object variable
Search result display options

The following settings affect display of search results, but also affect the search logic (SQL query executed).

  -max_results_per_page   => Maximum number of database records to display on a
                             single page of search result display table
                             (default: 20)
  -show_total_numresults  => Show total number of records found by most recent
                             search, with First/Last page navigation links
                             (default: true)

The following settings only affect display of search results, not the search logic.

  -display_columns        => {HASH} Associative array holding column names as
                             keys, and labels for display table as values,
  -column_titles          => {HASH} Associative array holding column names as keys, and titles
                             (anchor titles, a.k.a mouseovers) for display table as values;
                             has effect only when using -display_mode => 'table
  -column_align           => {HASH} Keyed on column name to specify the table cell
                             html align attribute when using -display_mode => 'table'
  -numeric_columns        => {HASH} Columns of numeric type should have a
                             true value in this hash,
  -currency_columns       => {HASH} Columns of monetary value should have a
                             true value in this hash,
  -unsortable_columns     => {HASH} Columns which the user should not be able
                             to sort by should have a true value in this hash,
  -sortable_columns       => {HASH} Columns which the user should only be able
                             to sort by should have a true value in this hash,
  -pre_nondb_columns      => [ARRAY] Columns to show left of database columns
                             in display table,
  -post_nondb_columns     => [ARRAY] Columns to show right of database columns
                             in display table,
     (Note: Since no data from the database will be present for
      -{pre,post}_nondb_columns columns, you should define
      -columndata_closures for each column you list)

  -optional_header        => Optional HTML header to display just above search
                             result display table,
  -optional_footer        => Optional HTML footer to display just below search
                             result display table,
  -href_extra_vars        => {HASH} Extra CGI params to append to column sorting and
                             navigation links in search result display table.
                             If a key in the HASHREF has an undef value, will take the
                             value from an existing CGI param on request named the
                             same as key.
  -href_extra_vars_qs     => Extra CGI params (in query string form) to append to column sorting.
  -form_extra_vars        => {HASH} Extra CGI params to include in HTML as hidden
                             input fields.  Note that the search widget itself doesn't
                             generate an HTML form, so if the search output is not
                             wrapped in an HTML form, these hidden inputs will be ignored
                             by browsers.  The HASHREF has the same syntax as -href_extra_vars,
                             including the meaning of keys with undef values.
  -action_uri             => HTTP URI of script this is running under
                             (default: SCRIPT_NAME environment variable),
  -page_range_nav_limit   => Maximum number of pages to allow user to navigate to
                             before and after the current page in the result set
                             (default: 10)
  -columndata_closures    => {HASH} of (CODE): Reference to a hash containing a
                             code reference for each column which should be
                             passed through before displaying in result table.
                             Each closure will be passed 3 arguments:
                              $searchdispobj (the -display_class object),
                              $row (the current row from the result set)
                              $color (the current background color of this row)
                             and is (currently) expected to return an HTML table
                             cell (e.g. "<td>blah</td>")

  -display_mode           => ('table'|'grid') Which of the default display modes
                             to use, table or grid.
                             (default: table)
  -display_class          => Actual class to use to display search results.
                             (default: CGI::Widget::DBI::Search::Display::Table)
  -grid_columns           => Maximum number of columns to render, if displaying
                             as grid
  -browse_mode            => If true, hides sorting and paging options from search
                             result display.  Used by CGI::Widget::DBI::Browse.
Universal options
  -no_persistent_object   => Inform object that we are not running under a
                             persistent object framework (eg. Apache::Session):
                             disable all features which enhance performance
                             under a persistence framework, and enable features
                             necessary for smooth operation without persistence
                             (default: true)
CSS / Skinning options
  -css_grid_class        => (default: searchWidgetGridTable)
  -css_grid_cell_class   => (default: searchWidgetGridCell)

  -css_table_class             => (default: searchWidgetTableTable)
  -css_table_row_class         => (default: searchWidgetTableRow)
  -css_table_header_row_class  => (default: searchWidgetTableHeaderRow)
  -css_table_cell_class        => (default: searchWidgetTableCell)
  -css_table_header_cell_class => (default: searchWidgetTableHeaderCell)
  -css_table_unsortable_header_cell_class => (default: searchWidgetTableUnsortableHeaderCell)

  -extra_grid_cell_attributes  => {HASH} Static attributes to add to the grid table cell (<TD> element),
                            -OR-  (CODE) Dynamic attributes to add to the grid table cell (<TD> element); the
                                         anonymous sub takes two arguments: the display object and the results row
  -extra_table_cell_attributes        => {HASH} -OR- (CODE) Same as above except for table display_mode instead of grid
  -extra_table_header_cell_attributes => {HASH} -OR- (CODE) Same as above except for table display_mode instead of grid



Sets necessary object variables from defaults in package constants, if not already set. Called from search() method.


search([ $where_clause, $bind_params, $clobber ])

Perform the search: runs the database query, and stores the matched results in an object variable: 'results'.

Optional parameters $where_clause and $bind_params will override object variables -where_clause and -bind_params. If $clobber is true, search results from a previous execution will be deleted before running new search.

After this method executes, various internal object variables will be set, to indicate the state of the search. Here are some useful ones:

(if -show_total_numresults is true) page => current page in result set (0 indexed) lastpage => last page in result set (0 indexed)


Executes a SELECT COUNT() query with the current search parameters and stores result in object variable: 'numresults'. Has no effect unless -show_total_numresults object variable is true. As a side-effect, this method also sets the 'lastpage' object variable which, no surprise, is the page number denoting the last page in the search result set.

This is used for displaying total number of results found, and is necessary to provide a last-page link to skip to the end of the search results.

pagesort_results($col, $reverse)

Sorts a single page of results by column $col. Reorders object variable 'results' based on sort column $col and boolean $reverse parameters.

(note: method currently unused)

append_where_clause($where_sql, [ $op ])

Adds an SQL expression to the current -where_clause, if any. Optional $op specifies the SQL operator to attach the expression with (default: AND).


Appends extra bind params to the end of the current list of -bind_params.

display_results([ $disp_cols ])

Displays an HTML table of data values stored in object variable 'results' (retrieved from the most recent call to search() method). Optional variable $disp_cols overrides object variable -display_columns.


Transfers all display-specific settings from search widget object to the search display widget object.


CGI::Widget::DBI::Search::Display::Grid, CGI::Widget::DBI::Search::Display::Table, CGI::Widget::DBI::Search::AbstractDisplay


Columns listed in -sql_retrieve_columns may not contain newline characters (\n). You can alias complex SQL functions though, which is exactly where you'd want to use a newline for readability.


Adi Fairbank <>


Copyright (C) 2004-2010 Adi Fairbank


This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License along with this program. If not, see <>.


Jun 27, 2010

syntax highlighting: