Retrieving rows to update another table MySQL using PHP

Question

I'm trying to extract information from one table to update another table with some of the information from the previous table and some input from the user. Everything is working like it should until I get to the this part of the code.

$amount is passed from another function

$sql="SELECT inventoryID,quantity FROM rel_command_inventory WHERE commandID=".$id;
        $result=mysqli_query($conn,$sql);
        while($data = mysqli_fetch_array($result))
        {
            $sql="UPDATE inventory SET quantity=quantity-".($amount*$data["quantity"])." WHERE id=".$data["inventoryID"];
            $result=mysqli_query($conn,$sql);
         ........
         ........
        }

mysqli_fetch_array($result) is returning this warning and is not executing the next part of the code:

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

I have already tried the code in phpMyAdmin SQL to be sure that I'm retrieving what I want and it works fine. But for some reason in PHP is not working... Please Help.

I'm not strong on php, but could it be due to assigning to $result while in a loop based on it? — Uueerdo, May 15 '15 at 17:12

score 1 · Accepted Answer · answered May 15 '15 at 17:18

The reason is that you are having two $result which is causing the issue. just rename one as following . moreover you can try checking if there isnt a query error:

$sql="SELECT inventoryID,quantity FROM rel_command_inventory WHERE commandID=".$id;
    $result=mysqli_query($conn,$sql);


 if (!$result) {
    printf("Error: %s\n", mysqli_error($conn));
    exit();
  }
  else
  {
    while($data = mysqli_fetch_array($result))
    {
        $sql1="UPDATE inventory SET quantity=quantity-".($amount*$data["quantity"])." WHERE id=".$data["inventoryID"];
        $result1=mysqli_query($conn,$sql1);
     ........
     ........
    }
  }

score 0 · Answer 2 · answered May 15 '15 at 17:13

I think is because you are setting a new value to $result inside the while

$sql="SELECT inventoryID,quantity FROM rel_command_inventory WHERE commandID=".$id;
        $result=mysqli_query($conn,$sql); 
        while($data = mysqli_fetch_array($result))
        {
            $sql="UPDATE inventory SET quantity=quantity-".($amount*$data["quantity"])." WHERE id=".$data["inventoryID"];
            $result=mysqli_query($conn,$sql);  -> here
         ........
         ........
        }

score 0 · Answer 3 · answered May 15 '15 at 17:17

You can actually do this without a loop.

Not php, but I think you'll understand:

UPDATE inventory AS i 
   INNER JOIN rel_command_inventory AS rci
      ON i.id = rci.inventoryID
SET i.quantity = i.quantity-($amount*rci.quantity)
WHERE commandID= $id
;

score 0 · Answer 4 · answered May 15 '15 at 17:21

When you run query with in query's while loop, you should set different connection & result variables. In this case you use same variable so that select query result lossed- for example-

<?php 
//conection: 
$link = mysqli_connect("localhost","root","pass","test") or die("Error " . mysqli_error($link)); 

//consultation: 

$query = "SELECT inventoryID,quantity FROM rel_command_inventory WHERE commandID=".$id or die("Error in the consult.." . mysqli_error($link)); 

//execute the query. 

$result = mysqli_query($link, $query); 

//display information: 

while($row = mysqli_fetch_array($result)) { 
  echo $row["Tables_in_test"] . "<br>"; 

  $link2 = mysqli_connect("localhost","root","pass","test") or die("Error " . mysqli_error($link));
  $query2 = "UPDATE inventory SET quantity=quantity-".($amount*$data["quantity"])." WHERE id=".$data["inventoryID"] or die("Error in the consult.." . mysqli_error($link2));
   $result2 = mysqli_query($link2, $query2);
    while($row2 = mysqli_fetch_array($result2)) {
    echo "<pre>";
    print_r($row2);
    echo "</pre>";
  }

} 
?>

Retrieving rows to update another table MySQL using PHP

4 Answers4