从存储过程循环MySQL结果

For the sake of simplicity here, I have the following SQL query:

为了简单起见,我有以下SQL查询:

"SELECT id,name FROM employees WHERE birthdate<'$dymd'"

If I want use records from standard (not stored procedure) query, the code looks like this:

如果我想使用标准(非存储过程)查询中的记录,代码如下所示:

$sql="SELECT id,name FROM employees WHERE birthdate<'$dymd'";

$rst=$mysqli->query($sql);

while($row=$rst->fetch_assoc())
{
  ...
}

$rst->free();

But if I store my query into stored procedure:

但是如果我将查询存储到存储过程中:

CREATE PROCEDURE GetEmployees(IN dymd DATE)
  READS SQL DATA
BEGIN
  SELECT id,name FROM employees WHERE birthdate<dymd;
END;

I cannot use previous loop (I get the error message: "Commands out of sync; you can't run this command now"). I have to loop like this:

我无法使用上一个循环(我收到错误消息:“命令不同步;您现在无法运行此命令”)。我必须像这样循环:

$sql="CALL GetEmployees('$dymd')";

$rst=$mysqli->query($sql);

if($rst->num_rows)
{
  do
  {
    $row=$rst->fetch_assoc();
    ...
  }
  while($mysqli->next_result());
  $rst->free();
}

The question is why I need to explicitly push records pointer in case of records from stored procedure!? Why fetch_assoc() does not do it for me like in previous case?

问题是为什么我需要在存储过程的记录的情况下显式推送记录指针!?为什么fetch_assoc()不像以前的情况那样为我做?

Even more, the code above works only for one retrieved result set from stored procedure per PHP page. If I have two stored procedures (e.g. CALL YoungEmployees() and CALL OldEmployees()) on the same PHP page for the second stored procedure I get the already mentioned error message. If I use standard queries (not from stored procedures) then it works fine - both result sets are correctly presented on one page!

更重要的是,上面的代码仅适用于每个PHP页面的存储过程中的一个检索结果集。如果我在第二个存储过程的同一个PHP页面上有两个存储过程(例如CALL YoungEmployees()和CALL OldEmployees()),我会得到已经提到的错误消息。如果我使用标准查询(不是来自存储过程),那么它工作正常 - 两个结果集都正确地显示在一个页面上!

1 个解决方案

#1

According to @JurisGregov comment, the solution for this is;

根据@JurisGregov评论,解决方案是:

//first stored procedure

$sql1="CALL YoungEmployees('$dymd')";

if($rst1=$mysqli->query($sql1))
{
  while($row1=$rst1->fetch_row())
  {
    ...
  }
  $rst1->close();
  $mysqli->next_result(); //!!!
}

//second stored procedure

$sql2="CALL OldEmployees('$dymd')";

if($rst2=$mysqli->query($sql2))
{
  while($row2=$rst2->fetch_row())
  {
    ...
  }
  $rst2->close();
  $mysqli->next_result(); //!!!
}