CODEWITHSUNDEEP
mysqlperlselectdbi
Melody
Melody

Reputation: 19

What does 'SELECT COUNT(*)' with Perl DBI return?

I am querying a MySQL database with Perl DBI, and my goal is just to see if my SELECT query finds anything (i.e., rows > 0). In other words, if the SELECT query finds anything at all.

SELECT COUNT(*) returns a number to me, like 1, 2 or 3 when I test out the query in MySQL.

However, I am wondering if this number is returned directly to my Perl code through DBI and gets assigned to my $sth variable.

my $sth = $dbh->prepare("SELECT COUNT(*) FROM `USERS` WHERE FIRSTNAME = ? AND LASTNAME = ?");

$sth->execute($firstName, $lastName);

if ($sth > 0){                  #Check if found any matches
    print "Found something!";

} else { 
    print "No matches!";
}

I guess my main question is whether the SELECT COUNT(*) returns a number and saves directly in my $sth variable, or if I need to do something else to $sth to figure out how many rows were found with the query.

Much appreciated.

Upvotes: 0

Views: 5236

Answers (1)

Borodin
Borodin

Reputation: 126722

You're misunderstanding how DBI works. To quote the documentation

The typical method call sequence for a SELECT statement is:

prepare,
  execute, fetch, fetch, ...
  execute, fetch, fetch, ...
  execute, fetch, fetch, ...

So you have the prepare and the execute in place, but you're never actually fetching the data that the SQL engine has discovered. Testing the value of $sth will never give you any useful information as it is a reference to a DBI statement handle object, and if it is ever zero then something critical has gone wrong

To find the number of users with the given first and last names, you would write something like this 

my $sth = $dbh->prepare('SELECT COUNT(*) FROM users WHERE firstname = ? AND lastname = ?');

$sth->execute($first_name, $last_name);

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

print $count ? "Found something!\n" : "No matches!\n";

There are many varieties of the fetch method, and which one you choose depends on whether you are expecting one row or many rows; one column or many columns; whether you want to fetch all the rows at once or just one at a time; and whether a simple list of columns values will do or if you want the values identified by their column name in the form of a Perl hash

In this case you have asked for just a single row containing a single column -- the count -- so fetchrow_array allows the result to be assigned directly to a scalar variable $count, and finally the output can be prepared according whether this value is zero

Upvotes: 3

Related Questions