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.

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.