how to do client side sorting using querystring in hyperlink associated with the table header using Perl?
Hello everybody i hope everybody is doin well, Actually i have a table in which i fetch the data from sqlite database,i have done the paging and filtering for the grid using perl,now i need to do the sorting. The way in which i want to do is "MAKE THE TABLE HEADERS AS HYPERLINK AND WHENEVER I CLICK THEM THEN IT SHOULD SORT THE TABLE IN ASCENDING OR DESCENDING "ORDER BY" THE COLUMN WHICH I CLICK. Please do let me know is it possible, if yes then please do guide me find the solution. HERE'S My Code. Thank You.
#!C:\perl\bin\perl.exe
use CGI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use strict;
use warnings;
use DBI;
use POSIX;
my $query = new CGI;
my $q = new CGI;
my $numofPages;
my $i;
my @overduedata;
my $pageN;
my @rows = ();
my $pageNum=$query->param('pageNum');
my $Id="";
my $Name="";
my @list;
my $var;
my $val;
my $filterexpression= " ";
print "Content-Type: text/html\n\n";
$query = $ENV{'QUERY_STRING'};
@list = split( /\&/, $query);
foreach (@list) {
($var, $val) = split(/=/);
$val =~ s/\'//g;
$val =~ s/\+/ /g;
$val =~ s/%(\w\w)/sprintf("%c", hex($1))/ge;
($var, ' = ', $val,);
}
print <<END_HTML;
<html>
<head><title></title>
</head>
<body>
<form action="Filtering.cgi" method="post">
<TABLE>
<TR>
<TD>
<input type="hidden" name="submit" value="Submit">
</TD>
</TR>
</TABLE
</form>
</body></html>
END_HTML
my $dbh = DBI->connect("dbi:SQLite:DEVICE.db","", "",
{
RaiseError => 1,
AutoCommit => 1
}
开发者_如何学编程);
unless($pageNum) {
$pageNum=0;
}
my $offset;
unless($offset) {
$offset=11;
}
if ( $q->param("Filter") )
{
my $Id=$q->param('User_Id');
my $Name=$q->param('User_Name');
if ($Id ne "" )
{
$filterexpression= $filterexpression." UserId like '" .$Id. "%' and " ;
}
if ($Name ne "" )
{
$filterexpression= $filterexpression." UserName like '" .$Name. "%' and " ;
}
}
$filterexpression= $filterexpression. " UserId > 0" ;
my $exp;
my $query =$dbh->selectall_arrayref("SELECT * FROM UsersList " ." where ".
$filterexpression);
my $numOfRec=@$query ;
my $numofPages = ceil($numOfRec / $offset);
my $sql = "SELECT UserId,UserName,CardNo,GroupId,Role,VerifyType FROM UsersList " ."
where ". $filterexpression;
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");
my $query=$dbh->selectall_arrayref('SELECT
UserId,UserName,CardNo,GroupId,Role,VerifyType FROM UsersList where '.
$filterexpression);
my $exp = @$query;
while (my ($UserId,$UserName,$CardNo,$GroupId,$Role,$VerifyType) = $sth-
>fetchrow_array())
{
push(@overduedata,{UserId=>$UserId,UserName=>$UserName,
CardNo=>$CardNo,GroupId=>$GroupId,Role=>$Role,VerifyType=>$VerifyType});
}
my $startDisplay = ($pageNum)*$offset;
my $endDisplay = ($pageNum + 1 ) * $offset;
$i = $startDisplay;
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 class=\"tablestyle\">";
print "<tr border=1 style='background-color:#CDC9C9'>
<td class=\"tdstyle\" colspan=\"2\"></td>
<td><A HREF=\"http://localhost/cgi-bin/AddUser.cgi\">ADD</A></td>
<td><b>UserId</b></td>
<td><input type=\"text\" name=\"User_Id\" size=\"6\"></td>
<td><b>UserName</b></td>
<td><input type=\"text\" name=\"User_Name\" size=\"10\"></td>
<td><input type=\"submit\" name=\"Filter\" value=\"Filter\" ></td>
</tr>";
print "<tr class=\"trstyle1\"></tr>";
print "<tr class=\"trstyle2\">";
print "<th class=\"thstyle\"><A HREF=\"http://localhost/cgi-bin/Filtering.cgi?
Sortype=1\" class=\"hiddenlink\">$sth->{NAME}->[0]</A></th>";
print "<th colspan=\"2\" class=\"thstyle\">A HREF=\"http://localhost/cgi-
bin/Filtering.cgi?Sortype=2\" class=\"hiddenlink\">$sth->{NAME}->[1]</A></th>";
print "<th class=\"thstyle\"><A HREF=\"http://localhost/cgi-bin/Filtering.cgi?
Sortype=3\" class=\"hiddenlink\">$sth->{NAME}->[2]</A></th>";
print "<th class=\"thstyle\"><A HREF=\"http://localhost/cgi-bin/Filtering.cgi?
Sortype=4\" class=\"hiddenlink\">$sth->{NAME}->[3]</A></th>";
print "<th class=\"thstyle\"><A HREF=\"http://localhost/cgi-bin/Filtering.cgi?
Sortype=5\" class=\"hiddenlink\">$sth->{NAME}->[4]</A></th>";
print "<th class=\"thstyle\"><A HREF=\"http://localhost/cgi-bin/Filtering.cgi?
Sortype=6\" class=\"hiddenlink\">$sth->{NAME}->[5]</A></th>";
print "<th class=\"thstyle\"> EDIT</th>";
print "<th class=\"thstyle\"> DELETE</th>";
print "</tr>";
if($exp != 0)
{
while ($i < $endDisplay)
{
if($i == $exp){
last;}
print "<tr class=\"trstyle3\">
<td >" . $overduedata[$i]->{'UserId'} . "</td>
<td colspan=\"2\" >" . $overduedata[$i]->{'UserName'} . "</td>
<td>" . $overduedata[$i]->{'CardNo'} . "</td>
<td>" . $overduedata[$i]->{'GroupId'} . "</td>
<td>" . $overduedata[$i]->{'Role'} . "</td>
<td>" . $overduedata[$i]->{'VerifyType'} . "</td>
<td><A HREF=\"\">EDIT</A></td>
<td><A HREF=\"\">DELETE</A></td>
</tr>";
$i = $i + 1;
}
}
if ( $pageNum > 0 ) {
print q[<td><a href="Filtering.cgi?pageNum=0&Sortype=$val">|<</a></td>];
$pageN = $pageNum - 1;
print qq[<td><a href="Filtering.cgi?pageNum=$pageN&Sortype=$val"><</a></td>];
}
else
{
print q[<td><span class="currentpage">|<</span></td>];
print q[<td><span class="currentpage"><</span></td>];
}
if ( $pageNum < ( $numofPages - 1 ))
{
$pageN = $pageNum + 1;
print qq[<td><a href="Filtering.cgi?pageNum=$pageN&Sortype=$val">></a></td>];
$numofPages=$numofPages-1;
print qq[<td><a href="Filtering.cgi?
pageNum=$numofPages&Sortype=$val">>|</a></td>];
}
else {
print q[<td><span class="currentpage">></span></td>];
print q[<td><span class="currentpage">>|</span></td>];
}
print "</table>";
Client side table sorting is achieved with Javascript. There are dozens of libraries easily found by a simple Web search. Stuart Langridge's sorttable is very easy to implement.
This is not an answer to your question, but general advice. Therefore, I have made it community wiki.
Please stop writing CGI scripts for a while until you understand why your script has serious problems.
You have:
use CGI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
# ...
my $query = new CGI;
my $q = new CGI;
First, note that you need to initialize a CGI object only once. Avoid indirect method calls:
my $cgi = CGI->new;
I know the CGI.pm docs use $query but I find $cgi to be more meaningful.
That is a good step. Almost all CGI scripts should use well established libraries rather than homebrew code. However, after that good first step, you do:
print "Content-Type: text/html\n\n";
$query = $ENV{'QUERY_STRING'};
@list = split( /\&/, $query);
foreach (@list) {
($var, $val) = split(/=/);
$val =~ s/\'//g;
$val =~ s/\+/ /g;
$val =~ s/%(\w\w)/sprintf("%c", hex($1))/ge;
($var, ' = ', $val,);
}
There is no reason to engage in cargo-cult practices. Your CGI object already has the parameters passed to the script.
Also, you should declare your variables where they are first used as opposed to dumping all of them at the script.
Use CGI.pm's header to send the header. You have:
print <<END_HTML;
<html>
<head><title></title>
</head>
<body>
<form action="Filtering.cgi" method="post">
<TABLE>
<TR>
<TD>
<input type="hidden" name="submit" value="Submit">
</TD>
</TR>
</TABLE
</form>
</body></html>
END_HTML
which makes no sense as you have sent a complete HTML document before doing anything else in the script.
The rest of the code cannot change what you have already sent.
Put your HTML in a template. Personally, I like HTML::Template for the clean separation between code and content.
This way, you can write your Perl script to generate the content and include any client side functionality in the template separately.
加载中,请稍侯......
精彩评论