Error checking duplicate record input

Mr_Mako

Hi...

I've been working with this code for the last few days. The code does what I need... namely adding a record to a table, but I also need to error check for duplicate records so if someone tries to add a duplicate to the table it gives a message stating that, rather than a mysql error.

I've been trying to come up with an error checking and have had no success at all with it. My code for the record insertion is below... any help would be greatly appreciated.

<?

// Create variables //
$add_record = $_POST['username'];
$user = "USERNAME";
$password = "PASSWORD";
$database = "DATABASE";
$table = "TABLE";

if ($add_record == "") {
	// header("Location: index.php");
	echo "<p align=left>No Data in delete_record string.</p>";
	exit;
} else {
	// Open connection to database //
	$connection = mysql_connect('localhost', $user, $password) or die(mysql_error());
	$sql = mysql_select_db($database, $connection) or die(mysql_error());

// Set up query statement //
$add_query = "INSERT INTO $table (pilot) VALUES ('$_POST[username]')";

// Run query and get affected rows //
$addresult = mysql_query($add_query, $connection) or die(mysql_error());

// Run completed statement //
$addpilot_msg = "
	<p align=center>&nbsp;</p>
	<p align=center><font size=4><b>Pilot added Successfully!</b></font></p>
	<p align=center>&nbsp;</p>
	<p align=center>&nbsp;</p>
	<p align=center><b>Click below to return to Administration page.</b></p>
	<p align=center>&nbsp;</p>
	<p align=center>&nbsp;</p>
	<p align=center><b><a href=index.php>BACK TO STAB INDEX</a></b></p>
	<p align=center>&nbsp;</p>";
}

?>

trooper

OK you'll need to do the check b4 you actually perform the insert


$check = "SELECT COUNT(pilot) FROM $table WHERE pilot='$_POST[username]')";
$check_result = mysql_query($check);

if(mysql_num_rows($check_result)>0))
{
    die("That name is already taken - choose another");
}

$add_query = "INSERT INTO $table (pilot) VALUES ('$_POST[username]')";

Then if the name already exists it'll halt with the message that you put there

HTH

ahundiak

Create a unique index on the pilot column.

CREATE UNIQUE INDEX index_pilot ON table(pilot);

With the index, any attempt to insert a record with a duplicate pilot will fail. So just insert then check the return code to see if it worked.

No need for an explicit check.

trooper

The post above is abslutely correct ... however the request was for more graceful error handling hence why i suggested a check before doing the insert

asfaw

I am trying to check for an existing record before inserting a new record - MySQL would not insert the reqcord because the unique key will be duplicated.

What I want to do is notify the user.

$check = "SELECT COUNT(pilot) FROM $table WHERE pilot='$_POST[username]')";

Is pilot a field name or what is it.

Thank you.

Asfaw.

Roger_Ramjet

Originally posted by trooper
The post above is abslutely correct ... however the request was for more graceful error handling hence why i suggested a check before doing the insert

GM

I don't see what is more gracefull. If he just runs the insert he can still use your die message instead of the query error.

// Run query and get affected rows // 
    $addresult = mysql_query($add_query, $connection) or die("That name is already taken - choose another");