Hi Dave, > I am trying to create a registration area that uses a nickname and email > pair. > I need both to be unique. > So how do I that either do not already exist then if unique insert into > 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 the INSERT/UPDATE with: 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 (http://www.mysql.com/doc/C/R/CREATE_TABLE.html) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [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 INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [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: http://uk.php.net/manual/en/function.mysql-affected-rows.php Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
