2. PHPRunner General questions
  3. Check for existing recordset avoiding duplicate entries
This topic is locked

Check for existing recordset avoiding duplicate entries

 		4/14/2007 12:22:17 PM
PHPRunner General questions
Z
 zebex author

Help me on this. Im lost.
I have a table named "classprog". On that table there are 3 fields namely, "time","subject","room".

I want to prevent duplicate entries on these fields. On the before record added event, below is the code:
I cant understand, where to insert the fields to the code below. If someone could point and guide me,

I would it appreciate be very much.
****

*
function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Check if specific record exists ****

global $conn;

$strSQLExists = "select * from classprog where AnyColumn='AnyValue'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

// if record exists do something

}

else

{

// if dont exist do something else

}
return true;
// return true if you like to proceed with adding new record

// return false in other case
}
J
 		Jane 4/16/2007

Hi,
entered values is stored in the $values array. Try to use following event code:

function BeforeAdd(&$values)

{

global $conn;

$strSQLExists = "select * from classprog where time='".$values["time"]."' and subject='".$values["subject"]."' and room='".$values["room"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

echo "Duplicate Entries, Try Again";

return false;

}

else

{

return true;

}

}
Z
 		zebex author 4/16/2007

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=16995&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' /> Thanks...worked flawlessly.
I have one more problem. From the code below, I inserted the "show a message on the webpage" action.

And if user, would enter a duplicate record the message "Duplicate Entries, Try Again." really do appear,

but it still proceed to add the record to the table. How can I prevent this? After showing the message, I want

the user be taken back to the data-entry page. thanks in advance.

Hi,

entered values is stored in the $values array. Try to use following event code:
L
 		larsonsc 4/16/2007

Try moving the final value for return outside of your loop. As you can see in one of my events below (this series manages adoptions of cats in the rescue group that I work with) in side each else or elseif, I set the return value, but then call the return value outside of the loop. Try doing something like that and see if that fixes your duplicate records issue. See the code below and feel free to contact me for further assistance if you need to.

function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//********** Check if specific record exists ************
// This is for speeding up data entry only

$_SESSION["Adoption_Date"] = $values["Adoption_Date"];
global $conn;

$strSQLExists = "select * from `inventory` where `Intake_ID` = '$values[Intake_ID]'";

$rsExists = db_query($strSQLExists,$conn);

$data = db_fetch_array($rsExists);
// Automatically marks a cat as adopted when the adoption record is entered.

if((($data['Intake_ID']) == ($values['Intake_ID']))

&& ($data['Date_Of_Birth'] < $values['Adoption_Date'])

&& (date("Y-m-d", strtotime($values['Adoption_Date'])) <= date("Y-m-d"))) {

$sqlupdate = "UPDATE `inventory` SET `Status` = 'Adopted' WHERE `inventory`.`Intake_ID` = '$values[Intake_ID]'";

db_exec($sqlupdate,$conn);

$return_value = true;

}
// Checks to make sure that the date of adoption is greater than the cat's date of birth

else if($data['Date_Of_Birth'] > $values['Adoption_Date']) {

echo "<font color=red>The Adoption Date you entered is before this cat was born. I demand an explanation, if we can adopt

them out before they are born, this is a system we must patent! Please verify and resubmit your record.</font>";

$return_value = false;

}
// Checks to make sure that the date of adoption is not in the future

else if((date("Y-m-d", strtotime($values['Adoption_Date']))) > ((date("Y-m-d")))) {

echo "<font color=red>The Adoption Date you entered is in the future. Do you promise this one will get adopted again on t

he date you have chosen?? Please verify and resubmit your record.</font>";

$return_value = false;

}
// Verifies that the adoption record being entered is for a cat that has actually been entered into the system

else {

echo "<font color=red>The Intake ID you entered does not exist in the records system. Why don't we just worry about getting home

s for the cats we have OK? Please verify and resubmit your record.</font><br />";

$return_value = false;

}
// All other conditions match this and get recorded

return $return_value;
// return true if you would like to proceed with adding new record

// return false to refuse the record
}
J
 		Jane 4/17/2007

Hi,
to redirect to the List page use Redirect to another paga action on the Events tab:

function BeforeAdd(&$values)

{

global $conn;

$strSQLExists = "select * from classprog where time='".$values["time"]."' and subject='".$values["subject"]."' and room='".$values["room"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

echo "<script>alert('Duplicate Entries, Try Again')</script>";

header("Location: classprog_list.php");

exit();

return false;

}

else

{

return true;

}

}