How to get applied stored procedure for inserted row in mysql php query

Question

$query = "CREATE PROCEDURE IF NOT EXISTS Insertion(IN firstname varchar(40),IN lastname varchar(40),IN email varchar(40),IN department varchar(40),IN doj date,IN basicpay int(11))
BEGIN

  DECLARE HRA decimal(20,2);
  DECLARE DA decimal(20,2);
  DECLARE PF decimal(20,2);
  DECLARE NET_SALARY decimal(20,2);

  IF department = 'HUMAN RESOURCE' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;

  ELSEIF department = 'MARKETING' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;

  ELSEIF department = 'PRODUCTION' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;

  ELSEIF department = 'FINANCE AND ACCOUNTING' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;


  ELSE 
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;


  END IF; 
SET NET_SALARY = basicpay+HRA + DA + PF;

  insert into employees(FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT,DATE_OF_JOINING,BASIC_PAY,HRA,DA,PF,NET_SALARY)
  values(firstname,lastname,email,department,doj,basicpay,HRA,DA,PF,NET_SALARY);

END";

Answer 1

CREATE PROCEDURE has no IF NOT EXISTS.

You have to drop it and create it a new

Also php and mysqli don't add DELIMITER to the CREATe PROCEDURE, so you have to do it yourself.

So the code must loook like

DROP PROCEDURE IF EXISTS Insertion;
DELIMITER $$
CREATE PROCEDURE Insertion(IN firstname varchar(40),IN lastname varchar(40),IN email varchar(40),IN department varchar(40),IN doj date,IN basicpay int(11))
BEGIN
DECLARE HRA decimal(20,2);
  DECLARE DA decimal(20,2);
  DECLARE PF decimal(20,2);
  DECLARE NET_SALARY decimal(20,2);
IF department = 'HUMAN RESOURCE' THEN
  SET HRA = (5/100)basicpay;
  SET DA = (7/100)basicpay;
  SET PF = (10/100)*basicpay;
ELSEIF department = 'MARKETING' THEN
  SET HRA = (5/100)basicpay;
  SET DA = (7/100)basicpay;
  SET PF = (10/100)*basicpay;
ELSEIF department = 'PRODUCTION' THEN
  SET HRA = (5/100)basicpay;
  SET DA = (7/100)basicpay;
  SET PF = (10/100)*basicpay;
ELSEIF department = 'FINANCE AND ACCOUNTING' THEN
  SET HRA = (5/100)basicpay;
  SET DA = (7/100)basicpay;
  SET PF = (10/100)*basicpay;
ELSE 
  SET HRA = (5/100)basicpay;
  SET DA = (7/100)basicpay;
  SET PF = (10/100)*basicpay;
END IF; 
SET NET_SALARY = basicpay+HRA + DA + PF;
insert into employees(FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT,DATE_OF_JOINING,BASIC_PAY,HRA,DA,PF,NET_SALARY)
  values(firstname,lastname,email,department,doj,basicpay,HRA,DA,PF,NET_SALARY);

END$$
DELIMITER ;