Re: cgi ordering a table with sql

Pat Rice wrote:
> I'm trying to achieve the following
> a table that when I click on the top link I will chance the ORDER BY
> value in the table. eg
> 
> |fruit	| boxes |
> |orange	| 10	|
> |apples	| 5	|
> 
> so if fruit was a link and I clicked it I would change my select
> statement to order by fruit, if I clicked on boxes I would order by
> boxes,

Maybe this code will help you sort it out:

     use DBI;
     use CGI;

     my $cgi = CGI->new;
     my $sortby = $cgi->param('sortby') || 'fruit';
     print $cgi->header;

     print "<table>\n<tr>\n<th>";
     print $sortby eq 'fruit'
       ? '<i>fruit</i>'
       : '<a href="?sortby=fruit">fruit</a>';
     print "</th>\n<th>";
     print $sortby eq 'boxes'
       ? '<i>boxes</i>'
       : '<a href="?sortby=boxes">boxes</a>';
     print "</th>\n</tr>\n";
     foreach my $row ( @{ getdata($sortby) } ) {
         print "<tr><td>$row->[0]</td><td>$row->[1]</td></tr>\n";
     }
     print "</table>\n";

     sub getdata {
         my $sortby = shift;
         my $dbh = DBI->connect('dbi:SQLite:test.db', '', '',
           { RaiseError => 1 } );
         my $sth = $dbh->prepare('SELECT * FROM fruits ORDER BY ?');
         $sth->execute($sortby);
         my $data = $sth->fetchall_arrayref;
         $dbh->disconnect;
         $data;
     }

-- 
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl