在while循环中仅第一次调用mysql PROCEDURE [英] Call mysql PROCEDURE inside while loop works only first time

问题描述

我从表上发布的数据库中获取了一些数据.对于表的每一行(while循环的每个循环)，我需要调用存储过程来计算表的最后一个<td>的数据.

I have some data from the DB I post on a table. For every line of the table (every loop of the while loop), I need to CALL a stored procedure to calculate the data for the last <td> of my table.

下面是一些代码:

$s = $lk->query("SELECT *
                  FROM A_USERS
                  JOIN A_DATA
                  WHERE A_DATA.id_user = A_USERS.id
                    AND A_USERS.usr_att = 1
                    AND A_DATA.act_data = 1
                    AND A_DATA.a_att = 1
                    AND A_DATA.qty IS NOT NULL
                  ORDER BY ID ASC");


while ($dato = $s->fetch_object()) {
    print '<tr>';

    print '<td>';   
    print $dato->id;
    print '</td>';

    //others cells


    $GetP = $lk->query("CALL GetPr($dato->qty, '$dato->prod')");

    if(mysqli_num_rows($GetP) === 1){
        while($p_db = mysqli_fetch_array($GetP)){
            $p = $p_db['p'];
        }

        $format = number_format(round($p, 1), 2, ',', '\'');

        //some other stuff here...

    }else{
        print '<td>';
        print 'ERROR';
        print '</td>';
    }
    print '</tr>';
}

问题在于，只有while的第一个循环才调用返回1 row的过程.否则，我总是在此<td>上出现ERROR，这意味着存储过程没有返回1 row.

The problem is that ONLY the first loop of the while calls the procedure which returns 1 row. Else I get always ERROR on this <td> which means the stored procedure didn't return 1 row.

为什么?

编辑

存储过程:

CREATE DEFINER=`...` PROCEDURE `GetPr`(IN `cli_qty` INT, IN `cli_prod` VARCHAR(10) CHARSET ascii)
BEGIN
    SET @cli_qty = cli_qty;

    SET @q = (SELECT qty FROM MRG_H ORDER BY ABS(@cli_qty - qty) ASC LIMIT 1);

    SET @cli_prod = cli_prod;

    IF @cli_prod = 'OECO' THEN SET @marg = (SELECT (m+v) FROM MRG_H JOIN DT WHERE m <> '' AND q = @q AND par = 'MarOECO');

    ELSE SET @marg = (SELECT m FROM MRG_H WHERE m <> '' AND q = @q);
    END IF;

    SELECT (Med+@marg)*((Val+100)/100) AS P, D_aaaammgg AS Date
        FROM PL
          JOIN TVA
        WHERE I_D = 0
      AND Med <> ''
      AND Date_f = ''
        ORDER BY Iden DESC
        LIMIT 1;
END

MYSQLI错误:

Error: 2014-Commands out of sync; you can't run this command now

推荐答案

在@Mihai的建议下，我找到了以下解决方案:

With suggestions of @Mihai I found this solution:

参考: PHP手册

while ($dato = $s->fetch_object()) {
    print '<tr>';

    print '<td>';   
    print $dato->id;
    print '</td>';

    //others cells


    $GetP = $lk->query("CALL GetPr($dato->qty, '$dato->prod')");

    if(mysqli_num_rows($GetP) === 1){
        while($p_db = mysqli_fetch_array($GetP)){
            $p = $p_db['p'];
        }

        $format = number_format(round($p, 1), 2, ',', '\'');

        //some other stuff here...

    }else{
        print '<td>';
        print 'ERROR';
        print '</td>';
    }

    //Added this 2 lines
    $GetPrice->close();
    $link->next_result();

    print '</tr>';
}

这篇关于在while循环中仅第一次调用mysql PROCEDURE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！