2. PHPRunner General questions
  3. BeforeAdd Event Question
This topic is locked

BeforeAdd Event Question

 		12/8/2006 11:43:52 PM
PHPRunner General questions
L
 larsonsc author

I assume this question is generated by a lack of knowledge regarding PHP functions, so please bear with me.
I have been forced into running MyISAM as my storage engine due to a compatibility issue between my forum software and the InnoDB storage engine. This has left me with the inability to define Primary/Foreign key constraints in my database. What that has lead to is the ability for a user to insert data into a "dependant" table that does not have a logical parent record.
Is it possible for me to use the BeforeAdd Event to extract a single value from the record being added and look to see if it exists in the logical parent table? And further more, if the records does not exist, then not add the record and return an error to the user? If so, would someone be willing to work with me on how to code this function? This is a pretty important aspect of the records system I am building, and a few of these records being inserted without the proper parent record, could cause me some really big headaches not too far down the road.
Thanks for any help anyone can offer me.
--Shawn
Sergey Kornilov admin 12/9/2006

Shawn,
you can use BeforeAdd event and Check if record exists sample action. Something like this might work. Make sure you use correct table and field names.

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 ParentTable where ID=" . $values["ParentID"];**

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

$data=db_fetch_array($rsExists);

if($data)

{

// if record exists do something

return true;
}

else

{

// if dont exist do something else

return false;

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

// return false in other case
}
L
 		larsonsc author 12/9/2006

So all I need to do is edit the "ParentTable", "ID", and "ParentID" values to the proper table/field names? I do not need to code anything in the if/else section?
Could I add something in the "else" section that would return an error message on the screen informing the user that the "value of field blah does not exist, please verify and resubmit"?
Thanks for your help Sergey!
L
 		larsonsc author 12/10/2006

OK, I figured out how to prevent the record from being added using the BeforeRecordAdd event. However, I would like when the page is redrawn, for an error to print on the screen stating that the ID # was invalid, which I can do with an echo statement. But is there any way for me to control where it appears on the page in the event code? I would like to have the error message print out right about where it would normally say "<<<Record Added>>>" rather than above the header file.
Any suggestions on where to add my code for the echo statement? Thanks.
--Shawn
L
 		larsonsc author 12/11/2006

OK, I have another question related to the code for BeforeAdd functions. I am trying to do multiple verifications against data before a record gets added to make sure certain data is valid when the record is inserted. However, it seems that my general lack of knowledge in coding functions is causing everything to get inserted no matter what the data is so long as it is a valid type.
What I need to is this:

  1. Check to see if the Intake ID entered exists in the system. This I have working.
  2. Check to see that the Treatment Date entered is after the Date of Birth for the animal AND that the Treatment Date entered is not in the future. This I do NOT have working.



If both of the above criteria are valid, the I want the return to be true and the record to be inserted, if either are false, then of course I want the record to be refused. Below is the code I am using that makes sense to my inexperienced PHP coding brain. Could someone suggest how to change it to make it do what I need? Thanks to anyone who can help me out.
--Shawn

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 `inventory` where `Intake_ID`='$values[Intake_ID]'";

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

$data = db_fetch_array($rsExists);

if($data) {

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

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

$data2 = db_fetch_array($rsExists);

if($data2) {

($data2 < $values['Date_Of_Treatment']) && ($values['Date_Of_Treatment'] <= getdate());

$return_value = true;

}

else {

echo "<font color=red>The Treatment Date you entered is before this cat was born. Please verify and resubmit your record.</font>";

$return_value = false;

}

}

else {

echo "<font color=red>The Intake ID you entered does not exist in the records system. Please verify and resubmit your record.</font>";

$return_value = false;

}

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

// return false in other case
}
L
 		larsonsc author 12/11/2006

Nevermind...I stared at this a bit longer and realized the error of my ways. All I had to do to make it work was call the array value for Date_Of_Birth that I already had in $data and use the right date function. I was overcomplicating things as usual by calling a second query to get a values I already had.
L
 		larsonsc author 12/12/2006

I managed to get my data validity checks working and I thought I would share the solution here in case anyone else ever found a need to do the same kind of check. This code is a BeforeAdd function and performs the following checks:

  1. The ID# that was entered exists in the parent table
  2. The date of treatement is after the animal's date of birth
  3. The date of treatement is not in the future

This could of course be adapted to other checks, but if anyone needs to comapre dates for reasons like I listed above, or for any other reason, you could just modify the code to reflect your own comparisons. Hope this can help someone.
--Shawn

<?php
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 `inventory` where `Intake_ID`='$values[Intake_ID]'";

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

$data = db_fetch_array($rsExists);
/* If a record is returned, verify that the date of treatment is greater than the date of birth AND less than or equal to the current date. If yes return TRUE and exit, if FALSE move to next check */

if(($data['Date_Of_Birth'] < $values['Date_Of_Treatment']) && (date("z", strtotime($values['Date_Of_Treatment'])) <= date("z"))) {

$return_value = true;

}
// Check that date of brith is less than date of treatment

// If FALSE, return FALSE, refuse the record and echo error message

elseif($data['Date_Of_Birth'] > $values['Date_Of_Treatment']) {

echo "<font color=red>The Treatment Date you entered is before this cat was born. Talk about preventative medicine! Please verify and resubmit your record.</font>";

$return_value = false;

}
// Check that date of treatment is not in the future

// If FALSE, return FALSE, and echo error message

elseif((date("z", strtotime($values['Date_Of_Treatment']))) > ((date("z")))) {

echo "<font color=red>The Treatment Date you entered is in the future. Nice try oh psychic one! Please verify and resubmit your record.</font>";

$return_value = false;

}
// Echo error message for non-existant Intake ID

else {

echo "<font color=red>The Intake ID you entered does not exist in the records system. How about this time we try treating a cat we actually have OK? Please verify and resubmit your record.</font><br />";

$return_value = false;

}
// Insert or Refuse record based on $return_value

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

// return false to refuse the record
}
?>