Listings 4-1 to 4-9 show the complete code for the programs in the chapter.
Listing 4-1: select.pl
01: #!/usr/bin/perl -w 02: # 03: # select1.pl 04: # Chapter 4 05: # Listing 4-1 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: my $counter = 1; 15: while(my $record = $sth->fetch){ 16: print "$counter: "; 17: for my $field (@$record){ 18: print "$field - "; 19: } 20: $counter++; 21: print "\n\n"; 22: }
Listing 4-2: select2.pl
01: #!/usr/bin/perl -w 02: # 03: # select2.pl 04: # Chapter 4 05: # Listing 4-2 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library WHERE isbn = ‘1565924347'); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: while(my $record = $sth->fetch){ 15: for my $field (@$record){ 16: print "$field\n"; 17: } 18: print "\n\n"; 19: }
Listing 4-3: select3.pl
01: #!/usr/bin/perl -w 02: # 03: # select3.pl 04: # Chapter 4 05: # Listing 4-3 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library ORDER BY publisher); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: my $counter = 1; 15: while(my $record = $sth->fetch){ 16: print "Book #$counter:\n"; 17: for my $field (@$record){ 18: print "$field\n"; 19: } 20: $counter++; 21: print "\n"; 22: }
Listing 4-4: select4.pl
01: #!/usr/bin/perl -w 02: # 03: # select4.pl 04: # Chapter 4 05: # Listing 4-4 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT COUNT(*) FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: while(my $record = $sth->fetch){ 15: print "You have $record->[0] books."; 16: print "\n"; 17: }
Listing 4-5: select5.pl
01: #!/usr/bin/perl -w 02: # 03: # select5.pl 04: # Chapter 4 05: # Listing 4-5 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT COUNT(*), publisher FROM library GROUP BY publisher); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: while(my $record = $sth->fetch){ 15: for my $field (@$record){ 16: print "$field "; 17: } 18: print "\n"; 19: }
Listing 4-6: select6.pl
01: #!/usr/bin/perl -w 02: # 03: # select6.pl 04: # Chapter 4 05: # Listing 4-6 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT isbn, price, title FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: while(my @array = $sth->fetchrow_array){ 15: no warnings; 16: my $index = 0; 17: for my $data (@array){ 18: print "\$array[$index] contains $data\n"; 19: $index++; 20: } 21: print "\n"; 22: }
Listing 4-7: select7.pl
01: #!/usr/bin/perl -w 02: # 03: # select7.pl 04: # Chapter 4 05: # Listing 4-7 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: my $ref = $sth->fetchall_arrayref; 15: my $record_idx = 0; 16: for my $rec (@$ref){ 17: no warnings; 18: my $field_idx = 0; 19: for my $field (@$rec){ 20: print "ref[$record_idx][$field_idx] = $field\n"; 21: $field_idx++; 22: } 23: $record_idx++; 24: print "\n"; 25: } 26: print "\n"; 27: print "$ref->[57][1]\n\n";
Listing 4-8: select8.pl
01: #!/usr/bin/perl -w 02: # 03: # select8.pl 04: # Chapter 4 05: # Listing 4-8 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: my $counter = 1; 15: while(my $record = $sth->fetchrow_hashref){ 16: no warnings; 17: print "Book #$counter\n"; 18: print "Title: $record->{title}\n"; 19: print "Author: $record->{author}\n"; 20: print "Publisher: $record->{publisher}\n"; 21: print "Cost: $record->{price}\n"; 22: print "\n"; 23: $counter++; 24: }
Listing 4-9: select9.pl
01: #!/usr/bin/perl -w 02: # 03: # select8.pl 04: # Chapter 4 05: # Listing 4-9 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass") 10: or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute; 14: my $counter = 1; 15: while(my $record = $sth->fetchrow_hashref){ 16: for my $field (keys %$record){ 17: $record->{$field} = "N/A" unless $record->{$field}; 18: $record->{$field} =~ s/^\W+//; 19: $record->{$field} =~ s/\015?\012//g; 20: } 21: print "Book #$counter\n"; 22: print "Title: $record->{title}\n"; 23: print "Author: $record->{author}\n"; 24: print "Publisher: $record->{publisher}\n"; 25: print "ISBN: $record->{isbn}\n"; 26: print "Cost: $record->{price}\n"; 27: print "Pub Date: $record->{pubdate}\n"; 28: print "\n"; 29: $counter++; 30: }