CODEWITHSUNDEEP
phpstored-proceduresmysqlisql-updatesqltransaction
Gautam Arya
Gautam Arya

Reputation: 723

mysqli stored procedure and update statement

I have problem in updating mysql table. While the problem seems somewhat strange I'm explaining it below.

I am working on user's profile update in which the data from single form is inserting/updating to two different tables but if i update the whole form information then updating data is successful but if i only update some 2 or 3 fields then updation is fail. I'm using mysql stored procedure for sql update the code is as under...

DELIMITER $$

DROP PROCEDURE IF EXISTS `usp_user_profile_save` $$
CREATE PROCEDURE `usp_user_profile_save`(IN sIntro_para VARCHAR(255), IN sBook VARCHAR(255), IN sProfileNewName VARCHAR(255),
IN iRel VARCHAR(255), IN iBdate VARCHAR(255), IN iSO INT, IN iMerital_status INT, IN iChildren INT, IN iProfession INT,
IN iIncome INT, IN iIncome_unit INT, IN iCountry INT, IN iState INT, IN iCity_or_post_code INT, IN iHeight VARCHAR(255), IN iSp INT, IN iEthnicity INT,
IN iHair_color INT, IN iHair_lenght INT, IN iEye_color INT, IN iSmoker_or_not INT, IN iUserId INT)
BEGIN
DECLARE n,n1,respCode INT;
DECLARE respMsg,dbg VARCHAR(255);

  START TRANSACTION;

  UPDATE `tbl_user` SET
    `introduction` = sIntro_para,
    `profile_picture` = sProfileNewName,
    `birthdate` = iBdate,
    `s_o` = iSO,
    `marital_status` = iMerital_status,
    `children` = iChildren,
    `profession` = iProfession,
    `income` = iIncome,
    `income_unit` = iIncome_unit,
    `my_book` = sBook,
    `r_s` = iRel,
    `counrty` = iCountry,
    `state` = iState,
    `city` = iCity_or_post_code,
    `modified` = NOW(),
    `modified_by` = iUserId
  WHERE `id` = iUserId;
  SET n = ROW_COUNT();

  IF n <= 0 THEN
    ROLLBACK;
  ELSE

    IF EXISTS (SELECT * FROM `tbl_user_physical` WHERE `tbl_user_id` = iUserId) THEN
      UPDATE `tbl_user_physical` SET
        `tbl_user_id` = iUserId,
        `height` = iHeight,
        `shape` = iSp,
        `ethnicity` = iEthnicity,
        `hair_color` = iHair_color,
        `hair_length` = iHair_lenght,
        `eye_color` = iEye_color,
        `smoker_or_non_smoker` = iSmoker_or_not
      WHERE `tbl_user_id` = iUserId;
      SET n1 = ROW_COUNT();

    ELSE
        INSERT INTO `tbl_user_physical`(`tbl_user_id`, `height`, `shape`, `ethnicity`, `hair_color`, `hair_length`, `eye_color`, `smoker_or_non_smoker`) VALUES (iUserId, iHeight, iSp, iEthnicity, iHair_color, iHair_lenght, iEye_color, iSmoker_or_not);
        SET n1 = LAST_INSERT_ID();
    END IF;

    IF n1 > 0 THEN
      COMMIT;
        SELECT 1 AS respCode, 'Registration successfull.' AS respMsg;
    ELSE
        ROLLBACK;
        SELECT 0 AS respCode,'Registration couldn\'t be completed.' AS respMsg, n, n1;
    END IF;
  END IF;

END $$

DELIMITER;

Though i have googled my question many time with different keywords but i dont find relevant questions as mine, I have written update statement correctly but its not updating because most of the new data which is going to be update is same as old and my senior said me that update only work if there is a new set of data is submitted and used..

so, please help me to solve this problem thanks in advance..

Upvotes: 1

Views: 599

Answers (1)

Gautam Arya
Gautam Arya

Reputation: 723

I lately come to know that UPDATE statement return error if we update same data. So, I've update added new column modified_date and updating this field with NOW() so that at least one column got changed and UPDATE statement returns no error.

Upvotes: 0

Related Questions