How can I do paging and sorting in a Perl CGI program?
Here is the table in which I am retrieving the data from an SQLite database.
Its having lots of records, so near that ADD button I need something like
|< < > >| which would do the paging function whenever I click.
Also, besides the table each header (e.g. UserName UserId) I need a sorting
button. Something like a ^ button. Please do help me find the solution..Thank You.
#!C:\perl\bin\perl.exe
use CGI;
use CGI qw/:standard/;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
my $q = new CGI;
use DBI;
use CGI qw(:all);
use warnings;
print $q->header ( );
my $dbh = DBI->connect(
"dbi:SQLite:DEVICE.db",
"", "",
{
RaiseError => 1,
AutoCommit => 1
}
);
my @rows = ();
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");
print '<table>';
print "&l开发者_开发百科t;tr>";
print "<th>$sth->{NAME}->[0]</th>";
print "<th>$sth->{NAME}->[1]</th>";
print "<th>$sth->{NAME}->[2]</th>";
print "<th>$sth->{NAME}->[3]</th>";
print "<th>$sth->{NAME}->[4]</th>";
print "<th>$sth->{NAME}->[5]</th>";
print "<th> EDIT </th>";
print "<th> DELETE </th>";
while (my @row = $sth->fetchrow_array) {
print "
<tr>
<td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td>
<td>$row[3]</td>
<td>$row[4]</td>
<td>$row[5]</td>
<td><A HREF=\"\">EDIT</A></td>
<td><A HREF=\"\">DELETE</A></td>
</tr>";
}
print "<tr style='background-color:#CDC9C9;'><td><A HREF=\"http://localhost/cgi-
bin/AddUser.cgi\">ADD</A></td><td></td><td></td><td></td><td></td></tr>";
print"</table>";
$sth->finish();
$dbh->commit();
$dbh->disconnect;
print <<END_HTML;
<html>
<head><title></title></head>
<body>
<form action="UsersList.cgi" method="get">
<TABLE align="center">
<TR>
<TD align="left">
<input type="hidden" name="submit" value="Submit">
</TD>
</TR>
</TABLE>
</form>
</body></html>
END_HTML
----------------------------------------
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">ADD</a></td>'
. '<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.
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.
加载中,请稍侯......
精彩评论