I am using DBI with MySQL and am looking for a way to test if a query returned an empty recordset.

You might have better luck by rephrasing the question as "how many users with this name exist?"

Try something like this (untested):

  my $sth = $db1->prepare(
    "SELECT count(*) FROM users WHERE username = ?"
  );
  $sth->execute($Username_POST);
  my $rs = $sth->fetchall_arrayref();
  my $count = $rs->[0]->[0];
[download]

I have never seen that syntax before. It is very sisinct; I like it. Thanks for providing your code. Joe

As allready mentioned I would highly recomend using placeholders. For more info on them check the nodes out...
Variable placeholders with DBI
What are placeholders in DBI, and why would I want to use them?
DBD::mysql and binding placeholders
Understanding placeholders

S t r e b e n M ö n c h (Yes, I like FoxtrotUniform's sig =P)

dws's solution works great if you only need to check if a user exists or not (although I'd use fetch() and $rs->[0] instead of fetchall_arrayref() and $rs->[0]->[0]). If you want to check if the user exists or not, and do something with the record if he does, try the following:

$rs1 = $db1->prepare("SELECT ...");
$rs1->execute(...);
unless ($row = $rs1->fetch())) {
  die("User does not exist\n");
}
... check supplied password against the one in $row ...
[download]

Very nice way of putting it. That seems like it would give you more control over the variables inside the $rs1 if a user did exist. Thanks for the post. Joe

my $sql = "SELECT username FROM users WHERE username = ?";
my $user = $dbh->selectrow_array( $sql,{},$Username_POST );
if( $user ) { die "User '$user' exists; }
else { ... # user doesn't exist }
[download]

Update Or, if you want the whole row:

my $sql = "SELECT username FROM users WHERE username = ?";
my @row = $dbh->selectrow_array( $sql,{},$Username_POST );
if( @row ) { die "User exists"; }
else { ... # user doesn't exist }
[download]

Wow, that's an interesting use of the selectrow_array function. I didn't know that you could have it just return one variable. Very neat. Thanks, Joe

    $rows =  $rs1->rows;
    if (!$rows) { 
        report_error("username does not exist"); 
    } else { 
        report_error("username_exists"); 
    }
[download]

That seems simple enough. I'll give that a try. Thanks for the input. Joe

if($rs1 != 'NULL')
[download]

if($rs1 ne "0E0")
[download]

The DBI execute returns the number of rows affected. In the case of a select, it returns the number of rows found. Argh! My bad... jZed caught my mistake. So, something like:

$rs1->execute;
if (@{$rs1->fetchrow_array}) {
   report_error("username_exists");
} else {
   report_error("username does not exist");
}
[download]

You could also have fun with the ternary operator:

$rs1->execute;
report_error( (@{$rs1->fetchrow_array) ? "username_exists" : "username
+ does not exist" );
[download]

The ternary operator works by checking the truth of the first part (before the ?) and returning the second part if it is true, else returning the third part.

No, execute only returns the rows affected with *NON-SELECT* statement handles. From the DBI docs:

"For SELECT statements, execute simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling execute. The execute method does not return the number of rows that will be returned by the query (because most databases can't tell in advance), it simply returns a true value."