Allow only One Record to be Add based on Specified Data

i have master-child table with detail of:

• Master Table “project” with field “id” as primary key
• Child Table “project_sti” with field “id” as primary key, field “project_id” as a foreign key that references the primary key in the master table, field “sti_org_id” and field “role“.

So in PHPRUNNER, in page edit “project”, how to make if data in child table can only add 1 record only based on fields “project_id“, “sti_org_id” and “role“.

To ensure that only one record can be added to the project_sti table based on the combination of project_id, sti_org_id, and role in PHPRunner, you can use the BeforeAdd event to check for existing records before allowing a new one to be added. Here’s how you can do it:

1. Open PHPRunner and go to the Events section for the project_sti table.
2. Select the BeforeAdd event and add the following code:

global $conn, $message;

$project_id= $values["project_id"];
$sti_org_id= $values["sti_org_id"];
$role= $values["role"];

// SQL query to check if a record with the same project_id, sti_org_id, and role already exists
$sql = "SELECT COUNT(*) as count FROM project_sti WHERE project_id = '$project_id' AND sti_org_id = '$sti_org_id' AND role = '$role'";
$rs = db_query($sql, $conn);
$data = db_fetch_array($rs);

if ($data["count"] > 0) {
    // If a record exists, set the message and return false to prevent adding the new record
    $message = "A record with the same project_id, sti_org_id, and role already exists.";
    return false;
}

// If no record exists, allow the new record to be added
return true;