PHP - Stored Procedure example requested
Posted: Sat Jan 31, 2009 7:08 am
I am a COMPLETE NOVICE to PHP
I have two tables on my database - Create and inserts follow:
categorytype
physicalcontainers
I have a stored procedure that I want to call to "construct" a value that before inserting a new row into the pyhsicalcontainers table
The stored procedure definition called GenLabel is also shown below
I'm looking for example PHP code to define the two input variables and one output variable as well as the sql command and how to handle the result.
-- --------------------------------------------------------
CREATE TABLE `containertype` (
`id` smallint(6) NOT NULL auto_increment,
`description` varchar(100) character set utf8 NOT NULL,
`abbrev` varchar(5) character set utf8 NOT NULL,
`ownerid` varchar(10) character set utf8 NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `IXContDesc` (`description`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;
INSERT INTO `containertype` VALUES (1, '-', '-', 'jkpalmer');
INSERT INTO `containertype` VALUES (2, 'Manilla Folder', 'MF', 'jkpalmer');
-- --------------------------------------------------------
CREATE TABLE `physicalcontainers` (
`id` smallint(6) NOT NULL auto_increment,
`containerId` smallint(6) NOT NULL,
`seq` smallint(6) NOT NULL default '0',
`label_note` varchar(100) character set utf8 NOT NULL,
`label_value` varchar(100) character set utf8 default NULL,
`ownerid` varchar(10) character set utf8 NOT NULL,
PRIMARY KEY (`id`),
KEY `owner_ndx` (`ownerid`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;
INSERT INTO `physicalcontainers` VALUES (11, 2, 2, 'Some note to add', 'MF-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (17, 4, 2, 'Some folder name', 'HF-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (21, 5, 2, 'Bankers box', 'BB-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (6, 6, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (7, 7, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (8, 8, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (9, 9, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (10, 2, 1, 'Personal Notes', 'MF-00001', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (12, 2, 3, 'Savings Books', 'MF-00003', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (13, 2, 4, 'Manila folder 4', 'MF-00004', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (16, 4, 1, 'Car Records', 'HF-00001', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (18, 4, 3, 'JKP Stuff', 'HF-00003', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (19, 2, 7, 'Electric Bills', 'MF-00007', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (20, 5, 1, 'Not labeled', 'BB-00001', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (2, 2, 3, 'Some note', 'MF-00003', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (4, 4, 2, 'A value', 'HF-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (43, 2, 8, 'Birth Certificates', 'MF-00008', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (44, 27, 1, 'some box', 'AB-00001', 'test1');
The code for the stored procedure is as follows:
CREATE PROCEDURE `genlabel`(IN V_ID SMALLINT(6), IN V_OWNERID CHAR(10), OUT V_RESULT CHAR(10))
BEGIN
DECLARE MAX_SEQ SMALLINT(6) default 123;
DECLARE S_ID SMALLINT(6);
DECLARE V_RESULT CHAR(10) default '';
DECLARE S_OWNERID CHAR(10);
DECLARE CONT_PFX VARCHAR(5);
DECLARE V_LABEL_SEQ CHAR(5);
DECLARE N_LABEL_SEQ CHAR(5);
DECLARE NO_SEQ INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_SEQ = 1;
SET S_ID = V_ID;
SET S_OWNERID = V_OWNERID;
SELECT MAX(SEQ) INTO MAX_SEQ FROM physicalcontainers
WHERE containerId = S_ID AND ownerid = S_OWNERID;
set max_seq = max_seq + 1;
set v_label_seq = cast(max_seq as char);
set n_label_seq = case length(v_label_seq)
when 1 then concat('0000',v_label_seq)
when 2 then concat('000',v_label_seq)
when 3 then concat('00',v_label_seq)
when 4 then concat('0',v_label_seq)
end;
IF NO_SEQ = 0 THEN SELECT abbrev INTO CONT_PFX FROM containertype WHERE id = S_ID;
else
if no_seq = 0 then
SET V_RESULT = CONCAT(CONT_PFX,'-',n_label_seq);
/* insert into physicalcontainers (containerid, seq, label_value, ownerid) values (V_ID, max_seq, V_RESULT, V_OWNERID); */
end if;
END IF;
commit;
END
I have two tables on my database - Create and inserts follow:
categorytype
physicalcontainers
I have a stored procedure that I want to call to "construct" a value that before inserting a new row into the pyhsicalcontainers table
The stored procedure definition called GenLabel is also shown below
I'm looking for example PHP code to define the two input variables and one output variable as well as the sql command and how to handle the result.
-- --------------------------------------------------------
CREATE TABLE `containertype` (
`id` smallint(6) NOT NULL auto_increment,
`description` varchar(100) character set utf8 NOT NULL,
`abbrev` varchar(5) character set utf8 NOT NULL,
`ownerid` varchar(10) character set utf8 NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `IXContDesc` (`description`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;
INSERT INTO `containertype` VALUES (1, '-', '-', 'jkpalmer');
INSERT INTO `containertype` VALUES (2, 'Manilla Folder', 'MF', 'jkpalmer');
-- --------------------------------------------------------
CREATE TABLE `physicalcontainers` (
`id` smallint(6) NOT NULL auto_increment,
`containerId` smallint(6) NOT NULL,
`seq` smallint(6) NOT NULL default '0',
`label_note` varchar(100) character set utf8 NOT NULL,
`label_value` varchar(100) character set utf8 default NULL,
`ownerid` varchar(10) character set utf8 NOT NULL,
PRIMARY KEY (`id`),
KEY `owner_ndx` (`ownerid`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;
INSERT INTO `physicalcontainers` VALUES (11, 2, 2, 'Some note to add', 'MF-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (17, 4, 2, 'Some folder name', 'HF-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (21, 5, 2, 'Bankers box', 'BB-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (6, 6, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (7, 7, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (8, 8, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (9, 9, 0, '', NULL, 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (10, 2, 1, 'Personal Notes', 'MF-00001', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (12, 2, 3, 'Savings Books', 'MF-00003', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (13, 2, 4, 'Manila folder 4', 'MF-00004', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (16, 4, 1, 'Car Records', 'HF-00001', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (18, 4, 3, 'JKP Stuff', 'HF-00003', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (19, 2, 7, 'Electric Bills', 'MF-00007', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (20, 5, 1, 'Not labeled', 'BB-00001', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (2, 2, 3, 'Some note', 'MF-00003', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (4, 4, 2, 'A value', 'HF-00002', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (43, 2, 8, 'Birth Certificates', 'MF-00008', 'jkpalmer');
INSERT INTO `physicalcontainers` VALUES (44, 27, 1, 'some box', 'AB-00001', 'test1');
The code for the stored procedure is as follows:
CREATE PROCEDURE `genlabel`(IN V_ID SMALLINT(6), IN V_OWNERID CHAR(10), OUT V_RESULT CHAR(10))
BEGIN
DECLARE MAX_SEQ SMALLINT(6) default 123;
DECLARE S_ID SMALLINT(6);
DECLARE V_RESULT CHAR(10) default '';
DECLARE S_OWNERID CHAR(10);
DECLARE CONT_PFX VARCHAR(5);
DECLARE V_LABEL_SEQ CHAR(5);
DECLARE N_LABEL_SEQ CHAR(5);
DECLARE NO_SEQ INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_SEQ = 1;
SET S_ID = V_ID;
SET S_OWNERID = V_OWNERID;
SELECT MAX(SEQ) INTO MAX_SEQ FROM physicalcontainers
WHERE containerId = S_ID AND ownerid = S_OWNERID;
set max_seq = max_seq + 1;
set v_label_seq = cast(max_seq as char);
set n_label_seq = case length(v_label_seq)
when 1 then concat('0000',v_label_seq)
when 2 then concat('000',v_label_seq)
when 3 then concat('00',v_label_seq)
when 4 then concat('0',v_label_seq)
end;
IF NO_SEQ = 0 THEN SELECT abbrev INTO CONT_PFX FROM containertype WHERE id = S_ID;
else
if no_seq = 0 then
SET V_RESULT = CONCAT(CONT_PFX,'-',n_label_seq);
/* insert into physicalcontainers (containerid, seq, label_value, ownerid) values (V_ID, max_seq, V_RESULT, V_OWNERID); */
end if;
END IF;
commit;
END