顺序调用或多次调用时出现PHP存储过程错误

I have two menus in php that uses stored procedure. Let's name it Menu1 and Menu2. This code this for Menu1: This is also the code for Menu 2.

我在php中有两个使用存储过程的菜单。我们将它命名为Menu1和Menu2。这个代码用于Menu1:这也是菜单2的代码。

<?php

$sql=$mysqli->query("call selectproducts()");
$i=1;
while($row=mysqli_fetch_array($sql)){
$id=$row['prodid'];
$date=$row['prodname'];
$item=$row['proddescription'];
$qtyleft=$row['prodsupplier'];
$qty_sold=$row['proddate'];
$price=$row['prodprice'];
$sales=$row['prodquantity'];

if($i%2){
?>
<tr id="<?php echo $id; ?>" class="edit_tr">
<?php } else { ?>
<tr id="<?php echo $id; ?>" bgcolor="#f2f2f2" class="edit_tr">
<?php } ?>
<td class="edit_td">
<span class="text"><?php echo $date; ?></span> 
</td>
<td>
<span class="text"><?php echo $item; ?></span> 
</td>
<td>
<span class="text"><?php echo $qtyleft; ?></span>
</td>
<td>

<span id="last_<?php echo $id; ?>" class="text">
<?php

echo $qty_sold;
?>
</span> 
<input type="text" value="<?php echo $rtrt; ?>"  class="editbox" id="last_input_<?php   echo $id; ?>"/>
</td>
<td>
<span id="first_<?php echo $id; ?>" class="text"><?php echo $price; ?></span>
<input type="text" value="<?php echo $price; ?>" class="editbox" id="first_input_<?php echo $id; ?>" />
</td>
<td>

<span class="text"><?php echo $dailysales; ?>
<?php
echo $sales;
?>
</span> 
</td>
</tr>

<?php
$i++;
}
?>

My Problem is when i call stored procedure in Menu1 it works, but in Menu2 it has error.

我的问题是当我在Menu1中调用存储过程时它可以工作,但在Menu2中它有错误。

Based on my research, this code might has error because I am calling the stored procedure sequentially.

根据我的研究,此代码可能有错误,因为我按顺序调用存储过程。

How to modify this code to be able to call the stored procedure the second time around? I'm really confused with this one. It seems like I need to close the stored procedure after the execution of first before I can call stored procedure again. I really don't know how to do this.

如何修改此代码以便能够第二次调用存储过程?我真的很困惑这个。在我再次调用存储过程之前,似乎需要在执行第一个之后关闭存储过程。我真的不知道该怎么做。

1 个解决方案

#1

I'm guessing that you're getting an "out of sync" error?

我猜你得到了“不同步”的错误?

You need to release resources by calling close() on your result set before you can make another call to the database on the same connection. Since you named your result variable $sql, you need to make a call $sql->close().

您需要通过在结果集上调用close()来释放资源,然后才能在同一连接上再次调用数据库。由于您将结果变量命名为$ sql,因此需要调用$ sql-> close()。

For example:

<?php
if( $result = $mysqli->query( "call selectproducts()" ) ) {
    $i = 1;
    while( $row=mysqli_fetch_array( $result ) ) {
        $id=$row[ 'prodid' ];
        $date=$row[ 'prodname' ];
        $item=$row[ 'proddescription' ];
        $qtyleft=$row[ 'prodsupplier' ];
        $qty_sold=$row[ 'proddate' ];
        $price=$row[ 'prodprice' ];
        $sales=$row[ 'prodquantity' ];

        if( $i % 2 ) {
?>
            <tr id="<?php echo $id; ?>" class="edit_tr">
<?php
        } else {
?>
            <tr id="<?php echo $id; ?>" bgcolor="#f2f2f2" class="edit_tr">
<?php
        }
?>
                <td class="edit_td"><span class="text"><?php echo $date; ?></span></td>
                <td><span class="text"><?php echo $item; ?></span></td>
                <td><span class="text"><?php echo $qtyleft; ?></span></td>
                <td>
                    <span id="last_<?php echo $id; ?>" class="text">
                        <?php echo $qty_sold; ?>
                    </span> 
                    <input type="text" value="<?php echo $rtrt; ?>"  class="editbox" id="last_input_<?php   echo $id; ?>"/>
                </td>
                <td>
                    <span id="first_<?php echo $id; ?>" class="text"><?php echo $price; ?></span>
                    <input type="text" value="<?php echo $price; ?>" class="editbox" id="first_input_<?php echo $id; ?>" />
                </td>
                <td>
                    <span class="text"><?php echo $dailysales; ?><?php echo $sales; ?></span>
                </td>
            </tr>
    <?php
    $i++;
    }

    $result->close();
}
    ?>

#1