MySQL Call Procedure in Function ERROR

Question

I am trying to get the count from a procedure result. I have seen that you can create a temporary table and count it later. But when I add my procedure to the function, it stops working. Why is that?

  $connection->query('
   drop procedure if exists parents;
   create procedure parents(in parent int(11),in name varchar(22))
   begin
    set @parent=parent;
    drop temporary table if exists ids;
    create temporary table ids(id int(11));
    while @parent<>0 do
     prepare statement from concat("select related into @parent from ",name," where id=?");
     execute statement using @parent;
     insert into ids(id) values(@parent);
    end while;
    select*from ids;
   end;
   drop function if exists counted;
   create function counted(parent int(11),name varchar(22))
   returns int(11)
   begin
    call parents(parent,name);
    return (select count(*) from ids);
   end;
   ');
   $fetch=$connection->query('select counted(3,"category");')->fetchall();

I get a fetch boolean error.

You can't execute multiple statements in a single call to query() in mysqli. — Barmar
– Barmar, Commented May 1, 2018 at 21:37

Barmar · Accepted Answer · 2018-05-01 21:42:08Z

1

You can't execute multiple queries in a single call to query() (you can do it with multi_query(), but you can't customize the query delimiter to distinguish between the ; that separates queries and the ones used inside the procedure). So split this into multiple queries.

  $connection->query('xdrop procedure if exists parents');
  $connection->query('
   create procedure parents(in parent int(11),in name varchar(22))
   begin
    set @parent=parent;
    drop temporary table if exists ids;
    create temporary table ids(id int(11));
    while @parent<>0 do
     prepare statement from concat("select related into @parent from ",name," where id=?");
     execute statement using @parent;
     insert into ids(id) values(@parent);
    end while;
    select*from ids;
   end;
  ');
  $connection->query('drop function if exists counted');
  $connection->query('
   create function counted(parent int(11),name varchar(22))
   returns int(11)
   begin
    call parents(parent,name);
    return (select count(*) from ids);
   end;
   ');
   $fetch=$connection->query('select counted(3,"category");')->fetchall();

answered May 1, 2018 at 21:42

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

2 Comments

Maciek Over a year ago

But mine executes all of them.

Barmar Over a year ago

What is the error message? When query() returns a boolean false, you should print $connection->error.

Collectives™ on Stack Overflow

MySQL Call Procedure in Function ERROR

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related