CODEWITHSUNDEEP
phpmysqldatabasestored-procedures
Handy Manny
Handy Manny

Reputation: 388

PHP Stored Procedure error when calling sequentially or more than once

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

$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.

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.

Upvotes: 1

Views: 271

Answers (1)

Craig Tullis
Craig Tullis

Reputation: 10497

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().

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();
}
    ?>

Upvotes: 1

Related Questions