Identifying the field which is being duplicated!

I have this registration form which has several Unique fields. Now on insertion, I want to detect all the fields on which the duplicate values were tried. How can I do that? I am using MySQL server 5.5.8 and开发者_运维百科 PHP 5.3. Thanks!

---

1. submit the form
2. query the db to see if any of those fields values are in use
3. ???
4. profit

---

If you specify any keys that cannot have duplicates when you create the table (or afterwards with the alter table command) as unique using unique key constraints, the insert will fail. This will cause mysql_query() to return false, and you can get the error message from the previous mysql operation using myqsl_error(). Something like this:

if(mysql_query("INSERT INTO tbl VALUES ('1','2','3'))
{
    //success code
}
else
{
    $error_string = mysql_error();
    //handle error
}

I'm not sure that the error message will give you exactly what you are looking for, but at least it keeps your db overhead down.

---

The MySQL error will return only one of the keys that was violidated. As the different column values can be found in different rows, there is no easy way to get all of them. Using query like SELECT * FROM table WHERE column1=val1 OR column2=val2 won't work, since it can use any indexes. Making few separate queries to check for each of the field is better approach. If this is InnoDB and you are quering only for the column (SELECT column FROM table WHERE column=val) this would be index-covered query, which is extremly fast, even if you have millions of users (and I assume we are talking about few unique fields)

---

You can loop over $_POST or $_GET, depending on whatever form method you are using. Consider this example:

$input = array();

foreach($_POST as $key => $val) {
  $input["$key"] = trim($val);
}

Now we have a sanitized version of input, trimming out leading and trailing spaces.

You get which fields were duplicated by looping over values:

foreach($input as $key => $val) {
  $keys = array_keys($input, $val);
  if(count($keys) > 1) {
    /* More code here */
  }
}