Re: cgi ordering a table with sql

Hi all
I'm trying to get the following working
what I want to do is to click on the link and it would order the
table, through the SQL query.

I think the problem is that when I click on on the link to ORDER BY it
fails, that is it does not change the order of the table. I wondering
does anyone know how I can confirm that the ? is correct, as in what
is being passed to the query. but I'm prety sure that I am passing the
correct arguments to the SQL query,
i.e.
my $sth = $dbh->prepare('SELECT * FROM test1 ORDER BY ?');

Thanks in advance
Pat



      1 #!/usr/bin/perl
      2 use DBI;
      3   use CGI;
      4
      5
      6
      7    my $cgi = CGI->new;
      8    my $sortby = $cgi->param('sortby') || 'Account';
      9    print $cgi->header;
     10
     11    print "<table>\n<tr>\n<th>";
     12
     13
     14 ##CREATE TABLE test1 (SR varchar(30), Account varchar(50),
Customer_Name varchar(50), Sev varchar(10), Status varchar(50), Data_o
       pened varchar(50), last_touched varchar(50), Next_Touch
varchar(50), Age varchar(10) , Last_touch varchar(10), sub_status
varchar        (50));
     15
     16
     17
     18 print $sortby eq 'SR'
     19      ? '<i> SR </i>'
     20      : '<a href="?sortby=SR"> SR </a>';
     21    print "</th>\n<th>";
     22    print $sortby eq 'Account'
     23      ? '<i> Account </i>'
     24      : '<a href="?sortby=Account"> Account </a>';
     25    print "</th>\n</tr>\n";
     26
     27 print "\n The value of sortby: $sortby";
     28 foreach my $row ( @{ getdata($sortby) } ) {
     29        print "<tr><td>$row->[0]</td><td>$row->[1]</td></tr>\n";
     30    }
     31    print "</table>\n";
     32
     33
     34
     35    sub getdata {
     36        my $sortby = shift;
     37         print "<br>SortBy in getdata = $sortby";
     38        my $dbh = DBI->connect('dbi:SQLite:test.db', '', '',
     39          { RaiseError => 1 } );
     40         my $sth = $dbh->prepare('SELECT * FROM test1 ORDER BY ?');
     41
     42         #$sortby = "Account";
     43        $sth->execute($sortby);
     44
     45          my $data = $sth->fetchall_arrayref;
     46
     47
     48        $dbh->disconnect;
     49        $data;
     50    }











On Mon, May 19, 2008 at 8:50 PM, Gunnar Hjalmarsson <[email protected]> wrote:
> 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
>
> --
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
> http://learn.perl.org/
>
>
>