Is there a way to check if a DBI->execute statement returns an empty set? The purpose is that I want to create a table, and then ask for username, if the username isn't in the table, (check for empty fetch() statement), then add to table. Any suggestions?
$sth = $dbh->prepare("SELECT * FROM table_name_here");
$sth->execute();
if(...$sth->fetch() was empty...)
do something...
Try that :
$sth = $dbh->prepare("SELECT * FROM table_name_here");
$sth->execute();
unless ($sth->fetch()) { do something..; }
Is there a way to check if a DBI->execute statement returns an empty set
Yes.
$sth = $dbh->prepare("SELECT * FROM table_name_here");
$sth->execute();
unless( $sth->rows ) {
#empty set
}
SELECT statements. From the documentation of the 'DBI' module: "For 'SELECT' statements, it is generally not possible to know how many rows will be returned except by fetching them all"I suggest you do a preliminary fetch of the number of records, like this
my ($records) = $dbh->selectrow_array('SELECT count(*) FROM table_name_here');
One possible approach to do that:
my $row_count = 0;
while (my @ary = $sth->fetchrow_array() ) { ...; $row_count++; }
unless ($row_count) {
...
}
But I can't help wondering why do you need to use fetchrow, and not fetchall, for this specific case. In fact, I'd reorganize it this way:
my ($count) = $dbh->selectrow_array('
SELECT COUNT(*) FROM users WHERE username = ?'
undef, $username);
if ($count) { ... }
