MySQL Forums
Forum List  »  Perl

Search all fields
Posted by: Jay Foo
Date: May 17, 2007 09:52AM

I have a asset db which stores (hostname,serial#, model...etc). I've written an interactive pl script that ask for hostname which search db based on hostname entered. Right now the script only searches the "hostname" field in the db....
What I want is to search all fields....model, serial etc...
Can someone help? Here is my working script

#!/usr/local/bin/perl

use DBI;

my ($dsn) = "DBI:mysql:asset:10.33.50.17"; #data source name
my ($user_name) = "assetuser"; #user name
my ($password) = "abc123"; # password
my ($dbh, $sth); #database and statement handles
my ($hostname); #user data STDIN
my (@data); #array for rows returned by query
my ($data);

# connect to database
$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });

$sth = $dbh->prepare(qq|SELECT * FROM board14 WHERE hostname LIKE ? |)
or die "Couldn't prepare statement: " . $dbh->errstr;


print "Enter Hostname or 'exit' to quit> ";
while (chomp ($hostname = <>) ) { # Read input from the user
if( lc($hostname) eq 'exit' ) { print "\nGoodbye!\n"; exit; }
else {
$sth->execute("\%$hostname\%") # Execute the query
or die "Couldn't execute statement: " . $sth->errstr;
}

### Print the header
print "\n";
print "HOSTNAME SERIAL LOCATION MODEL \n";
print "========= ======== =============================== ============\n\n";

# Read the matching records and print them out
while (@data = $sth->fetchrow_array()) {
my $host = $data[0];
my $serial= $data[1];
my $location = $data[2];
my $model = $data[3];

### Tidy up NULL fields
foreach ($host, $serial, $location, $model) {
$_ = "N/A" unless defined $_;
}

### Print out the formatted table...
printf "%-13s %-22s %-33s %-6s %s\n", $host, $serial, $location, $model;

}

if ($sth->rows == 0) {
print "No names matched `$hostname'.\n\n";
}


$sth->finish;
print "\n";
print "Enter Hostname or 'exit' to quit> ";
}

$dbh->disconnect;

Options: ReplyQuote


Subject
Written By
Posted
Search all fields
May 17, 2007 09:52AM
May 17, 2007 01:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.