in reply to perl (mysql) question...

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.

After Compline,
Zaxo

Replies are listed 'Best First'.
Re: Re: perl (mysql) question...
by CountZero (Bishop) on Dec 08, 2003 at 06:32 UTC
    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

[reply]
[d/l]
[select]
Re: Re: perl (mysql) question...
by kiat (Vicar) on Dec 08, 2003 at 07:03 UTC
    '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 :)

[reply]

      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

[reply]
      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

[reply]

In Section Seekers of Perl Wisdom