How can I do paging and sorting in a Perl CGI prog

2019-03-07 01:23发布

站内问答 / 后端开发
1631 2 6

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 "<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

  ----------------------------------------

标签： html perl cgi
举报
2条回答
可以哭但决不认输i
2楼-- · 2019-03-07 01:46

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.

查看更多
0人赞 添加讨论(0) 举报
神经病院院长
3楼-- · 2019-03-07 02:01

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.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)