bory has asked for the wisdom of the Perl Monks concerning the following question:

Hello, great perlmonks.How can I check if a mysql database is empty and print a message that it's empty. I have tried the following code: 
my $sth=$dbh->prepare("SELECT Email FROM foo ");
  $sth->execute();
  while (my $email=$sth->fetchrow_array ) {
                 if ($email=""){
                               print "Database is empty";
                                }
                                else {
                                     print $email;
                                     }
                                     }
$sth->finish;

[download]
In this way it's printing nothing when the database is empty, and when there is something in the database it's printing "database is empty" for every $email. Thank you very much for your time.

Replies are listed 'Best First'.
Re: How do i check if a mysql database is empty?
by edan (Curate) on Nov 27, 2003 at 08:12 UTC

    I think you want to check if a table is empty, not a database, since the code you present seems to be an attempt at that. Try looking at the following code snippet, and see if it makes sense. I didn't add any error checking here, since it's just an example of the concept. The key here is COUNT(*) which is an optimized (at least in MySQL) way to get a row count on a table. HTH.

    my $table = "foo";

my $sql = "SELECT COUNT(*) FROM $table";

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

my ($count) = $sth->fetchrow_array;

if ($count)
{
    print "$table has $count rows!\n";
}
else
{
    print "$table is empty\n";
}

    [download]
    --
    3dan

[reply]
[d/l]
Re: How do i check if a mysql database is empty?
by Itatsumaki (Friar) on Nov 27, 2003 at 08:02 UTC

    Try this:

    my $sql = 'SELEcT Email FROM foo';
my $sth = $dbh->prepare($sql);
$sth->execute();
if (my $email = $sth->fetchrow_array()) {
    print "$email\n";
    }
else {
    print "Database Empty\n";
    }
$sth->finish();
$dbh->disconnect();

    [download]

    Update: I think you want to print out all e-mails, like this:

    my $sql = 'SELECT Email FROM foo';
my $sth = $dbh->prepare($sql);
$sth->execute();

if (my $email = $sth->fetchrow_array()) {
    print "$email\n";
    while ($email = $sth->fetchrow_array()) {
        print "$email\n";
        }
     }
else {
    print "Database empty\n";
    }

    [download]

    Update 2: Fixed typo in first solution.

[reply]
[d/l]
[select]
Re: How do i check if a mysql database is empty?
by holo (Monk) on Nov 27, 2003 at 08:03 UTC

    $email="" does not do what you think. It computes the numeric values for both sides and compares. You whould need eq for that. See perlop for that.

    Next, you don't need to select Email from the table to check if it is empty or not. Use SELECT COUNT(*) FROM foo and get the number of rows. That way, you know how many rows of data you have. i.e. If you have 0 rows, you have an empty table. (not an empty database)

    Update: oops! Thanks 3dan (++)

[reply]
[d/l]
[select]

      $email="" does not do what you think. It assigns the empty string to $email. No comparison takes place.

      --
      3dan

[reply]

Back to Seekers of Perl Wisdom