I want to tell my users that they tried to add a duplicate entry to my database

[wright67uk]

I've set up my database with a Competition column as a primary key, to prevent duplicate entries.

 

How can I echo, a message to the user, informing them if they were unsuccessful in trying to add a duplicate entry?

 

Here is what I have already tried;

 

<?php


if(isset($_POST['processForm']))
{
$sql = "INSERT INTO Competitions (Competition) VALUES ('$_POST[compname]')";
mysql_query($sql);
if (!$sql) { echo "This Name is already taken"; } else { echo '<p>Your New Competition is now up and running <a href="comptable.php"><img src="refresh.png"/><a/></p><br/>'; }
};

?>

[Psycho]

You have two options that I know of:

 

1) First do a SELECT query to check for a duplicate value. If one exists provide an appropriate message and don't do the insert. The only problem with this method is "race condition" issues. It is *possible* that in the very brief amount of time that you check for a duplicate then insert the new record that someone else create a new record with that duplicate value. If that happens then the insert will fail. But, unless you have a site with a LOT of traffic this is a very remote possibility.

 

2) Do the insert - then when it fails check the error message. If the insert fails because of a duplicate value in a unique column the error will be something like "#1062 - Duplicate entry '55' for key 'PRIMARY' ". The problem with this is that there could potentially be multiple errors but only one would be returned. So, you may have to do additional logic to verify all error conditions.

Edited January 3, 2013 by Psycho

[trq]

In your example $sql will never be false. It the string containing your query.

 

You need to check the value returned from mysql_query.

[thara]

try something like this..

 

if (isset ( $_POST['processForm'])) {
$compname= $_POST['compname'];

// Make sure the Competition is available:
$q = "SELECT competition FROM Competitions WHERE competition ='$compname'";
$r = mysql_query ( $q );

if (mysql_num_rows($r) == 0) { // Available.

 // insert data into your database....

} else { // The Competition is not available.

 // Print your error msg...
 echo '<p>That competition has already been registered.</p>';
}
}

Edited January 3, 2013 by thara

[PFMaBiSmAd]

@thara,

 

Since your code example doesn't check if the SELECT query worked or failed before checking how many rows it matched, it will also run the // insert data into your database.... code when the select query fails due to an error. You must always test for errors before using data that a query returns to prevent follow-on errors and unexpected operation of code.

[thara]

@PFMaBiSmAd you mean this?

 

$result = mysql_query($query) or die ("Error in query: $query " . mysql_error());

 

if so.. This is my answer again..

 

if (isset ( $_POST['processForm'])) {
  $compname= $_POST['compname'];

  // Make sure the Competition is available:
  $q = "SELECT competition FROM Competitions WHERE competition ='$compname'";
  $r = mysql_query ( $q )or die ("Error in query: $q" . mysql_error());

  if (mysql_num_rows($r) == 0) { // Available.

        // insert data into your database....

  } else { // The Competition is not available.

        // Print your error msg...
        echo '<p>That competition has already been registered.</p>';
  }
}