The statement handle execute() method returns undef on error. The '0E0' return only applies to non-SELECT handles, so I'll guess that your problem comes from basing the logic on that.

You'd be better off using a placeholder in $sql, but the statement looks ok. Are you sure %table contains what you think at the time it is bound?

A better test would use 'SELECT COUNT(*) FROM foo WHERE nick=?', checking for definedness and testing the result of one of the $sth->fetch* methods for the result.

If 'nick' is a primary key, an INSERT will fail if the key already exists. That may be a better approach for this.

I think it is easier on the MySQL database to use the COUNT * ...-idiom to test whether a record exists, rather than relying on a failing insert because the primary key already exists.

The docs of MySQL state that COUNT * ... has been specifically optimised for such things.

Update:On second thought, if you have to insert the new data anyhow, then perhaps it is more resource-effective to rely on the "primary key already existing"-error, provided that most of the inserts are likely to succeed and such errors are rare.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

'nick' is not a primary key (in the main table 'members'). The primary key is an autocrement number, which is used as a foreign key in several other related tables. However, I do need the username to be unique in the table 'members' so that I do not have a situation where two members share the same name. Can I have username as the primary key and still use it as a foreign key in the related tables?

As this is my first foray into databases, I'm basing my gut feelings on decisions like that. So please pardon my question if it sounds silly :)

Yes, you can give your 'nick' column the UNIQUE attribute. That will make INSERT throw an error if a duplicate is attempted.

Since you will be searching the column by nick, it may be worthwhile to make it an INDEX, too. The combination makes a column behave as as if it were a primary key.

After Compline,
Zaxo

There are good reasons to have as your primary key an autoincrement value rather than the "nick".

Think of what would happen when a user changes his "nick". All foreign links to this nick would need to be changed as well if "nick" was the primary key.

Far better to define "nick" as UNIQUE (to disallow duplicates) and INDEX it (to speed up searches and access).

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

# return 1 (true) if user is found, 0 (false) if not found
sub check_exist {
  $username = shift;

  # do the necessary dbi conection...

  $sql = qq{SELECT count(*) FROM members WHERE nick=?};
  $sth = $dbh->prepare($sql);
  $sth->execute($username) or bail_out("Can not execute SQL query");
  my ($rowcount) = $sth->fetchrow();
  $dbh->disconnect();

  return $rowcount > 0;
}
[download]

if (check_exist($user)) {
   # handle_exception...
} else {
   # user does not exist
}
[download]

Your check_exists() sub exposes a race condition in a multi-user system, as two users selecting the same username at approximately the same time could both get the OK to use that username, with the second one getting an error on the insert (assuming you have a unique index or constraint on the "nick" column.

In general I prefer to let the database find out if a value is already taken as part of the insert operation. To do this you need to have a unique index on the column ("nick" in this case), and check for unique key violations during the insert.

I don't know MySQL, but you should be able to create indexes on column(s) beside the primary key column. In any case if a column is supposed to be unique then you should have a constraint that enforces this (that constraint in this case would be a unique index).

Michael