#! /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, SourceDateVolumeIssue.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis",
"FROM (Article, Source, SourceDateVolumeIssue)",
"WHERE (ArticleTitle LIKE '%".$searchWords."%' OR ArticleSynopsis LIKE '%".$searchWords."%') AND (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Source.SourceId = SourceDateVolumeIssue.SourceId)",
"ORDER BY Source.SourceName, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.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' did 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);

##</code><code>##

dave@davesdell:~/Perl$ ./wwfindt.pl
WWFindt.pl starting.
Connected to database 'wwindex' on server 'Falcon'
SQL: SELECT Source.SourceName, SourceDateVolumeIssue.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis FROM (Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LIKE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%') AND (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Source.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceName, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Article.ArticlePage
DB Query done.  1 rows returned.
Name='ShopNotes', Date/Vol/Iss=Sept/Oct, 2007/16/95, Page=4, Title='Tips for Your Shop'
WWFindt.pl completed processing.
dave@davesdell:~/Perl$ 

##</code><code>##

mysql_st_internal_execute MYSQL_VERSION_ID 50137
>parse_params statement SELECT Source.SourceName, SourceDateVolumeIssue.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis FROM (Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LIKE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%') AND (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Source.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceName, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.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)~0x85816f8) 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' 'Tips for Your Shop' 'Saw blade storage case; Conduit compass & router trammel; Shop-made gouge block; Circle cutter set-up gauge.' ) [7 items] row1 at wwfindt.pl line 47
    -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85816f8) thr#857e008

##</code><code>##

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.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis FROM (Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LIKE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%') AND (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Source.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceName, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Article.ArticlePage LIMIT 0, 30 ;
Rows: 6
SourceName 	SourceDate 	SourceVolume 	SourceIssue 	ArticlePage 	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 Shop 	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 Storage 	Three storage systems that protect saw blades while keeping them organized.
Wood 	Dec/Jan, 2008/2009 	25 	188 	18 	Shop Tips 	Extension for blocked drill crank; Pushstick for safer belt sanding; Easy-to-reach scrollsaw blade storage; Using metalworking clamps for extended reach; Adding hardboard zero-clearance plate to circular saw; Shoe organizer to store bottles and cans; Economical corner clamps; Using 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 vacuum.
Woodsmith 	Dec, 2002 	24 	144 	4 	Tips & Techniques 	Dangling drawer stops; PVC tool storage; Cork clamping; Mess-free cleaning; Saw blade storage;