Help with perl script using mysql and stdin.

motdman · 07-10-2009, 10:35 AM

Hello,

I have a perl script that searches for a pattern within a mysql database but the search is not working right. Here are pieces of the script that I think need improvement. Line 1 and 2 work fine. Line3 does not print out the results but the $search variable is displayed ok in Line4.

#line1
print "Enter search string:>;

#line2
my $search = <STDIN>;

#line3
my $query="SELECT id,problem,resolution FROM testtable where problem like '%$search%'";

#line4
print "The search results in $search are:\n";

If change #line3 to include the actual search string without defining a varible, the search works great.

my $query="SELECT id,problem,resolution FROM testtable where problem like '%searchstring%';

Is line3 not interpreting the search variable correctly?

Disillusionist · 07-10-2009, 11:14 AM

It is likely to be because of the single quotes.

Have you tried:

Code:

my $query="SELECT id,problem,resolution FROM testtable where problem like '%" . $search . "%'";

Disillusionist · 07-10-2009, 11:31 AM

Additionally you will need to use chomp to remove the carriage return from the string $search

Code:

#!/usr/bin/perl
use strict;
print "Enter search string:";
my $search;
chomp($search = <STDIN>);

my $query="SELECT id,problem,resolution FROM testtable where problem like '%" . $search . "%'";

print "The search results in $search are:\n";

motdman · 07-10-2009, 01:27 PM

Disill: That did the trick. Thank you very much! What do the . (periods)on both sides of $search do?

micxz · 07-10-2009, 01:37 PM

Period (.) performs string concatenation

motdman · 07-10-2009, 01:44 PM

ok thanks.

My script terminates if no results are found. How can I make it perform another search if it returns with no results?

Disillusionist · 07-10-2009, 02:06 PM

Please post the code you have showing where it is terminating.

Please surround your code with [CODE] tags (by pressing the # icon on the top of the thread reply area.

This keeps the formatting which makes life easier for others reading your code.

motdman · 07-10-2009, 02:21 PM

Code:

#!/usr/bin/perl

use DBI;
use strict;
use warnings;

#Prompt user to enter search string
print "Enter search string:\n";
my $search;

#Chomp removes carriage return from the string 
$search chomp($search = <STDIN>);

# database information
my $db="testdb";
my $host="localhost";
my $port="3306";
my $userid="blah";
my $passwd="blah";
my $connectionInfo="DBI:mysql:database=$db;$host:$port";

# make connection to database

my $dbh = 
DBI->connect($connectionInfo,$userid,$passwd);

# prepare and execute query

my $query="SELECT id,problem,resolution FROM testtable where problem like '%" . $search . "%'"; 
my $sth = $dbh->prepare($query); 
$sth->execute();

# assign fields to variables
my ($id,$problem,$resolution);
$sth->bind_columns(undef, \$id, \$problem, \$resolution);

# output search and resolution
print "The search results are:\n";
while($sth->fetch()) {
        print "$problem ";
	  print "$resolution\n";
}

# clean up
$sth->finish();

# disconnect from database
$dbh->disconnect;

Disillusionist · 07-10-2009, 02:47 PM

You could try looping through, with a slight change to your code:

Code:

#!/usr/bin/perl

use DBI;
use strict;
use warnings;

my $search;

#Prompt user to enter search string
print "Enter search string:\n";
#Chomp removes carriage return from the string 
$search chomp($search = <STDIN>);
if ($search) {
   # database information
   my $db="testdb";
   my $host="localhost";
   my $port="3306";
   my $userid="blah";
   my $passwd="blah";
   my $connectionInfo="DBI:mysql:database=$db;$host:$port";

   # make connection to database

   my $dbh = 
   DBI->connect($connectionInfo,$userid,$passwd);

   while ($search) {

      # prepare and execute query

      my $query="SELECT id,problem,resolution FROM testtable where problem like '%" . $search . "%'"; 
      my $sth = $dbh->prepare($query); 
      $sth->execute();

      # assign fields to variables
      my ($id,$problem,$resolution);
      $sth->bind_columns(undef, \$id, \$problem, \$resolution);

      # output search and resolution
      print "The search results are:\n";
      while($sth->fetch()) {
         print "$problem ";
	 print "$resolution\n";
      }

      # clean up
      $sth->finish();

      print "Enter search string:\n";
      #Chomp removes carriage return from the string 
      $search chomp($search = <STDIN>);

   }

   # disconnect from database
   $dbh->disconnect;
}

This should loop through asking for a new search string.

To exit just press [Return] on it's own.

motdman · 07-24-2009, 09:56 AM

syntax error....near "$search chomp"
aborted due to compilation errors

Any ideas?

micxz · 07-24-2009, 01:45 PM

how about just:

Code:

chomp($search = <STDIN>);

if you wanted to use the way you have it you need $search=chomp...but seems redundant.

chrism01 · 07-27-2009, 01:22 AM

chomp($x) reduces the param in place. The returned value as in

$x=chomp($x);

is the num of chars removed.

http://perldoc.perl.org/functions/chomp.html