Hello,Hope all are fine. I need to execute the below mysql code after data insert of table1. So, I put the below codes on Table Specific → Row_Updated :
if ($this->submission_status->CurrentValue == "Submitted") {
//Update Chemical Requisition Details data based on Total Liquor
$find_total_chemical = "
-- Initialize a variable to store the affected rows count
SET @affected_rows = 0;
-- Update the rows and store the affected rows count
UPDATE chemical_requisition_details
INNER JOIN chemical_requisition_master ON
chemical_requisition_master.production_id = chemical_requisition_details.production_id
AND chemical_requisition_master.grd_batch_no = chemical_requisition_details.grd_batch_no
AND chemical_requisition_master.labdip_no = '" . $this->labdip_no->CurrentValue . "'
SET chemical_requisition_details.total_chem_quantity =
(chemical_requisition_details.chem_quantity * chemical_requisition_master.total_liquor) / 1000
WHERE
chemical_requisition_master.production_id = '" . $this->production_id->CurrentValue . "'
AND chemical_requisition_master.grd_batch_no = '" . $this->grd_batch_no->CurrentValue . "'
AND chemical_requisition_details.process_type = 'Pre-Treatment';
-- Get the affected rows count into the variable
SELECT ROW_COUNT() INTO @affected_rows;
-- Display a message to the user based on the affected rows count
IF (@affected_rows > 0) THEN
SIGNAL SQLSTATE '50001'
SET MESSAGE_TEXT = 'Update successful. Pre-Treatment Dye/Chemical quantity update successfull based on your inputed Total Liquor. Rows affected: ', @affected_rows;
ELSE
SET MESSAGE_TEXT = 'No rows were updated.';
END IF;";
$crow = ExecuteQuery($find_total_chemical);
After successfully add data the above mysql query is executing properly. But the issue is user are not showing message. The below part.
-- Get the affected rows count into the variable
SELECT ROW_COUNT() INTO @affected_rows;
-- Display a message to the user based on the affected rows count
IF (@affected_rows > 0) THEN
SIGNAL SQLSTATE '50001'
SET MESSAGE_TEXT = 'Update successful. Pre-Treatment Dye/Chemical quantity update successfull based on your inputed Total Liquor. Rows affected: ', @affected_rows;
ELSE
SET MESSAGE_TEXT = 'No rows were updated.';
END IF;";
Can experts suggest me how to formulate this part for showing success or failure message to user with how many rows effected?