php - How can I execute multiple procedures continuously? -

here code:

$query = "call user_top_categories_score(?, 'all', 0, 1)"; $sth = $this->dbh->prepare($query); $sth->execute([$user_id]); $category = $sth->fetchall(pdo::fetch_assoc);  $query = "call user_top_tags_score(?, 'all', 0, 3)"; $sth = $this->dbh->prepare($query); $sth->execute([$user_id]); $tags = $sth->fetchall(pdo::fetch_assoc); 

it throws error:

fatal error: uncaught pdoexception: sqlstate[hy000]: general error: 2014 cannot execute queries while other unbuffered queries active. consider using pdostatement::fetchall(). alternatively, if code ever going run against mysql, may enable query buffering setting pdo::mysql_attr_use_buffered_query attribute. in c:\xampp\htdocs\myweb\others\user.php:71 stack trace: #0 c:\xampp\htdocs\myweb\others\user.php(71): pdo->prepare('call user_top_t...') #1 c:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 c:\xampp\htdocs\myweb\index.php(152): require_once('c:\xampp\htdocs...') #3 {main} thrown in c:\xampp\htdocs\myweb\others\user.php on line 71

also i've used closecursor() right after fetchall(), based on this solution. sadly throws new error:

warning: packets out of order. expected 1 received 9. packet size=7 in c:\xampp\htdocs\myweb\others\user.php on line 72

warning: pdo::prepare(): mysql server has gone away in c:\xampp\htdocs\myweb\others\user.php on line 72

fatal error: uncaught pdoexception: sqlstate[hy000]: general error: 2006 mysql server has gone away in c:\xampp\htdocs\myweb\others\user.php:72 stack trace: #0 c:\xampp\htdocs\myweb\others\user.php(72): pdo->prepare('call user_top_t...') #1 c:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 c:\xampp\htdocs\myweb\index.php(152): require_once('c:\xampp\htdocs...') #3 {main} thrown in c:\xampp\htdocs\myweb\others\user.php on line 72

any idea how can fix problem?

---

noted1: each of queries above work separately. mean, when call single procedure, works well.

noted2: each procedure returns result set. mean there select statement in procedures.

you may have 1 select in procedure, api doesn't know that. has assume might have multiple result sets returned procedure, therefore fetchall() alone doesn't close cursor.

you have keep calling nextrowset() until has returned false result, indicating results have been returned statement.

see answer how can use stored procedure in mysql database zend framework? (i answered in 2009!).