Help needed talking to database

[yim11]

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 qwall);
use CGI::Carp qw (fatalsToBrowser confess);


my $query = new CGI;
$user="username";
$password="password";
$dbname="thisdb";
$dbh=DBI->connect("dbig: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 %dat;
print $query ->startform(-method=>"POST",
-action=>"cgi-bin/searchdata.pl"
print <<&quot;ENDPRINT&quot;;

ENDPRINT

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

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=>&quot;POST&quot;,
-action=>&quot;cgi-bin/searchdata.pl&quot;
print &quot;<center><p><h2>Search the Database</h2><p>&quot;;

# 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 &quot;<BR>\n&quot;;
print $query ->reset;
print $query->submit(-name=> 'submit',
-value=>'Submit');
print &quot;<P>&quot;;

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

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

 

[hmerrill]

Lets start at the top - you don't have any error detection yet - how about starting with creating a simple connection script that *just* connects - something like this:

#!/path/to/perl -w
use strict;
use DBI;

my $dbh = DBI->connect(&quot;dbig:dbname=$dbname&quot;,
&quot;your_user&quot;,
&quot;your_password&quot;,
{ RaiseError => 1, PrintError => 0 }
);

if (defined($dbh)) {
print &quot;Connected fine!\n\n&quot;;
}
else {
print &quot;Could *NOT* connect!\n\n&quot;;
exit -1;
}
--------------------------------------

Start with that, which you can run at the command prompt - do you get a valid connection? With a good size script like the one you included, it's hard to tell what could be going wrong. Start small, with a simple connection script, and once that works just build on it.

Once that works from the command line, just add some html tags so that you can run the script from a browser, and make sure it can still connect OK. Then build on that.

You really need to read the DBI perldocs(&quot;perldoc DBI&quot - you should be using error trapping - to do that I always set RaiseError => 1 in my connect.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com