get an array output of a Stored Procedure in mysql

Question

I have created a stored procedure to count the number of works for each technician using the ID of that technicain and the intended year as a inputs to that stored procedure, but I have stucked to . . . How can I get an array of output in that procedure so that I can use it later in my code in php for example. Here is my stored procedure:

        DROP PROCEDURE `work_count`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `work_count`(IN `id` INT, IN `yearInput` INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN 
    SET @x := 1; 
    WHILE @x<13 DO 
       SELECT COUNT(work.workType) 
       FROM work 
       WHERE year(workDate)=yearInput 
          AND work.technicianID = id 
          AND month(workDate)= @x; 
       SET @x := @x+1; 
    END WHILE; 
 END

Uueerdo · Accepted Answer · 2018-11-06 20:47:17Z

2

If you're executing mulitple queries, you need to create a temporary table to hold your intermediate results, and then just make SELECT * FROM thattemporarytable one of the last things done in your procedure.

Something like this...

BEGIN
   DROP TEMPORARY TABLE IF EXISTS `blah` ( num INT );
   CREATE TEMPORARY TABLE `blah` ( num INT );
   SET @x := 1; 
   WHILE @x<13 DO 
      INSERT INTO blah (num)
      SELECT COUNT(work.workType) 
      FROM work 
      WHERE year(workDate)=yearInput 
            AND work.technicianID = id 
            AND month(workDate)= @x
      ; 
      SET @x := @x+1; 
   END WHILE; 
   SELECT * FROM blah;
   DROP TEMPORARY TABLE blah;
END

or, if you can manage it as a single query, a normal select should work:

BEGIN
   SELECT COUNT(work.workType) 
   FROM work 
   WHERE year(workDate)=yearInput 
         AND work.technicianID = id 
         AND month(workDate) BETWEEN 1 AND 12
   ;
END

edited Nov 6, 2018 at 20:47

answered Nov 6, 2018 at 20:41

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Osahady Over a year ago

The second thing is how to use that result SELECT* FROM tbl_someTable in a php can it be fetched like normal with while ($row = ...fetch_assoc()){}

Uueerdo Over a year ago

If you do not drop the temporary table at the end of the procedure, it can be used on the same database connection (that is the scope of temporary tables); but that should not be necessary. Executing the stored procedure (even from php) should already provide that result set; that is the purpose of that select * even being in the proc.

Osahady · Accepted Answer · 2018-11-07 17:57:24Z

0

I have used a temporary table to store data into it then publishing it using select statement and dropping the temp table at end of the procedure

BEGIN


  drop TABLE if exists `myDB`.`tbl_temp`;
  CREATE TABLE `myDB`.`tbl_temp` ( `countingWork` INT(11) NOT NULL ) ENGINE = InnoDB;   
  SET @x :=1;
  WHILE @x<=12 DO
      insert into `myDB`.`tbl_temp`
      SELECT COUNT(work.workType) 
      FROM work
      WHERE year(workDate)=yearInput 
            AND work.technicianID = id 
            AND month(workDate)= @x;
      SET @x = @x + 1;
  END WHILE;
  SELECT * from `myDB`.`tbl_temp`;
  drop TABLE `myDB`.`tbl_temp`;
END

edited Nov 7, 2018 at 17:57

answered Nov 6, 2018 at 20:37

Osahady

4498 silver badges16 bronze badges

Collectives™ on Stack Overflow

get an array output of a Stored Procedure in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related