[FIXED] Phpmyadmin Function Delete row if exists in other table

Issue

Need help on creating MySQL Function in phpmyadmin So I have 2 tables:

-Customer_Table CustomerID|CustomerName

-Project_Table ProjectID|CustomerID

My aim is to create a function called delete_customer(parameter_CustomerID). It'll take the CustomerID passed in the input parameter, then it checks whether this Customer ID exists in Project Table. If it is, then

return 0

Otherwise,

DELETE FROM Customer_Table WHERE CustomerID=parameter_CustomerID

I've tried to search the tutorial on how to create routine-function in phpmyadmin especially with if-else function, but turned out no luck.

Thank you in advance.

Solution

Hello Please check following Mysql Function:

DROP Function IF EXISTS delete_customer;

Delimiter //
CREATE FUNCTION delete_customer(customer_id INT(11))

RETURNS INT(1) 

BEGIN

DECLARE NAME_FOUND INT DEFAULT 0;
DECLARE USER_ACCEPT INT DEFAULT 0;

SELECT COUNT(iCustomerId) INTO NAME_FOUND FROM Customer_Table WHERE iCustomerId = customer_id;
IF NAME_FOUND > 0 
    THEN
    DELETE FROM Customer_Table WHERE iCustomerId = customer_id;
    SET USER_ACCEPT = 1;

ELSE 
     SET USER_ACCEPT = 0;

    END IF;
 RETURN USER_ACCEPT;
END//

Delimiter ;

Answered By - Jyoti Sharma