Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am designing an interface to interact with a SQL Database. I am using the CGI module to recieve information passed by an html post (includes text boxes etc). eg 
$client_name = $query->param('client_name');
chomp $client_name;

[download]
This is more of a design aspect question. I recieve these results in my perl script, then perform an SQL query using those parameters. I want this page to be a preview of the database results returned by the query.

$sql = "SELECT * FROM tblClient(NOLOCK)";
my $sth = $dbh->prepare( $sql );
$sth->execute;
while ( my $result = $sth->fetchrow_hashref ) {
     if ($result->{Client_Name} =~ /$client_name/i){
         print "$result->{Client_Name}";
       print "$result->{Email}";
       print "$result->{Address}";
       print "$result->{Phone}";
       print "$result->{Fax}";
       # Count is used to store the number of matches
         $count++;
     }

[download]
This will all be nested within a table. For example:

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

Name | Address | Phone | Order Date | More info |

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

Frank | 2 Dunedin | 34243 | 11/02/2001 | click me |

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

.........

.........

I want to use the More info column to allow the client to click to view specific information regarding a given client. As i am using CGI i have thought about 2 methods. One: incapsulate each row within a <form> </form> and use the More info column as a "submit" button. If i use this method which could be completely wrong, will i have to use text boxes to display the data (There is no need for these values to be edited). The second method would involve storing the values returned by the database in a hash array. Could someone please point out to me the common technique for achieving my goal. I'm sure this has been done thousands of times before. I hope i managed to explain my problem.

Thankyou very much for your time

Digger

janitored by ybiC: Balanced <code> tags around codeblocks instead of HTML formatting markup

Replies are listed 'Best First'.
Re: Database Search
by blokhead (Monsignor) on Jul 15, 2004 at 02:27 UTC
    A few comments:

    1. You don't need to chomp CGI input.

    2. A database is designed to do complex queries. We use databases so we don't have to do extra work in Perl to sort out the things we need. Instead, we only ask for what we need, and let the database do the grunt work for us (it's more efficient anyway): 

    my $name = param('name');

# you may want to prevent search metacharacters:
   $name =~ s/(\%|_)/\\$1/g;

my $sth  = $dbh->prepare( "select * from clients where name like ?" );

$sth->execute($name) or die "Whoops!";
while ($sth->fetchrow_hashref) {
  ...
}

    [download]
    Of course, always use placeholders!

    3. Who said something has to be in a textbox to submit to a form? Use <input type=hidden>. Each submit button would look something like this: 

    <form method=post>
  <input type=hidden name=action value="more_info">
  <input type=hidden name=name   value="whatever">
  <input type=submit value="More Info">
</form>

    [download]
    I don't understand your "alternative" of storing values in a "hash array." Again, you shouldn't have to store anything special/extra in a Perl datastructure, you just need to know the right thing to ask the database so it gives you what you want, and in the format you need.

    blokhead

[reply]
[d/l]
[select]
      A minor correction: 
      my $sth  = $dbh->prepare( "select * from clients where name like ?" );

$sth->execute($name) or die "Whoops!"

      [download]

      That should be $sth->execute("%$name%") or somesuch use of wildcards in the execute.

[reply]
[d/l]
Re: Database Search
by pg (Canon) on Jul 15, 2004 at 03:33 UTC

    I can think of at least two alternative ways:

    • You worried that you have to use textbox for non-editable data, simply for the purpose to be able to post it back to the server. That's not true. Your database tables must have keys. Present those keys as hidden fields in your forms. This could mean duplicating information, but what a big deal. (As a matter of fact, there is even no need to store those keys twice, hidden field can be extracted on fly to form the page. This requires some knowledge of scripting and DOM.)
    • If the chance is that the user will view the details of most records, and there are not many master rows, you can just send all data to the client in an XML island, and handles data thru browser and DOM.
[reply]

Back to Seekers of Perl Wisdom