How do I check if a database row exists using DBD::mysql and Perl?

Question

I'm working with a MySQL database and need to check if a row is already there before deciding which queries to run.

The table I'm working on is something like this:

player(playerid, nickname, data1, data2, data3)

Where "playerid" is an auto-incremented number and "nickname" is unique.

I tried some queries with COUNT, COALESCE, fetch->rows, etc.. and got nowhere. I already saw this question, but couldn't solve anything.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html doesn't help? — khachik, Nov 14 '10 at 18:03
Perhaps my greatest annoyance with SQL is its lack of a (standard) command meaning "just put this in the database and I don't care whether it INSERTs a new row or UPDATEs an existing one" so that you don't have to do the "if SELECT then UPDATE else INSERT" dance. There are many cases where it's important whether you INSERT or UPDATE, so the current model certainly has its uses, but they are, in my experience, the exception rather than the rule. (MySQL's "INSERT...ON DUPLICATE" syntax doesn't quite address this either, since you still have to manage the INSERT and UPDATE separately.) — Dave Sherohman, Nov 15 '10 at 07:01
@khachik: It wasn't merely a matter of whether to INSERT or UPDATE, it also influenced which Perl code was going to be run. Thanks anyway for the reference! — Gurzo, Nov 18 '10 at 09:22

score 8 · Accepted Answer · edited May 23 '17 at 10:30

8

Could you please post the specific query with SELECT COUNT(*) that didn't work and what the problem was?

The query should be modeled upon this answer: How do I know how many rows a Perl DBI query returns?

Assuming your "row is already there" definition is "the player with the given nickname is there", the query would be:

my $th = $dbh->prepare(qq{SELECT COUNT(1) FROM player WHERE nickname='$nickname'});
$th->execute();
if ($th->fetch()->[0]) {
    ....
} # Code stolen shamelessly from the link above

edited May 23 '17 at 10:30

Community

1
1

answered Nov 14 '10 at 17:56

DVK

126,886
32
213
327

This works. I used $th->fetch() when referring to the linked question, so it was probably this. Thanks. – Gurzo Nov 14 '10 at 18:05
If someone downvoted you, it wasn't me. As a matter of fact, I voted you up. – Gurzo Nov 27 '10 at 13:31
2

Old question, don't know what I searched for that brought me to this page. I am guessing the down vote may be because this for several reasons: (1) inserting `$nickname` directly into the query opens you up to SQL injections; (2)Why use prepare for something you aren't calling more than once? There is no benefit to caching here; (3) DBI has methods that allow you to query and extract the result in one step (e.g., `selectall_array`); (4) If my SQL behaves like Postgres, your if statement may always pass even when you don't receive any records. You may need to check for `0E0` explicitly – vol7ron Jul 03 '14 at 21:32

score 1 · Answer 2 · answered Nov 14 '10 at 18:01

1

You can use some dbh/sth method to check whether the result set is empty:

my $stmt = 'SELECT playerid FROM player WHERE playerid = ?';

if ($dbh->selectrow_array($stmt, undef, $id)) {
    print "The row exists";
}

answered Nov 14 '10 at 18:01

Eugene Yarmash

142,882
41
325
378

How do I check if a database row exists using DBD::mysql and Perl?

2 Answers2