如何更新存储过程中有多列的两个表？

Question

As Below code PersonID is primary key in one table and foreign key in another table i have written this code in php

$query = mysqli_query($conn,"UPDATE employee SET Firstname = '$fname', Lastname ='$lname', email_id ='$email', Mobile_no ='$mobile', city='$city'  WHERE PersonID = '$userid'");
$query = mysqli_query($conn,"UPDATE experience SET Company_name = '$com', Location ='$local', Year_Of_Experience ='$year', Description ='$description'  WHERE PersonID = '$userid'");

Now i want to write above code in sp any one have any idea how to write?

Answer 1

Just run this below in your PHPMyAdmin and use it

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE DATABASE_NAME.`update_sp`(IN PersonID DATATYPE(size),IN Firstname DATATYPE(size), IN Lastname DATATYPE(size), IN email_id DATATYPE(size), IN Mobile_no DATATYPE(size), IN city DATATYPE(size),
                            IN Location  DATATYPE(size), IN Year_Of_Experience DATATYPE(size), IN Description DATATYPE(size))
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
        UPDATE employee SET Firstname = REPLACE(Firstname,"'","`"), Lastname =REPLACE(Lastname,"'","`"), email_id =REPLACE(email_id,"'","`"), 
        Mobile_no =REPLACE(Mobile_no,"'","`"), city=REPLACE(city,"'","`")  WHERE PersonID = REPLACE(PersonID,"'","`");

        UPDATE experience SET Company_name = REPLACE(Company_name,"'","`") , Location =REPLACE(Location,"'","`") , Year_Of_Experience =REPLACE(Year_Of_Experience,"'","`") ,
         Description =REPLACE(Description,"'","`")   WHERE PersonID = REPLACE(PersonID,"'","`") ;
    END$$

DELIMITER ;

You can call it as like a function call by passing the arguments ..