How to Parse PHP Searilize data in Mysql Stored Procedure & Looping it

Question

I have below data in Mysql Column (storing all data in serialize form - with comma separated into column) and i want to get/fetch this column data in Mysql stored procedure and want to loop for each data and insert into another trans table.

So if my data like below then i want to insert 7 record in trans table.

{"FormBuilderId":"5","vAnswer":"Develeop"},
{"FormBuilderId":"15","vAnswer":"Search Engine"},
{"FormBuilderId":"13","vAnswer":"10-15"},
{"FormBuilderId":"6","vAnswer":"Tester entered"},
{"FormBuilderId":"1","vAnswer":"Female"},
{"FormBuilderId":"14","vAnswer":"Moon.jpg"},
{"FormBuilderId":"12","vAnswer":"TV,dancing and modeling"}

My table structure & data is like below in table:

CREATE TABLE IF NOT EXISTS `idea_history` (
    `iIdeaHistoryId` int(11) NOT NULL AUTO_INCREMENT,
    `iApplicationId` tinyint(11) unsigned DEFAULT '0',
    `tAnswerData` text COMMENT 'all answer data store here in json format',
    `dAddedDate` datetime NOT NULL,
    PRIMARY KEY (`iIdeaHistoryId`),
    KEY `iApplicationId` (`iApplicationId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='history comes here' AUTO_INCREMENT=57 ;

--
-- Dumping data for table `idea_history`
--

INSERT INTO idea_history (
  iIdeaHistoryId,
  iApplicationId,
  tAnswerData,
  dAddedDate
) 
VALUES
  (
    53,
    2,
    '{"FormBuilderId" : "2","vAnswer":"Environmental Group"},{"FormBuilderId" : "11","vAnswer":"Satelite"},{"FormBuilderId" : "3","vAnswer":"HB"},{"FormBuilderId" : "4","vAnswer":"Dev"},{"FormBuilderId" : "7","vAnswer":"HB"},{"FormBuilderId" : "8","vAnswer":"Balaji Satellite"},{"FormBuilderId" : "10","vAnswer":""}',
    '2014-07-05 19:20:56'
  ),
  (
    54,
    1,
    '{"FormBuilderId":"5","vAnswer":"Hello krishna|kanth double"},{"FormBuilderId":"15","vAnswer":"Website"},{"FormBuilderId":"6","vAnswer":"need to check"},{"FormBuilderId":"13","vAnswer":"20-25"}',
    '2014-07-05 19:20:56'
  ),
  (
    55,
    2,
    '{"FormBuilderId":"11","vAnswer":"comapnay"},{"FormBuilderId":"8","vAnswer":"here am|chw "},{"FormBuilderId" : "10","vAnswer":""},{"FormBuilderId":"9","vAnswer":"Business"}',
    '2014-07-05 19:20:56'
  ) ;

I will pass iIdeaHistoryId in stored procedure and it will fetch value of tAnswerData field and part this value and insert into another trans table.

Could you please guide what i have to change in stored procedure ?

Answer 1

you can do it like this

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'{"',-1),'":"',1) INTO arg_1;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'":"',-2),'","',1) INTO arg_2;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'","',-1),'":"',1) INTO arg_3;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'":"',-1),'"}',1) INTO arg_4;

INSERT INTO trans VALUES (arg_1,arg_2,arg_3,arg_4); 

or if you want to do it in store procedure add your data here

    DELIMITER $$
    DROP PROCEDURE IF EXISTS trans$$
    CREATE PROCEDURE trans()
    BEGIN
    DECLARE arg_1,arg_2,arg_3,arg_4 VARCHAR(100);
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE cursor_name CURSOR FOR YOUR_SELECT_statement;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN cursor_name;
      my_loop: LOOP
        FETCH cursor_name INTO your_variable;
        IF finished = 1 THEN 
          LEAVE my_loop;
        END IF;
          SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'{"',-1),'":"',1) 
          INTO arg_1 FROM your_table;
          SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'":"',-2),'","',1) 
          INTO arg_2 FROM your_table;
          SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'","',-1),'":"',1) 
          INTO arg_3 FROM your_table;
          SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(your_variable,'":"',-1),'"}',1) 
          INTO arg_4 FROM your_table;
          INSERT INTO trans VALUES (arg_1,arg_2,arg_3,arg_4); 
    END LOOP my_loop;
    CLOSE cursor_name;

    END$$

    DELIMITER ;

for one column you use this

DELIMITER $$
    DROP PROCEDURE IF EXISTS trans$$
    CREATE PROCEDURE trans(IN in_iIdeaHistoryId INT)
BEGIN
    DECLARE arg_1 VARCHAR(100);
      SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(`tAnswerData`,' ',''),'{"',-1),'":"',1) 
          INTO arg_1 FROM `idea_history` WHERE iIdeaHistoryId=in_iIdeaHistoryId;
          INSERT INTO trans VALUES (arg_1); 
    END$$
DELIMITER ;

and call it like this

CALL `trans`(53);