How can I check if a database query will return results?

Question

Our website uses Perl to provide a simple mechanism for our HR people to post vacancies to our website. It was developed by a third party, but they have been long since kicked into touch, and sadly we do not have any Perl skills in-house. This is what happens when Marketing people circumvent their in-house IT team!

I need to make a simple change to this application. Currently, the vacancies page says 'We currently have the following vacancies:', regardless of whether there are any vacancies! So we want to change it so that this line is only displayed at the appropriate times.

I could, obviously, start to learn a bit of Perl, but we are already planning a replacement site, and it certainly won't be using Perl. So since the solution will be trivial for those with these skills, I thought I'd ask for some focused help.

Below is the start of the procedure that lists the vacancies.

sub list {
  require HTTP::Date;
  import HTTP::Date;

  my $date = [split /\s+/, HTTP::Date::time2iso(time())]->[0];

  my $dbh = DBI->connect($dsn, $user, $password)
    || die "cannot connect to $database: $!\n";

  my $sql = <<EOSQL;
SELECT * FROM $table where expiry >= '$date' order by expiry
EOSQL

  my $sth = $dbh->prepare($sql);
  $sth->execute();


  while (my $ref = $sth->fetchrow_hashref()) {
    my $temp  = $template;
    $temp     =~ s#__TITLE__#$ref->{'title'}#;

    my $job_spec = $ref->{'job_spec'};

...etc...

The key line is while (my $ref = $sth->fetchrow_hashref()) {. I'm figuring that this is saying 'while I can pull off another vacancy from the returned recordset...'. If I place my print statement before this line, it will always be shown; after this line and it was be repeated for every vacancy.

How do I determine that there are some vacancies to be displayed, without prematurely moving through the returned recordset?

I could always copy the code within the while loop, and place it within an if() statement (preceding the while loop) which will also include my print statement. But I'd prefer to just have the simpler approach of If any records then print "We currently have.." line. Unfortunately, I haven't a clue to code even this simple line.

See, I told you it was a trivial problem, even considering my fumbled explanation!

TIA

Chris

Answer 1

A really simple way would be:

$sth->execute();

my $first = 1;
while (my $ref = $sth->fetchrow_hashref()) {
    if( $first ) {
        print "We currently have the following vacancies:\n";
        $first = 0;
    }
    my $temp  = $template;
    ...
}
if( $first ) {
    print "No vacancies found\n";
}

Answer 2

If you are using Mysql, the "rows" method works just fine:

$sth->execute();

if($sth->rows) {
  print "We have data!\n";
}

while(my $ref = $sth->fetchrow_hashref()) {
...
}

The method, and some caveats, are documented in extensive detail in "perldoc DBI". Always start with "perldoc".