Why doesn't my Perl DBI query return any results in a CGI script?

Question

I'm using DBI for the first time (and not long started Perl [2 weeks]) and I can't seem to get any results from the database. Here's what I have:

if( defined( $get{findAllPages} ) && defined( $post{ki} ) ){

   my ($database, $hostname, $port, $password, $user );

   $database = "#########";
   $hostname = "localhost";
   $password = "#########";
   $user = "###########";
   my $KI = $post{ki};

   # connect to the database
   my $dsn = "DBI:mysql:database=$database;host=$hostname;";
   my $dbh = DBI->connect($dsn, $user, $password);
   my $sth = $dbh->prepare("SELECT * FROM accounts WHERE KI = '" . $dbh->quote($KI) . "' ") or die "Could not select from table";
   $sth->execute();
   if( $sth->rows != 0 ) {
      my $ref = $sth->fetchrow_hashref();
      my $domain = $ref->{website};
      my $DB_username = $ref->{db_name};
      my $DB_password = $ref->{db_pass};
      $sth->finish();
      $dbh->disconnect();

      print "domian: " . $domain . "<br />\n";

      chomp(my $url = trim($domain));

As it stands it checks to see if KI is correct and then checks row amount which works. The bit I can't get to work is returning values from the array;

my $ref = $sth->fetchrow_hashref();
my $domain = $ref->{website};
my $DB_username = $ref->{db_name};
my $DB_password = $ref->{db_pass};

If anyone can let me know where I am going wrong it would be much appreciated.

Answer 1

Try to use DBI's error handling to see what's wrong. See Programming the Perl DBI, chapter 4 (Error handling) or "DBI::mysql error handling" on Perlmonks for references.

Answer 2

What I often do when I can't see what is going on:

use Data::Dumper;

my $ref = $sth->fetch();

print Dumper $ref;

and you'll see the data structure layout. Sometimes it is a matter of incorrect indexing into an array or hash.