LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 07-10-2009, 11:35 AM   #1
motdman
LQ Newbie
 
Registered: Jul 2009
Posts: 6

Rep: Reputation: 0
Help with perl script using mysql and stdin.


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?
 
Old 07-10-2009, 12:14 PM   #2
Disillusionist
Senior Member
 
Registered: Aug 2004
Location: England
Distribution: Ubuntu
Posts: 1,039

Rep: Reputation: 97
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 . "%'";

Last edited by Disillusionist; 07-10-2009 at 12:28 PM. Reason: Forgot the dots!
 
Old 07-10-2009, 12:31 PM   #3
Disillusionist
Senior Member
 
Registered: Aug 2004
Location: England
Distribution: Ubuntu
Posts: 1,039

Rep: Reputation: 97
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";

Last edited by Disillusionist; 07-10-2009 at 12:41 PM.
 
Old 07-10-2009, 02:27 PM   #4
motdman
LQ Newbie
 
Registered: Jul 2009
Posts: 6

Original Poster
Rep: Reputation: 0
Disill: That did the trick. Thank you very much! What do the . (periods)on both sides of $search do?
 
Old 07-10-2009, 02:37 PM   #5
micxz
Senior Member
 
Registered: Oct 2002
Location: CA
Distribution: openSuSE, Cent OS, Slackware
Posts: 1,131

Rep: Reputation: 75
Period (.) performs string concatenation
 
Old 07-10-2009, 02:44 PM   #6
motdman
LQ Newbie
 
Registered: Jul 2009
Posts: 6

Original Poster
Rep: Reputation: 0
ok thanks.

My script terminates if no results are found. How can I make it perform another search if it returns with no results?
 
Old 07-10-2009, 03:06 PM   #7
Disillusionist
Senior Member
 
Registered: Aug 2004
Location: England
Distribution: Ubuntu
Posts: 1,039

Rep: Reputation: 97
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.
 
Old 07-10-2009, 03:21 PM   #8
motdman
LQ Newbie
 
Registered: Jul 2009
Posts: 6

Original Poster
Rep: Reputation: 0
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;
 
Old 07-10-2009, 03:47 PM   #9
Disillusionist
Senior Member
 
Registered: Aug 2004
Location: England
Distribution: Ubuntu
Posts: 1,039

Rep: Reputation: 97
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.

Last edited by Disillusionist; 07-10-2009 at 03:49 PM.
 
Old 07-24-2009, 10:56 AM   #10
motdman
LQ Newbie
 
Registered: Jul 2009
Posts: 6

Original Poster
Rep: Reputation: 0
syntax error....near "$search chomp"
aborted due to compilation errors

Any ideas?
 
Old 07-24-2009, 02:45 PM   #11
micxz
Senior Member
 
Registered: Oct 2002
Location: CA
Distribution: openSuSE, Cent OS, Slackware
Posts: 1,131

Rep: Reputation: 75
how about just:

Code:
chomp($search = <STDIN>);
if you wanted to use the way you have it you need $search=chomp...but seems redundant.

Last edited by micxz; 07-24-2009 at 10:45 PM. Reason: add red tags for the equal sign
 
Old 07-27-2009, 02:22 AM   #12
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.10, Centos 7.5
Posts: 17,565

Rep: Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425Reputation: 2425
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
starting mysql server using perl script vinaytp Linux - Newbie 1 05-20-2009 06:32 AM
MySQL Updates With Null When Perl Script Run From Shell Script ThisGuyIKnow Programming 6 08-12-2008 10:56 AM
Perl script/mysql select query from a file mcdrr Programming 12 06-05-2007 01:00 AM
How to Encrypt <STDIN> in PERL 5.8 SPo2 Programming 2 01-21-2006 01:46 AM
Perl Script /MySQL Problem domquem Programming 2 01-10-2006 07:57 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 07:40 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration