php error when using stored procedure

Question

I have a php code with 4 Menus(products,suppliers,purchases,customers). Each menu has its own stored procedure. EACH meanu has these code:

Code for Products:

<?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;
?>

My problem is whenever I tried to use stored procedure in other menus or even in 1 menu this error in other meanus (suppliers,purchases,customers):

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

To make it short, If i included just even one(1) stored procedured, the error occurs. The only thing that everything will work is that when i use it in normal way (SELECT * from table).

What could be the problem?please help me..This is the only problem in my project.

Mysql Stored Procedure Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectproducts`()
begin
select * from products order by ProdID;
end

Here's the full code: http://jsfiddle.net/WKKD4/

Sorry I have no other place to put it on.

So it seems that your SQL is failing. What happens if you run that directly on your MySQL server? What shows up in the logs? — Daedalus
– Daedalus, Commented Mar 25, 2013 at 14:25
Sorry but I don't understand. I'm using xampp. Can you elaborate further? — scarface23
– scarface23, Commented Mar 25, 2013 at 14:28
and if you do this change: $sql=$mysqli->query("call selectproducts()"); $i=1; while($row=$mysqli->fetch_array($sql)){ — Hackerman
– Hackerman, Commented Mar 25, 2013 at 14:28
the method fetch_array should be called from the mysqli_result object — d4rkpr1nc3
– d4rkpr1nc3, Commented Mar 25, 2013 at 14:29

Amit · Accepted Answer · 2013-03-25 14:44:19Z

2

MySQL Procedure does not return result set. The way you can access results from a MySQL procedure is as below.

CREATE PROCEDURE selectproducts (OUT ver_param VARCHAR(25))
BEGIN
  # Set value of OUT parameter
  SELECT count(ProdId)  INTO ver_param from products;
END;

Now to access the output in your PHP script, execute below query immediatly after the procedure call.

$sql = $mysqli->query("call selectproducts(@productcount)");
$results = $mysqli->query ("select @productcount as COUNT");
$rows = mysqli_fetch_array($results);

answered Mar 25, 2013 at 14:44

Amit

1,3858 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

scarface23 Over a year ago

It returna values in first store procedure, when i use it again for other menus, it has errors.

Amit Over a year ago

Value is stored in mysql variable which is specific to current mysql session. Also when you execute another sql statement, variable value may be unset. Always fetch procedures return value after you call your procedure

Amit Over a year ago

I may not be around for long, can you please provide your code snippet?

scarface23 Over a year ago

I have posted the link in my post. Please check it out

scarface23 Over a year ago

If i'll follow your answer, I will be creating another procedure because the procedures are used in my vb6.

Handy Manny · Accepted Answer · 2013-03-28 07:53:41Z

1

Try this one:

**

$sql->close();
$mysqli->next_result();

**

Place this after fetching the results and see the magic.

answered Mar 28, 2013 at 7:53

Handy Manny

3881 gold badge5 silver badges20 bronze badges

1 Comment

scarface23 Over a year ago

WOW!Brilliant..really really really brilliant!

Evert · Accepted Answer · 2013-03-25 14:46:39Z

0

You are getting back multiple results. The result of the SELECT as well as the result of the call.

To get to the 'next' result, you must call:

<?php

  // Assuming $connection is your MySQLI object

  while($connection->next_result()) $connection->store_result();

?>

answered Mar 25, 2013 at 14:46

Evert

101k18 gold badges136 silver badges221 bronze badges

Comments

Hackerman · Accepted Answer · 2013-03-25 18:41:50Z

0

Try this change on your code:

<?php

    $result = $mysqli->query("call selectproducts()");
    $i=1;
    while($row = $result->fetch_assoc()){
        $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;
    ?>

It's a minor change but it should works.

--------EDITED---------

Just for debug, replace the code with this:

$rs = $mysqli->query("call selectproducts();");
while($row = $rs->fetch_object())
{
var_dump($row);
}

------------EDIT2----------------------

Continuing with the debuugging stuff....make a new php script named test.php....put this code in it:

 <?php
  include('db.php');

  $query = "call selectproducts()";

  if ($result = $mysqli->query($query)) {

      /* obtener array asociativo */
      while ($row = $result->fetch_assoc()) {
          echo $row['prodid']."------".$row["prodname"])."</br>";
      }

      /* liberar el resultset */
      $result->free();
  }


?>

If this shows your data in the right way, then your problem is not here. Can you post the result of this script please.

Saludos ;)

edited Mar 25, 2013 at 18:41

answered Mar 25, 2013 at 14:43

Hackerman

12.3k2 gold badges37 silver badges47 bronze badges

3 Comments

scarface23 Over a year ago

The result of first menu is also the result of other menus. Same results in all menus.

scarface23 Over a year ago

Still has errors. I think you should see the whole code for clear understanding.

scarface23 Over a year ago

You are my only hope. Once all of the menus can call stored procedure and i'll be fine..Please help me until the end. I can send you th whole project if you want. Just this one please.

Collectives™ on Stack Overflow

php error when using stored procedure

4 Answers 4

5 Comments

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related