If you are using ON DELETE RESTRICT in the database to prevent deletion of records that have details, then this function helps to display a normal message and prevent deletion of records.
This function only works with MySQL databases.
If someone from Xlinesoft can confirm that this approach is ok and will work in future versions of PHPRunner?
Here are the steps:
1.
In Output, edit or add a new connection in which you will save data about the connection to the database in the $_SESSION variable:
// store is sessions so we can use them in custom functions
$_SESSION['db_host'] = $host;
$_SESSION['db_user'] = $user;
$_SESSION['db_pwd'] = $pwd;
$_SESSION['db_port'] = $port;
$_SESSION['db_sys_dbname'] = $sys_dbname;
2.
In Events After Application Initilized event add this line of code:
include("custom_php/referential_integrity_checks.php");
3.
In every table (where you want to use this code) List page events > Before record deleted, add this code (and replace "my_table" and "table_primary_key_fileld" with your values)
$returned = can_delete_record("my_table", $deleted_values["table_primary_key_fileld"], $returned_error_msg);
$message= $returned_error_msg;
return $returned;
4.
Goto Style
Click on "Custom files"
Add a new folder and name it "custom_php"
Click on this "custom_php" and add a new file and call it "referential_integrity_checks.php"
Open the file "referential_integrity_checks.php" and paste this code into it:
<?php
// This is a function that will check the referential integrity before deleting a record
// It can be used only in MySQL
// It is not tested with more then 1 database
function can_delete_record($table, $id, &$returned_error_msg) {
// Define the strings that will be displayed to the user (so that we can use translations in multi-language projects)
// $connection_failed = "Connection failed: ";
$connection_failed = GetCustomLabel("CONNECTION_FAILED");
// $error_querying_information_schema = "Error querying the information schema: ";
$error_querying_information_schema = GetCustomLabel("ERROR_QUERYING_INFORMATION_SCHEMA");
// $error_restrict = "Error: The record cannot be deleted because it has related records in the following tables: \n";
$error_restrict = GetCustomLabel("ERROR_RESTRICT_CANNOT_BE_DELETED_RELATED_RECORDS");
// $error_querying_referenced_table = "Error querying the referenced table: ";
$error_querying_referenced_table = GetCustomLabel("ERROR_QUERYING_REFERENCED_TABLE");
// $error_records = "records";
$error_records = GetCustomLabel("ERROR_RECORDS");
// $error_first_delete_records = "You need to delete these records first before deleting the record from %s with id %s. ";
$error_first_delete_records = GetCustomLabel("ERROR_FIRST_DELETE_RECORDS");
$servername = $_SESSION['db_host'];
$username = $_SESSION['db_user'];
$password = $_SESSION['db_pwd'];
$dbname = $_SESSION['db_sys_dbname'];
$port = isset($_SESSION['db_port']) ? intval($_SESSION['db_port']) : null;
$returned_error_msg = "";
// Connect to the database
$conn = new mysqli($servername, $username, $password, $dbname, $port);
// Check connection
if ($conn->connect_error) {
die($connection_failed . $conn->connect_error);
}
// Query the information schema to get the on delete actions of all the foreign key constraints
$sql = "SELECT t.table_schema, t.table_name, t.delete_rule, t.column_name, t.referenced_table_name, t.referenced_column_name, t.column_type FROM (
SELECT tc.table_schema, tc.table_name, tc.constraint_name, kcu.column_name, rc.update_rule, rc.delete_rule, kcu.referenced_table_schema, kcu.referenced_table_name, kcu.referenced_column_name, c.column_type
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.referential_constraints AS rc
ON tc.constraint_name = rc.constraint_name
AND tc.table_schema = rc.constraint_schema
JOIN information_schema.columns AS c
ON kcu.referenced_table_schema = c.table_schema
AND kcu.referenced_table_name = c.table_name
AND kcu.referenced_column_name = c.column_name
WHERE tc.constraint_type = 'FOREIGN KEY' AND rc.delete_rule = 'RESTRICT'
) AS t
WHERE t.table_schema = ? AND t.referenced_table_name = ?";
$stmt1 = $conn->prepare($sql); // Use a different variable for the outer query statement
$stmt1->bind_param("ss", $dbname, $table);
$stmt1->execute();
$result1 = $stmt1->get_result(); // Use a different variable for the outer query result
if ($result1->num_rows > 0) {
$can_delete = true;
$returned_error_msg = "";
// Create an array to store the referenced tables and counts
$ref_tables = array();
while ($row1 = $result1->fetch_assoc()) {
$ref_table_schema = $row1["table_schema"];
$ref_table = $row1["table_name"];
$delete_rule = $row1["delete_rule"];
$column_name = $row1["column_name"];
$ref_column_name = $row1["referenced_column_name"];
$column_type = explode("(", $row1["column_type"])[0];
// Use the column type to determine the type character for binding the parameter
switch ($column_type) {
case "int":
case "tinyint":
case "smallint":
case "mediumint":
case "bigint":
$type_char = "i";
break;
case "decimal":
case "numeric":
case "float":
case "double":
$type_char = "d";
break;
case "char":
case "varchar":
case "text":
case "blob":
case "date":
case "time":
case "datetime":
case "timestamp":
case "enum":
case "set":
$type_char = "s";
break;
default:
$type_char = "b";
break;
}
// Count the number of rows in the referenced table that match the id
$sql = "SELECT COUNT(*) AS count FROM $ref_table WHERE $ref_column_name = ?";
$stmt2 = $conn->prepare($sql);
$stmt2->bind_param($type_char, $id);
$stmt2->execute();
$result2 = $stmt2->get_result();
if ($result2->num_rows > 0) {
$row2 = $result2->fetch_assoc();
$count2 = $row2["count"];
if ($count2 > 0) {
// Add the referenced table and count to the array
$ref_tables[$ref_table] = $count2;
$can_delete = false;
}
} else {
$returned_error_msg .= $error_querying_referenced_table . $conn->error;
$can_delete = false;
}
}
if (!empty($ref_tables)) {
// Construct the error message for each referenced table and count
foreach ($ref_tables as $ref_table => $count2) {
$error_restrict .= sprintf("- %s (%s $error_records)\n", getTableCaption($ref_table), $count2);
}
// Add the final message to delete the records first
$error_restrict .= sprintf($error_first_delete_records, getTableCaption($table), $id);
// Add the error message to the returned error message
$returned_error_msg .= $error_restrict;
}
if ($can_delete) {
// If the flag is true
return true;
} else {
// If the flag is false
return false;
}
} else {
echo $error_querying_information_schema . $conn->error;
// Return false
return false;
}
// Close the connection
$conn->close();
}
?>
dageci authorDevClub member
