ansaurus

Question

How can I do paging and sorting in a Perl CGI program?

Answer 1

A:

One of the (many) advantages that you'd get from using DBIx::Class for your database access is that all searches have built-in support for paging.

Alternatively, you might find something like Data::Page to be useful.

As for sorting, that's probably best done in your SQL query with a 'sort' clause.

davorg 2010-09-14 09:39:55

i found dbix class rar file as wel as a data::page rar file...i downloaded them in my d drive but when i tried to use the code of paging then i got the error...why is that so? where am i supposed to save the rar file..?

sonya 2010-09-14 10:57:12

Base class package "Class::Accessor::Chained::Fast" is empty. (Perhaps you need to 'use' the module which defines that package first, or make that module available in @INC (@INC contains: C:/Perl/site/lib C:/Perl/lib .). at C:/Perl/site/lib/Data/Page.pm line 4BEGIN failed--compilation aborted at C:/Perl/site/lib/Data/Page.pm line 4.Compilation failed in require at C:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/UsersList.cgi line 7.BEGIN failed--compilation aborted at C:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/UsersList.cgi line 7.

sonya 2010-09-14 10:58:30

i had icluded this code in my program.. use Data::Page; my $page = Data::Page->new(); $page->total_entries($total_entries); $page->entries_per_page($entries_per_page); $page->current_page($current_page); print " First page: ", $page->first_page, "\n"; print " Last page: ", $page->last_page, "\n"; print "First entry on page: ", $page->first, "\n"; print " Last entry on page: ", $page->last, "\n";

sonya 2010-09-14 10:59:34

Everything you need is on CPAN. I think you might want to go through _Learning Perl_ to learn the basics you seem to be missing.

brian d foy 2010-09-14 11:03:39

You can also search StackOverflow to learn how to solve your additional problems.

brian d foy 2010-09-14 11:04:07

If you're getting .rar files of CPAN modules then you're *so* looking in the wrong place. Try http://search.cpan.org/.

davorg 2010-09-14 11:12:17

Answer 2

+1 A:

Ok, first thing, get and read Learning Perl. It is, hands down, the best book to learn Perl with.

Next, take a look at Ovid's CGI Course.

Third, your code has some major problems, and you'll need to walk before you run.

I've tidied and commented the heck out of your code.

#!C:\perl\bin\perl.exe

#  Windows perl ignores the shebang, except to check for flags and 
#  arguments to start the Perl interpreter with.
#  Your webserver might use it though

# You forgot to enable strict.  You enabled warnings further down in
# your code.  These two pragmas will help you write bug free code by 
# catching many errors.
#
# Keep your module and pragma usage at the top of your 
# scripts.  It aids readability. 

use strict;
use warnings;

# Using CGI is a good idea, but you only need to use CGI one time.
use CGI qw/:all/;

# These are good while learning and debugging.
# Do not use them in production code.
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);

use DBI;

my $dbh = DBI->connect(
    "dbi:SQLite:DEVICE.db",
    "", "",
    {
        RaiseError => 1,
        AutoCommit => 1
    }
);

# Don't use indirect object notation.  It can lead to subtle bugs.
# Use the arrow notation for method invocation instead.
my $q = CGI->new();
print $q->header ( );

# The @rows array was doing nothing.




# No need to commit when autocommit is on.
$dbh->commit();
$dbh->disconnect;

# Here we get the html table in a string.

my $table = generate_data_table( $dbi );

# And here we print your whole HTML block with the table interpolated
# into the the main text.  As it was, the HTML page was printing AFTER
# the table you generated.
#
# I put a crappy improper stylesheet in the header of your html page.
# Unless you are only doing the most rudimentary HTML work, learn to 
# use CSS properly.  Your time will be repayed hundreds of times over.
# For only rudimentary work, there's still a good chance you'll break 
# even on any time you invest in learning CSS.

print <<END_HTML;
<html>
<head>
    <title>Add Users</title>
    <style>
        .adduser {
            background-color:#CDC9C9;
        }
    </style>
</head>
<body>

    <form action="UsersList.cgi" method="get">

$table

    <input type="hidden" name="submit" value="Submit">
    </form>

</body>
</html>

END_HTML


# Use subroutines to group related actions.
sub generate_data_table {
    my $dbi = shift;

    my $sql = "SELECT UserId,UserName,CardNo,GroupId,Role,VerifyType FROM UsersList";

    my $sth = $dbh->prepare($sql)
       or die("\n\nPREPARE ERROR:\n\n$DBI::errstr");

    $sth->execute 
        or die("\n\nQUERY ERROR:\n\n$DBI::errstr");

    # Actually generate the table HTML
    my $table = '<table><tr>';

    # Header
    $table .= join '', map "<th>$sth->{NAME}[$_]</th>\n", 0..5;
    $table .= "</tr>\n";

    # Normal Rows
    while (my @row = $sth->fetchrow_array) {
        $table .= '<tr>',
        $table .= join '', map "<td>$row[$_]</td>\n", 0..5;

        $table .= join "\n", 
            '<td><A HREF=\"\">EDIT</A></td>'
            '<td><A HREF=\"\">DELETE</A></td>'
            "</tr>\n";
    }

    # Special Row
    #
    # Don't use inline CSS, use classes and either group all your css at
    # the top of your html code, or better yet, load an external stylesheet.

    # There is no reason to have to escape quotes when working with Perl CGI.
    # First, in html ' and " are interchangeable, so you can pick a quote
    # that doesn't need esacaping.
    #
    # Finally, if you MUST use both ' and " in a single string, you can use
    # Perl's quoting operators (q and qq) to select a safe delimiter that will allow you
    # to avoid escaping.

    $table .= 
          "<tr class='adduser' >"
        . '<td><a HREF="http://localhost/cgi-bin/AddUser.cgi"&gt;ADD&lt;/a&gt;&lt;/td&gt;'
        . '<td></td><td></td><td></td><td></td></tr>'
        . "</table>";

    $sth->finish();

    return $table;
}

Finally, to handle sorting and paging, you can use a library as others have suggested, or you can modify your SQL query. The keywords you want for grabbing only a range of results are LIMIT and OFFSET, use an ORDER BY clause to sort your result set. Add some parameters to your forms to indicate what sorting methods or range you want.

daotoad 2010-09-14 19:17:33

Thanks a Lot for all your time and very very important information that you have provided me sir.Thanks a lot and yes you are right i need to have a strong basics before i move further,Thanks again.

sonya 2010-09-15 06:30:10

CAN ANYBODY PLEASE TELL ME,WHY MY REPUTATION HAS GONE DOWN FROM 6 TO 1 OVERNIGHT?

sonya 2010-09-15 09:11:41

jene, RELAX! SO gets a lot of posts that read like "How U do X? Write me teh codez!". Apparently, you've managed to get people to put you in this class. I don't see it that way. To me, your posts problems seem to stem from English being your 2nd language (based on some of your usage) and being overwhelmed by SO and Perl and CGI and HTML and CSS and SQL. Six big things to learn all at once. In any case, SO seems to reward careful, formal writing. In time your rep will recover. It sucks to be downvoted, but it calls your attention to a problem. Too bad you have to guess what it might be.

daotoad 2010-09-15 15:41:56

Ok am a bit releived now after reading your comment. Even am trying hard to get a strong hold in all these Languages,especially "the Basics".Anyways i will try my best. And for my surprise my reputation has increased to 6 in overnight again,Amazing, Thanks a Lot.

sonya 2010-09-16 09:04:22

ansaurus

tags:

views:

answers:

How can I do paging and sorting in a Perl CGI program?

related questions