Prevent duplicate entry

[chicago1985]

I have my PHP inserting into Oracle 9i.
But how do I prevent duplicate record entries?

I only have 3 fields in the insert in the action page and my below attempt doesnt output anything or insert anything even if not a duplicate:

<?php
$c=OCILogon("scott", "tiger", "orcl");
  if ( ! $c ) {
    echo "Unable to connect: " . var_dump( OCIError() );
    die();
  }
$mydupcheck = "select * from personTable where firstname = $_POST[firstname] and lastname = $_POST[lastname] and emailaddress = $_POST[emailaddress]";

if($mydupcheck)
{
   echo "Duplicate entry";
}
else
{
$s = OCIParse($c, "INSERT INTO personTable (firstname,lastname,emailaddress) VALUES ($_POST[firstname],'$_POST[lastname]','$_POST[emailaddress]'");
OCIExecute($s, OCI_DEFAULT);
echo "Record successfully entered";
}
?>

How do I prevent duplicate record info and show the user they entered duplicate info.

[hielo]

The best approach is to have the DB enforce Referenctial Integrity. Basically you instruct the database at design time NOT to allow duplicates for certain records ever. IF such an attempt is made, the db server will not carry out the operation. Look at these:

http://www.google.com/search?hl=en&client=firefox-a&rls=org.mozilla:en-US:official&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=Referential+Integrity+in+Oracle&spell=1

[hielo]

The "hard" (and wrong) approach would be to first query the database to check for the existence of the record and insert it only if no record exists. But the right way to approach this is through referential integrity.

[nav33n]

I have my PHP inserting into Oracle 9i.
But how do I prevent duplicate record entries?

I only have 3 fields in the insert in the action page and my below attempt doesnt output anything or insert anything even if not a duplicate:

There are some errors in your script. $mydupcheck = "select * from personTable where firstname = $_POST[firstname] and lastname = $_POST[lastname] and emailaddress = $_POST[emailaddress]"; Since firstname, lastname and emailaddress are strings, you should put them in quotes. And btw, you are not executing that query.

if($mydupcheck)
{
echo "Duplicate entry";
}

[hielo]

Try this:

<?php
$c=OCILogon("scott", "tiger", "orcl");
  if ( ! $c ) {
    echo "Unable to connect: " . var_dump( OCIError() );
    die();
  }

$sqlString = "select * from personTable where firstname='" . $_POST['firstname'] . "' and lastname='" . $_POST['lastname'] . "' and emailaddress='" . $_POST['emailaddress'] ."'");
$mydupcheck = OCIParse($c, $sqlString);

OCIExecute($mydupcheck);


$nrows = oci_fetch_all($mydupcheck, $results, 0, 1);


if($nrows)
{
   echo "Duplicate entry";
}
else
{
	$sqlString="INSERT INTO personTable (firstname,lastname,emailaddress) VALUES ('" . $_POST['firstname'] . "','" . $_POST['lastname'] . "','" . $_POST['emailaddress'] . "'";
	$s = OCIParse($c, $sqlString);
	OCIExecute($s, OCI_DEFAULT);
	echo "Record successfully entered";
}
?>