I'm doing a basic 'prepare' and 'execute'. I've tried this with variable substitutions ('?') and hard-coded SQL. What you see here is what I've been primarily working with - a single SQL call (1 per execution) with the search words inserted directly into the SQL.
Here's the stripped down code:
#! /usr/bin/perl
# wwfindt.pl - Find articles in WW Index databases, return in table
use DBI;
use strict;
my $server = "Falcon";
my $db = "wwindex";
# database-related variables
my $sqlString;
my $rows;
my ($dbh, $sth);
my @result;
# Counters
my $recs = 0;
# Variables
my $searchWords;
print "WWFindt.pl starting.\n";
# Connect to MYSQL
$dbh = DBI->connect("DBI:mysql:host=".$server.";database=".$db,"guest"
+,"",{RaiseError=>1}) or die "Failed to Connect! $DBI::errstr";
print "Connected to database '$db' on server '$server'\n";
#print "Enter search words:";
#chomp($words = <>);
$searchWords = "saw blade storage";
# Issue SQL and check results
$sqlString = join(" ", "SELECT Source.SourceName, SourceDateVolumeIssu
+e.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIss
+ue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.Ar
+ticleSynopsis",
"FROM (Article, Source, SourceDateVolumeIssue)",
"WHERE (ArticleTitle LIKE '%".$searchWords."%' OR ArticleSynopsis LIKE
+ '%".$searchWords."%') AND (SourceDateVolumeIssueId = Article.Article
+SourceId) AND (Source.SourceId = SourceDateVolumeIssue.SourceId)",
"ORDER BY Source.SourceName, SourceDateVolumeIssue.SourceVolume, Sourc
+eDateVolumeIssue.SourceIssue, Article.ArticlePage");
print "SQL: $sqlString\n";
$sth = $dbh->prepare($sqlString);
$sth->execute();
$rows = $sth->rows();
print "DB Query done. $rows rows returned.\n";
if ($rows == 0) {
print "** Query for Title or Synopsis containing '$searchWords' di
+d not find anything.\n\n";
}
else {
while(@result = $sth->fetchrow_array()) {
print "Name='$result[0]', Date/Vol/Iss=$result[1]/$result[2]/$
+result[3], Page=$result[4], Title='$result[5]'\n";
}
}
print "WWFindt.pl completed processing.\n";
$sth->finish;
$dbh->disconnect;
exit (0);
Here are the results:
dave@davesdell:~/Perl$ ./wwfindt.pl
WWFindt.pl starting.
Connected to database 'wwindex' on server 'Falcon'
SQL: SELECT Source.SourceName, SourceDateVolumeIssue.SourceDate, Sourc
+eDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Art
+icle.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis FROM
+(Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LIKE '%s
+aw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%') AND
+ (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Source.Sour
+ceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceName, So
+urceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue,
+Article.ArticlePage
DB Query done. 1 rows returned.
Name='ShopNotes', Date/Vol/Iss=Sept/Oct, 2007/16/95, Page=4, Title='Ti
+ps for Your Shop'
WWFindt.pl completed processing.
dave@davesdell:~/Perl$
Here are pertinent trace messages:
mysql_st_internal_execute MYSQL_VERSION_ID 50137
>parse_params statement SELECT Source.SourceName, SourceDateVolumeIssu
+e.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIss
+ue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.Ar
+ticleSynopsis FROM (Article, Source, SourceDateVolumeIssue) WHERE (Ar
+ticleTitle LIKE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw b
+lade storage%') AND (SourceDateVolumeIssueId = Article.ArticleSourceI
+d) AND (Source.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY So
+urce.SourceName, SourceDateVolumeIssue.SourceVolume, SourceDateVolume
+Issue.SourceIssue, Article.ArticlePage
<- dbd_st_execute returning imp_sth->row_num 1
<- execute= 1 at wwfindt.pl line 38
-> rows for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85816f8) thr
+#857e008
<- rows= '1' at wwfindt.pl line 40
-> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85
+816f8) thr#857e008
-> dbd_st_fetch
dbd_st_fetch for 08653d58, chopblanks 0
dbd_st_fetch result set details
imp_sth->result=086dab98
mysql_num_fields=7
mysql_num_rows=1
mysql_affected_rows=1
dbd_st_fetch for 08653d58, currow= 1
<- dbd_st_fetch, 7 cols
<- fetchrow_array= ( 'ShopNotes' 'Sept/Oct, 2007' '16' '95' '4' 'T
+ips for Your Shop' 'Saw blade storage case; Conduit compass & router
+trammel; Shop-made gouge block; Circle cutter set-up gauge.' ) [7 ite
+ms] row1 at wwfindt.pl line 47
-> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85
+816f8) thr#857e008
Finally, results from the SQL call in PHPAdmin:
SQL result
Host: Falcon
Database: wwindex
Generation Time: Oct 17, 2011 at 07:11 PM
Generated by: phpMyAdmin 3.3.2deb1 / MySQL 5.1.41-3ubuntu12.10
SQL query: SELECT Source.SourceName, SourceDateVolumeIssue.SourceDate,
+ SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssu
+e, Article.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis
+ FROM (Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LI
+KE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%
+') AND (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Sourc
+e.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceNa
+me, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceI
+ssue, Article.ArticlePage LIMIT 0, 30 ;
Rows: 6
SourceName SourceDate SourceVolume SourceIssue Article
+Page ArticleTitle ArticleSynopsis
ShopNotes July, 1992 0 4 28 Shop Solutions Saw
+ blade storage rack; Edge jointing tip; Installing threaded inserts;
+Clamping irregular shapes; File handles
ShopNotes Sept/Oct, 2007 16 95 4 Tips for Your Sho
+p Saw blade storage case; Conduit compass & router trammel; Shop-
+made gouge block; Circle cutter set-up gauge.
ShopNotes Sept/oct, 2009 18 107 44 Saw Blade Stora
+ge Three storage systems that protect saw blades while keeping th
+em organized.
Wood Dec/Jan, 2008/2009 25 188 18 Shop Tips Ex
+tension for blocked drill crank; Pushstick for safer belt sanding; Ea
+sy-to-reach scrollsaw blade storage; Using metalworking clamps for ex
+tended reach; Adding hardboard zero-clearance plate to circular saw;
+Shoe organizer to store bottles and cans; Economical corner clamps; U
+sing chip cans for storing paint rollers on the job.
Woodsmith June, 2002 24 141 4 Tips & Techniques
+ PVC Pipe Clamp Storage; Name that cord; Quicker panels on the table
+ saw; Saw blade storage; Tap & die storage; Laminate liner for shop v
+acuum.
Woodsmith Dec, 2002 24 144 4 Tips & Techniques
+ Dangling drawer stops; PVC tool storage; Cork clamping; Mess-free cl
+eaning; Saw blade storage;
(I have a PDF of this PHPAdmin result set for easier reading.)
Again, any suggestions are appreciated!!
Dave
|