I am having two different tables users and invoice_order
users table -
+-------+-------+-----------+
| pid | fname | iid |
+=======+=======+===========+
| sk001 | aa | null |
+-------+-------+-----------+
| sk002 | ss | ssk001 |
+-------+-------+-----------+
| sk003 | dd | sk001 |
+-------+-------+-----------+
| sk004 | ff | sk002 |
+-------+-------+-----------+
| sk005 | gg | sk002 |
+-------+-------+-----------+
| sk006 | hh | sk005 |
+-------+-------+-----------+
| sk007 | jj | sk006 |
+-------+-------+-----------+
| sk008 | kk | sk006 |
+-------+-------+-----------+
| sk009 | ll | sk004 |
+-------+-------+-----------+
| sk010 | mm | sk005 |
+-------+-------+-----------+
Here `pid` is personal id of the user and `iid` is the parent_id of the users
invoice_order table -
+-------+-------+-----------+--------+
| pid | fname | iid |order_to|
+=======+=======+===========+========+
| sk001 | aa | null | 100 |
+-------+-------+-----------+--------+
| sk002 | ss | ssk001 | 400 |
+-------+-------+-----------+--------+
| sk002 | dd | sk001 | 225 |
+-------+-------+-----------+--------+
| sk004 | ff | sk002 | 50 |
+-------+-------+-----------+--------+
| sk005 | gg | sk002 | 59 |
+-------+-------+-----------+--------+
| sk006 | hh | sk005 | 77 |
+-------+-------+-----------+--------+
| sk007 | jj | sk006 | 89 |
+-------+-------+-----------+--------+
| sk004 | ff | sk002 | 87 |
+-------+-------+-----------+--------+
| sk009 | ll | sk004 | 45 |
+-------+-------+-----------+--------+
| sk010 | mm | sk005 | 56 |
+-------+-------+-----------+--------+
And I am using a procedure team_total_test to calculate the team total of a individual user using a recursive query in the procedure
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `team_total_test`(IN `userid` VARCHAR(200))
NO SQL
BEGIN
CREATE TABLE tt_3 (pid VARCHAR(100),iid VARCHAR(100),total INT(250));
INSERT INTO tt_3 SELECT pid,
iid,0
from (SELECT pid,iid from users
order by pid, iid) users,
(SELECT @pv := userid) initialisation
where find_in_set(iid, @pv) > 0
and length(@pv := concat(@pv, ',', pid)) order by iid ASC;
UPDATE tt_3
SET total=(SELECT (SUM(order_total_before_tax))/2 FROM invoice_order WHERE tt_3.pid=invoice_order.pid
AND MONTH(order_date)=MONTH(NOW()) AND YEAR(order_date)=YEAR(NOW()) );
SELECT sum(total) as total_pv
from tt_3 ;
SELECT COUNT(pid) as counting FROM tt_3;
DROP TABLE tt_3 ;
END$$
DELIMITER ;
The Problem is I am getting correct output in localhost and not in server
output from localhost is
output in server is
