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

My question relates to searching via an HTML form using an Access SQL database. I am trying to make a form box, with which a user can search for a poem containing whatever word they input. The results would then be shown in a list of which the user may choose from on the page. How would I go about searching the database for the word(s) inputted by the user? The "body" is the main content of each poem which it would have to search. Any help would be appreciated.

Shinwa : Did that penguin just meow at me?
Snuggy : What hunny?
Shinwa : nuffin' luff...

Replies are listed 'Best First'.
Re: CGI...Perl...Database Searching
by BUU (Prior) on Aug 09, 2004 at 22:17 UTC
    First you would create the html page containing a form that has an action pointing at some cgi script.

    Next you create the cgi script.

    In this cgi script, first you would utilize a module to parse the cgi data and obtain the form parameter from the html page.

    Next you would use dbi to connect to your database.

    After that you would create some sql that fetches you the proper results: this sql is likely to be server specific so examine your server's manual.

    Nextly you would execute this sql and retrieve the results.

    Lastly you print out the appropiate header and any html to display the results you retrieved.
Re: CGI...Perl...Database Searching
by Your Mother (Archbishop) on Aug 10, 2004 at 07:28 UTC

    A way to go about it: start with DBD::ODBC (really DBI as BUU suggested) and CGI. If your app is as simple as you suggest, you can probably stop there. Here is some loose pseudo-code for it. You'll have to do a great deal of reading or get some more help, perhaps point by point after reading bits you can absorb, to complete it and make it safe.

    use warnings; no warnings 'uninitialized'; use strict; use CGI qw(:standard); use DBI; my $data_source = 'whatever'; my $username = 'whoever'; my $auth = 'whichever'; my %attr = (); print header(), start_html(-title => "Search poems"), start_form(), textfield(-name => 'q'); eval { my $dbh = DBI->connect($data_source, $username, $auth, \%attr); if ( param('q') ) { my $sth = $dbh->prepare(<<""); SELECT * FROM whatever WHERE this MATCHES ? $sth->execute( param('q') ); while ( my $row = $sth->fetchrow_hashref ) { # format and print the results } # OR mention you couldn't find anything } }; print h3("Couldn't execute querry! $@") if $@; print end_form(), end_html();