PHP Stored Procedure error when calling sequentially or more than once

Question

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.

What is the error message?

Jocelyn
– Jocelyn

2013-03-26 18:37:09 +00:00
Commented Mar 26, 2013 at 18:37 — Jocelyn
– Jocelyn, Commented Mar 26, 2013 at 18:37

Craig Tullis · Accepted Answer · 2013-04-13 22:27:35Z

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

Collectives™ on Stack Overflow

PHP Stored Procedure error when calling sequentially or more than once

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related