Hi Dave,

> I am trying to create a registration area that uses a nickname and
> pair.
> I need both to be unique.
> So how do I that either do not already exist then if unique insert
> db.
> if nickname exist then error = "sorry..."
> if email exist then error = "sorry..."
> else must be unique
> insert.
> I have been trying for hours know but cant get the twin check to work.
> Can get single check to work though.

You do not mention which DBMS you are using but they must all offer
something similar (I shall work with MySQL as an example). If you had
posted your code we might have been able to work directly at that level
to achieve the final tweak...

Set up the db-tbl so that the nickname and email fields are both
constrained as UNIQUE. You can now attempt to INSERT row data and MySQL
will perform all the checks for uniqueness. After the (single row at a
time) INSERT operation check mysql_affected_rows() and if it returns <1
then the INSERT failed (and assuming all else is equal) we'll assume
because the data is not DISTINCT. If you do not need to know which of
the two fields fails this is fine.

If you need to know which field failed the uniqueness test, then precede

SELECT COUNT(nickname) AS NicknameTaken, COUNT (email) AS EmailTaken ...
WHERE nickname=value OR email=value

and then running a PHP IF( >0 ) across the two returned values will tell
you that the data is non-DISTINCT and which field(s) are at issue. NB
still follow the actual INSERT/UPDATE with an affected rows check if
your tbl is not locked and multi-user access is allowed!

Man ref: 6.5.3 CREATE TABLE Syntax
[table_options] [select_statement]

  col_name type [NOT NULL | NULL] [DEFAULT default_value]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
In MySQL, a UNIQUE key can have only distinct values. An error occurs if
you try to add a new row with a key that matches an existing row.

Man ref: 6.4.3 INSERT Syntax (http://www.mysql.com/doc/I/N/INSERT.html)
Similarly 6.4.5 UPDATE Syntax (http://www.mysql.com/doc/U/P/UPDATE.html)
applies if changing/updating values

        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
If you specify the keyword IGNORE in an INSERT with many value rows, any
rows that duplicate an existing PRIMARY or UNIQUE key in the table are
ignored and are not inserted. If you do not specify IGNORE, the insert
is aborted if there is any row that duplicates an existing key value.

PHP man ref:


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to