CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Help needed with database script

 



yim11
Novice

Aug 15, 2001, 1:58 PM

Post #1 of 1 (378 views)
Help needed with database script Can't Post

Hello,
I have the following script (posted below) that works for the most part, but fails when searching for the Business Name, Contact Name, or Contact Phone fields. Searches using
the Type or Center fields work as expected.

For example - the database has a text field called "business_name", an entry in that field is
"Demo Inc.". Using the script below and typing Demo Inc. in the Business Name search field
will not display any results. I am sure the error is in my script as the SQL query produces the
desired results when executed manually. Any and all help from the Perl/DBI gurus here is
GREATLY appreciated!

TIA!
-jim


-----Begin Script-----

#!/usr/bin/perl

use DBI;
use CGI;
use CGI qw(:all);
use CGI::Carp qw (fatalsToBrowser confess);


my $query = new CGI;
$user="username";
$password="password";
$dbname="thisdb";
$dbh=DBI->connect("dbi:Pg:dbname=$dbname",$user,$password);

if ( param('submit') ) {

# L I S T I N G

print $query->header;
print $query ->start_html
(
-title=> 'Queries Page',
-BGCOLOR=>'white',
-TEXT=>'black',
-LINK=>'blue',
-VLINK=>'blue',
-ALINK=>'blue'
);

$statement="select
center,contact_date,business_name,type,contact_name,contact_title,contact_phone,address
from info";

if ( param('center') ) {

$center=$query->param('center');
$statement="$statement where center LIKE '%$center%' ";

}

elsif ( param('type') ) {

$type=$query->param('type');
$statement="$statement where type LIKE '%$type%' ";

}

if ( param('business_name') ) {

$business_name=$query->param('business_name');
$statement="$statement where business_name ~*'%$business_name%'";

}

elsif ( param('contact_name') ) {

$contact_name=$query->param('contact_name');
$statement="$statement where contact_name LIKE '%$contact_name%' ";

}


$statement="$statement";
$sth = $dbh->prepare("$statement");
$rv= $sth->execute( );
my Út;
print $query ->startform(-method=>"POST",
-action=>"cgi-bin/searchdata.pl");
print <<"ENDPRINT";

ENDPRINT

while ($dat = $sth->fetchrow_hashref) {
print " <table border\=\"0\">
<tr><td><font size\=2 color\=\"blue\"><B><u>Center</u></b>:</font></td><td>
$dat->{center}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Contact Date</u></b>: </font></td><td>
$dat->{contact_date}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Business Name</u></b>: </font>
</td><td>$dat->{business_name}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Business Type</u></b>: </font>
</td><td>$dat->{type}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Contact Name</u></b>: </font></td><td>
$dat->{contact_name}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Contact Title</u></b>: </font></td><td>
$dat->{contact_title}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Contact Phone</u></b>:
</font></td><td> $dat->{contact_phone}</td></tr>
<tr><td><font size\=2 color\=\"blue\"><B><u>Address</u></b>: </font></td><td>
$dat->{address}</td></tr>
</table><hr>
";
}

print $query->end_form;
print $query -> end_html();


} else {

# F O R M

print $query->header;
print $query ->start_html
(
-title=> 'Queries Page',
-BGCOLOR=>'white',
-TEXT=>'black',
-LINK=>'blue',
-VLINK=>'blue',
-ALINK=>'blue'
);
print $query ->startform(-method=>"POST",
-action=>"cgi-bin/searchdata.pl");
print "<center><p><h2>Search the Database</h2><p>";

# Set up the Parts of the Table

$center = $query ->textfield(-name=>'center',
-size=>10,
-maxlength=>20);
$contact_name = $query ->textfield(-name=>'contact_name',
-size=>20,
-maxlength=>100);
$business_name = $query ->textfield(-name=>'business_name',
-size=>20,
-maxlength=>50);
$type=$query-> popup_menu (-name=>'type',
-values=>[' ', 'Educational', 'Medical', 'Legal', 'Accounting', 'Hospitality', 'Real Estate',
'Manufacturing', 'Retail', 'Other']);
$contact_phone = $query ->textfield(-name=>'contact_phone',
-size=>20,
-maxlength=>30);


print $query -> table({-border=>0},
Tr({-align=>LEFT,-valign=>TOP},
[
td({-align=>LEFT},['<b>Center:</b>',$center]),
td(['<b>Type:</b><P>',$type])
]
)
);

print $query -> table({-border=>0},
Tr({-align=>LEFT, -valign=>TOP},
[
td(['<b>Business Name:</b>',$business_name]),
td(['<b>Contact Name:</b>',$contact_name]),
td(['<b>Contact Phone:</b>',$contact_phone])
]
)
);


print "<BR>\n";
print $query ->reset;
print $query->submit(-name=> 'submit',
-value=>'Submit');
print "<P>";

print "</center>";
print $query-> end_form;
print $query -> end_html();
}

-----End Script-----


 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives