How to customize mysql error messages? (get key with duplicate entry)

[DavidT]

Hi everyone.

I have a form to insert data in a mysql database, with some fields that are supposed to be unique.

If there is a duplicate key I show user an error message, however I?d like to avoid show them the mysql error text itself, to not show informations about the database structure.

If I use errno, instead, of course I could write a custom message. However, is there a straightforward way which key has a duplicate entry, so that I can point it out to the user?

 

I could find it out in some way (using some queries to check or by splitting the error message), but it looks to me like a very clumsy way to do something so simple. Isn?t it?

 

Thanks in advance.

[Pikachu2000]

Yeah, displaying the actual error from the DB is probably not the best move. You could check mysql_errno, and make your custom error message conditional on its value. The values and meanings are in the MySQL manual.

[Psycho]

I don't think there is any way to determine which unique fields were possibly duplicated based on the error. I would just attempt the insert and trap the error if it occurs. Then if there is an error run a select query to get current records matching any of the unique values and loop through to determine which ones are duplicated.

[Pikachu2000]

Oh, missed the part about determining which field(s) is/are duplicated. Yeah, I'm pretty sure even if there were multiple UNIQUE conflicts, the full mysql_error() message would only list the first field that causes a UNIQUE key conflict.

[xyph]

Oh, missed the part about determining which field(s) is/are duplicated. Yeah, I'm pretty sure even if there were multiple UNIQUE conflicts, the full mysql_error() message would only list the first field that causes a UNIQUE key conflict.

 

Yeah, that's the way it behaves

[DavidT]

Thank you all!

 

It?s a good point that the error will show only the first field where a duplicate entry occurs. Given that, I suppose the best way is to add a code to my validation function that checks if the posted values are already in the database.

 

So, something like:

 

foreach ($unique_fields as $u) {
    $conditions[] = " `$u` = ".$PostedValue[$u];
}
//get db entries where some unique field has the same value as the one posted by the user
$strSQL = "SELECT * FROM `table_name` WHERE ".implode(' OR ',$conditions);  

And then it will be easy to see which fields has duplicate value.

However, is there any straightforward way to find out which unique fields the table has? Can I use "DESCRIBE"?

[DavidT]

Yep, DESCRIBE does work.

 

 

 

$invalid_fields = array();
$table = 'myTable';

		//Check for duplicate value in unique keys
		$res = mysql_query('DESCRIBE '.$table);
		$unique_fields = array();

		//Find which fields have to be unique
		while($row = mysql_fetch_assoc($res)) {
		    //Check if it is a unique field
		    if ($row['Key'] == 'UNI') $unique_fields[] = $row['Field'];
		}

		//Check data user posted for the unique fields
		foreach ($unique_fields as $u) {
			 $conditions[] = " `$u` = '".$FormData[$u]."'";
		}

		//get db entries where some unique field has the same value as the one posted by the user
		$strSQL = "SELECT * FROM `$table` WHERE ".implode(' OR ',$conditions);
		$res = mysql_query($strSQL);

		if ($res) while ($a = mysql_fetch_assoc($res)) {
			//Loop the fields to see if some has the same value
			foreach($unique_fields as $u) {
				if ($FormData[$u] == $a[$u]) {
					$invalid_fields[$u] = 'duplicate';
				}
			}
		} else { //query failed }

		if (empty($invalid_fields)) $valid = true;
		else $valid = false;

[Psycho]

Well, you *can* generate a single query to return a singe record with a value to determine if any duplicates exist in any of the columns you are interested in:

 

SELECT SUM(IF(field1='$value1', 1, 0)) as field1Dupe,
       SUM(IF(field2='$value2', 1, 0)) as field2Dupe,
       SUM(IF(field3='$value3', 1, 0)) as field3Dupe, 
FROM table_name

 

You will receive a single record with a 0 in each column that does not have a duplicate, else there will be a count of the number of duplicates in that column. Since you are not going to allow duplicates the values would only be a 0 or 1, so you can treat the returned values as Booleans.