Hi @all,

i've got a table (user) with 2 unique keys (username and email).
Database is Mysql 5.1 with myisam-engine.

I'm doing an SELECT for username and email to check for duplicate entries
(and display a message to the user). If there are no duplicates, i do an
INSERT. Because mysql blocks for some seconds sometimes before the insert
(maybe a locking issue), the user resubmitts the html-form, which results in
duplicate-key-violations. I dont use transactions for the moment.

---
//check if email is in the database
$rs = sql("SELECT `username` FROM `user` WHERE `email`='$email'");
if (mysql_num_rows($rs) > 0)
$email_exists = true;
else
$email_exists = false;

//check if username is in the database
$rs = sql("SELECT `username` FROM `user` WHERE `username`='$username'");
if (mysql_num_rows($rs) > 0)
$username_exists = true;
else
$username_exists = false;

if (!$username_exists && !$email_exists)
{
sql("INSERT INTO `user` ( `user_id`, `username`, `email`) VALUES ('',
'$username', '$email')");
}
else
// display error to the user
---


I think there are 2 possible solutions:

1) Use transactions or lock table
Because i've no experience with transactions, i cannot estimate the
performance-impact on database and don't sure if myisam-engine supports
that.

2) No checks before INSERT, but do INSERT INGORE INTO with check for errors

Is there any way to get the unique-key-name that caused the violation,
without parsing string-text?
---
mysql> INSERT INTO `user` (`username`, `email`) VALUES
('my-name','EMAIL REMOVED');
ERROR 1062 (23000): Duplicate entry 'my-name' for key 'username'
mysql> SHOW ERRORS;
+-------+------+-----------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------+
| Error | 1062 | Duplicate entry 'my-name' for key 'username' |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
---


Can someone point me to the right solution?


Thank you,
Oliver

Oliver Dietz wrote:
> Hi @all,
>
> i've got a table (user) with 2 unique keys (username and email).
> Database is Mysql 5.1 with myisam-engine.
>
> I'm doing an SELECT for username and email to check for duplicate entries
> (and display a message to the user). If there are no duplicates, i do an
> INSERT. Because mysql blocks for some seconds sometimes before the insert
> (maybe a locking issue), the user resubmitts the html-form, which results in
> duplicate-key-violations. I dont use transactions for the moment.
>
> ---
> //check if email is in the database
> $rs = sql("SELECT `username` FROM `user` WHERE `email`='$email'");
> if (mysql_num_rows($rs) > 0)
> $email_exists = true;
> else
> $email_exists = false;
>
> //check if username is in the database
> $rs = sql("SELECT `username` FROM `user` WHERE `username`='$username'");
> if (mysql_num_rows($rs) > 0)
> $username_exists = true;
> else
> $username_exists = false;
>
> if (!$username_exists && !$email_exists)
> {
> sql("INSERT INTO `user` ( `user_id`, `username`, `email`) VALUES ('',
> '$username', '$email')");
> }
> else
> // display error to the user
> ---
>
>
> I think there are 2 possible solutions:
>
> 1) Use transactions or lock table
> Because i've no experience with transactions, i cannot estimate the
> performance-impact on database and don't sure if myisam-engine supports
> that.
>
> 2) No checks before INSERT, but do INSERT INGORE INTO with check for errors
>
> Is there any way to get the unique-key-name that caused the violation,
> without parsing string-text?
> ---
> mysql> INSERT INTO `user` (`username`, `email`) VALUES
> ('my-name','EMAIL REMOVED');
> ERROR 1062 (23000): Duplicate entry 'my-name' for key 'username'
> mysql> SHOW ERRORS;
> +-------+------+-----------------------------------------------------+
> | Level | Code | Message |
> +-------+------+-----------------------------------------------------+
> | Error | 1062 | Duplicate entry 'my-name' for key 'username' |
> +-------+------+-----------------------------------------------------+
> 1 row in set (0.00 sec)
> ---
>
>
> Can someone point me to the right solution?
>
>
> Thank you,
> Oliver
>
>

Just insert it and check for the 1062 error code. If you get it, the
data were not unique.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

Hi,

> Just insert it and check for the 1062 error code. If you get it, the data
> were not unique.

thank you for posting.
As i wrote, the question is "why was the data not unique".

I will do the INSERT with IGNORE, check the affected rows count and if
affected rows = 0, then i will do a SELECT for username and email ... not
transactional, but should fix my problem.


Thank you & best regards,
Oliver