Standard SQL (dunno whether mysql supports it): add a UNIQUE modifier in the CREATE TABLE statement at the e-mail column. Afterwards, your dbase won't allow duplicate e-mail addresses.
Be sure to add decent error-checking to inform your prospective users why their entry was not allowed.
Jeroen
"We are not alone"(FZ) | [reply] |
| [reply] |
If you make email address a primary key on the table storring the information in mysql, it will prevent dups. This may have other effects (performance, etc), i have only used mysql once, so i am not sure. Also, once you do this it will actually error when you try to insert new rows, so it makes it trickier to do inert error checking.
Also, a word of caution, to prevent dup emailing, normalize the email addresses before you load them. This may seem silly, but the RFC allows for the same email address to be written a million ways (well, nearly infinte if you think about number of character/position combonations).
you could also query the whole table out into a hash.BUT i do NOT suggest that ... talk about missing the scalability bus.
i had a memory leak once, and it ruined my favorite shirt. | [reply] |
| [reply]
[d/l] |
| [reply] |
Is there something to issue in PERL to check without connecting to MySQL
No. Your data is held in the database, you will need to
connect to and query the database to find out what is in it. So this appears very much to be a mySQL question, not Perl.
To get it back to Perl, perhaps you could have a button (on the page where you add new people) that fetches the names and email address of those currently in the database. You could put these in nice table tags (and hidden tags) and check against them in the target perl script, using CGI, before doing your database connection.
But, as stated in previous replies, the mySQL homepage is an adequate place to learn about the PRIMARY KEY and UNIQUE attributes available in mySQL.
To be honest, if what you really want is exactly (and no more than) what you described in your question, I might consider PHP. But don't tell anyone that I said that!
<a href="http://www.graq.co.uk">Graq</a> | [reply] |
Many good suggestions concerning unique.
Another technique is available for checking whether a given value appears in a column, without any needing to fetch any data from your backend, and that is:
select 1 from mytable where mycol='foo'
You'll either get a row back, or nothing. In the general case, this is about as cheap as it gets.
--g r i n d e r
| [reply]
[d/l] |
I agree with grinder here. I would think this is the best method of inserting unique rows into a database. The table should have the unique constraint enabled to enforce the integrity. Good application design would *not* have the integrity enforced externaly
In my mind it would be better to make to graceful calls to the database, rather than making one and traping any errors.
| [reply] |
