NAME
CGI::Browse - HTML table from MySQL to display rows with sortable columns, flexible delete links, and multiple column link-outs.
VERSION
This document describes CGI::Browse version 1.0.0
SYNOPSIS
This module enables a browsable list for data with controls to set the viewing window (page length), page to previous and next pages, jump to a page, resort the rows by column headers, delete rows, and define link-outs for multiple columns. Links to working examples are listed below, and the CGI scripts are included.
The Browse object can be used with the default HTML layout, which includes all of the labeling features, such as "Sorted by" and "Starting row".
use CGI::Browse;
# Get CGI variables using favorite method
# Define table fields
my $fields = [ { name => 'state_capital_id', label => 'ID',
hide => 1, sort => 0 },
{ name => 'state', label => 'State',
hide => 0, sort => 1, link => 'link1', id => 0 },
{ name => 'statehood_year', label => 'Statehood',
hide => 0, sort => 1 },
{ name => 'capital', label => 'Capital',
hide => 0, sort => 1, link => 'link2', id => 0 },
{ name => 'capital_since', label => 'Capital Since',
hide => 0, sort => 1 },
{ name => 'most_populous', label => 'Most Populous',
hide => 0, sort => 1 },
{ name => 'city_population', label => 'City Pop.',
hide => 0, sort => 1 },
{ name => 'metro_population', label => 'Metro Pop.',
hide => 1, sort => 1 },
{ name => 'notes', label => 'Notes',
hide => 0, sort => 0 } ];
# Define browse parameters (including fields and matching SQL statement)
my $params = { fields => $fields,
sql => "select state_capital_id, state, statehood_year, capital, capital_since, most_populous, city_population, metro_population, notes from state_capitals",
connect => { db => 'mydb', host => 'localhost', user => 'user', pass => 'pass' },
urls => { root => 'http://www.ourpug.org/',
browse => 'cgi-bin/eg/browse.cgi',
link1 => 'cgi-bin/eg/browse_link1.cgi?id=',
link2 => 'cgi-bin/eg/browse_link2.cgi?id=',
delete => 'cgi-bin/eg/browse_delete.cgi?id=' },
classes => ['browseRowA', 'browseRowA', 'browseRowA', 'browseRowB', 'browseRowB', 'browseRowB'],
features => { default_html => 1, delete => 'each' } };
# Create the browse object
my $browse = CGI::Browse->new( $params );
# Build HTML page
my $html = "Content-type: text/html\n";
$html .= "Status: 200 OK \n\n";
$html .= "<html>\n";
$html .= "<head>\n";
$html .= " <title>CGI::Browse Module Sample Script</title>\n";
$html .= $browse->_build_styles(); # Defines included styles
$html .= "</head>\n";
$html .= "<body>\n";
$html .= $browse->build( \%cgi_vars );
$html .= "</body>\n";
$html .= "</html>\n";
# Print page
print $html;
A working example of this form is available at http://www.ourpug.org/cgi-bin/eg/browse.cgi and is included in this package as "scripts/browse.cgi".
It can also be used with a Template system (such as Template Toolkit) by removing the "default_html" feature. Using this method, you can decide which of the features you wish to use on your form.
use Template;
use CGI::Browse;
...
my $params = { fields => $fields,
sql => "select state_capital_id, state, statehood_year, capital, capital_since, most_populous, city_population, metro_population, notes from state_capitals",
connect => { db => 'mydb', host => 'localhost', user => 'user', pass => 'pass' },
urls => { root => 'http://www.ourpug.org/',
browse => 'cgi-bin/eg/browse_tmpl.cgi',
link1 => 'cgi-bin/eg/browse_link1.cgi?id=',
link2 => 'cgi-bin/eg/browse_link2.cgi?id=',
delete => 'cgi-bin/eg/browse_delete.cgi' },
classes => ['browseRowA', 'browseRowA', 'browseRowA', 'browseRowB', 'browseRowB', 'browseRowB'],
features => { delete => 'multi' } };
my $browse = CGI::Browse->new( $params );
my $build = $browse->build( \%cgi_vars );
my $template = Template->new();
$template->process( \$tmpl, $build );
A working example of this form is available at http://www.ourpug.org/cgi-bin/eg/browse_tmpl.cgi and is included in this package as "scripts/browse_tmpl.cgi".
DESCRIPTION
The Browse object is a flexible component for listing data in an HTML table. As a script developer, you must define the fields and set the parameters for your desired features.
PARAMETERS
fields (req)
Defines the columns of the table.
sql (req)
SQL statement which matches the fields list parameter. It may use a where clause to filter rows.
connect (req)
Connect parameters for MySQL database.
urls (req)
Script URK plus link-out and delete URLS.
classes (opt)
Optional parameter defining CSS styles for displaying alternating rows.
features (opt)
Optional parameter for controlling delete and layout features.
FIELDS LIST
The module requires a fields list-of-hashes to define the name, label, and other features of each column.
my $fields = [ { name => 'state_capital_id', label => 'ID',
hide => 1, sort => 0 },
{ name => 'state', label => 'State',
hide => 0, sort => 1, link => 'link1', id => 0 },
Field options include:
name (req)
This is the SQL field name. It is used in conjunction with sort to reorder the table. It is also used in the "Sorted by" label feature.
label (req)
This is the header label. It does not have to match the database field. It will be clickable if sort is TRUE.
hide (opt)
This setting is optional for all fields. If TRUE, it will keep Browse from displaying the column. This is useful for keys/foreign keys that are included for the purpose of linking to other screens.
sort (opt)
This setting enables the user to click on this column's heading (label) to resort the column. Repeatedly clicking the column heading alternates between ascending and descending sorts.
link (opt)
This setting defines a link-out for this column's data. There must also be a URL setting for the value of this link.
id (opt)
This setting defines which column's value is appended to the link-out URL. Note: this column should be included in the SQL statement.
OTHER REQUIRED PARAMETERS
SQL STATEMENT
Use a select statement of the form "select field1, field2 from table". The Browse module will edit your statement three ways:
Counting rows
The "field1, field2" portion of your statement is replaced with "count(*)" to count the total number of rows.
Sorting rows
The statement will be appended by "order by <sort_field> <sort_vector>" iff a column heading is clicked to resort the rows. Once resorted, the same relative index will be used to create a view of the data. If you were on row 41 of 50 before resorting, you will still be on row 41 of 50 after resorting.
Limiting view
The statement will be appended by "limit <index>, <window>" in order to select just the rows to be viewed based on your current relative index and window settings.
CONNECT PARAMETERS
These parameters are necessary for connecting to your MySQL database. You may get these values from a configuration file instead of hard-coding them, but they should be included in a hashref value for the "connect" parameter key.
URLS AND LINK-OUTS
Two keys should always be defined: "root" and "browse".
If all of your scripts/controllers use a common domain or directory, use the root key to set it. If they are on different pathways or domains, set the root key to an empty string.
Additional URLs may be defined. The keys should match the "link" key values from the fields list-of-hashes. In the included example, the "State" column has a link-out named "link1", so the "urls" parameter includes a URL key for link1, which points to "cgi-bin/eg/browse_link1.cgi?id=". Note that the given field's "id" value is defined as a column number, so in this example, the link-out for each row will include that row's "state_capital_id" value.
OPTIONAL PARAMETERS
CLASSES
This list of CSS classes enables you to create alternating style patterns on your data rows for visibility. Note that the default classes are ["rowA", "rowB"], which alternates every other row. The included example uses a larger pattern ["rowA", "rowA", "rowA", "rowB", "rowB", "rowB"], which alternates between sets of three rows for each style. You may include as many styles as you wish, although more than two is probably detrimental.
DEFAULT HTML FEATURE
The module can return the default layout by using the "default_html" key in the "features" parameter hashref. If you do not use this setting, then the module will return a hashref of individual blocks for each data and label feature. This hashref is suitable for passing to Template. The included example html/browse.tmpl illustrates the hashref keys.
DELETE FEATURE
There are two different delete methods available in addition to disabling the delete feature.
You can enable a delete per row:
features => { default_html => 1, delete => 'each' }
You can enable delete checkboxes with a "Delete selected" button:
features => { default_html => 1, delete => 'multi' }
You can disable delete by removing the delete key from the "features" parameter key:
features => { default_html => 1 }
INCLUDED FILES
CGI SCRIPTS
Two of the included scripts illustrate working implementations of CGI::Browse using the included browse.sql data.
browse.cgi
Shows "each" delete and "default_html" feature.
browse_tmpl.cgi
Shows "multi" delete and uses Template to manage HTML layout.
Additionally, a browse_delete.cgi script is included as a target for the browse*cgi scripts. Its only purpose is to display the CGI variables sent from the form or link-out.
To edit the scripts for your system, include these steps.
- 1. Install CGI::Browse.
- 2. Set script ownership (chown) and permissions (chmod) if necessary.
- 3. Run the included browse.sql script against your database.
- 4. Edit browse.cgi and change the database "mydb" to your database name.
- 5. Edit browse.cgi and change the user "user" to your user name.
- 6. Edit browse.cgi and change the password "pass" to your password.
HTML FILES
The included files are:
browse.css
This file can be edited and placed in your traditional styles directory, or the styles can be added to your choice of CSS file.
browse.js
This file is included for illustration, but the generated version should be used since the included URLs are built according to your script's need. (The scripts reset the "action" for the form.)
browse.tmpl
This file can be edited and placed in your traditional template directory, or you can use whatever template variables you wish in your own tmpl file.
INTERFACE
The module has only one public method besides new().
build()
Returns a hashref or html depending on the "default_html" feature setting (see above). The included hashref-keys/tmpl-variables are:
browse_styles browse_script browse_action browse_table browse_sorted browse_start browse_prevnext browse_show browse_goto browse_control browse_delete
The following methods are considered private because they are already called by build(), but may be useful depending on your implementation.
_build_styles()
Returns the default CSS stylesheet.
_build_tmpl()
Returns the default template with variables.
CONFIGURATION AND ENVIRONMENT
CGI::Browse requires no configuration files or environment variables.
DEPENDENCIES
Class::Std
Class::Std::Utils
DBIx::MySperqlOO
INCOMPATIBILITIES
None reported.
BUGS AND LIMITATIONS
No bugs have been reported.
Please report any bugs or feature requests to bug-cgi-browse@rt.cpan.org
, or through the web interface at http://rt.cpan.org.
AUTHORS
Feel free to email the authors with questions or concerns. Please be patient for a reply.
Roger Hall (roger@iosea.com), (rahall2@ualr.edu)
Michael Bauer (mbkodos@gmail.com), (mabauer@ualr.edu)
LICENSE AND COPYRIGHT
Copyleft (c) 2009, Roger A Hall <rogerhall@cpan.org>
. All rights reserved.
This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See perlartistic.
DISCLAIMER OF WARRANTY
BECAUSE THIS SOFTWARE IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY FOR THE SOFTWARE, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE SOFTWARE "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE IS WITH YOU. SHOULD THE SOFTWARE PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR, OR CORRECTION.
IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR REDISTRIBUTE THE SOFTWARE AS PERMITTED BY THE ABOVE LICENCE, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE SOFTWARE (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE SOFTWARE TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.