Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result

brenners · March 2, 2018, 3:36pm

Hello all, I’ve been working on a shopping cart project for some fun and have hit a brick wall. I’m quite new to PHP and was wondering if anyone could help me resolve this issue. Basically, when the user clicks the checkout button everything works correctly, but the following error is given;

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\PizzaStore\index.php on line 86

Here is my code:

[code]<?php
include(“includes/header.php”);
?>

<?php $product_ids = array(); //check if Add to Cart button has been submitted if(filter_input(INPUT_POST, 'add_to_cart')) { if(isset($_SESSION['shopping_cart'])) { //Controls how many products are in the shopping cart $count = count($_SESSION['shopping_cart']); //Create sequential array for matching array keys to product id $product_ids = array_column($_SESSION['shopping_cart'], 'id'); //Checks to see if item already exists in the shopping cart array if (!in_array(filter_input(INPUT_GET, 'id'), $product_ids)) { $_SESSION['shopping_cart'][$count] = array ( 'id' => filter_input(INPUT_GET, 'id'), 'name' => filter_input(INPUT_POST, 'name'), 'price' => filter_input(INPUT_POST, 'price'), 'quantity' => filter_input(INPUT_POST, 'quantity') ); } else { //Increases the quantity key if item already exists in the shopping cart (rather than re-adding item) //Match array key to the id of the products being added to the cart for ($i = 0; $i < count($product_ids); $i++) { if ($product_ids[$i] == filter_input(INPUT_GET, 'id')) { //Increment item quantity for existing item in the array $_SESSION['shopping_cart'][$i]['quantity'] += filter_input(INPUT_POST, 'quantity'); } } } } else { //if shopping cart does not exist, create first product with array key 0 //create array using submitted form data, start from key 0 and fill it with values $_SESSION['shopping_cart'][0] = array ( 'id' => filter_input(INPUT_GET, 'id'), 'name' => filter_input(INPUT_POST, 'name'), 'price' => filter_input(INPUT_POST, 'price'), 'quantity' => filter_input(INPUT_POST, 'quantity') ); } } if(filter_input(INPUT_GET, 'action') == 'delete') { //loop through all items in the cart until GET id variable matches foreach($_SESSION['shopping_cart'] as $key => $product) { if ($product['id'] == filter_input(INPUT_GET, 'id')) { //remove item from cart when it matches with GET id unset($_SESSION['shopping_cart'][$key]); } } //reset session array keys so they match the $product_ids numeric array $_SESSION['shopping_cart'] = array_values($_SESSION['shopping_cart']); } //pre_r($_SESSION); function pre_r($array){ echo '

';
	print_r($array);
	echo '

'; } ?>

<?php if (isset($_POST['checkout'])) { if ($_SESSION['shopping_cart'] != "" && isset($_SESSION['shopping_cart'])) { foreach($_SESSION['shopping_cart'] as $item) { $id = $item["id"]; $quantity = $item["quantity"]; $updateStock = mysqli_query ($con, "UPDATE products SET stock = stock - $quantity WHERE id = '$id'"); if (mysqli_num_rows($updateStock) == 0) { unset($_SESSION['shopping_cart']); //header("Location: index.php"); } } } else { unset($_SESSION['shopping_cart']); //header("Location: index.php"); } } ?>

<?php 

$query = 'SELECT * FROM products ORDER BY id ASC';
$result = mysqli_query ($con, $query);

if ($result):
	if(mysqli_num_rows($result)>0):
		while($product = mysqli_fetch_assoc($result)):
		//print_r($product);
		?>
		<div class="col-sm-4 col-md-3">
			<form method="POST" action="index.php?action=add&id=<?php  echo $product['id']; ?>">
				<div class="products">
					<img src="<?php echo $product['image']; ?>" class="img-responsive" />
					<h4 class="text-info"><?php echo $product['name']; ?></h4>
					<h4>£<?php echo $product['price']; ?></h4>
					<input type="text" name="quantity" class="form-control" value="1" />
					<input type="hidden" name="name" value="<?php echo $product['name']; ?>" />
					<input type="hidden" name="price" value="<?php echo $product['price']; ?>" />
					<input type="submit" name="add_to_cart" style="margin-top: 5px; " class="btn btn-info" value="Add to Cart" />
				</div>
			</form>
		</div>
		<?php
		endwhile;
	endif;
endif;
 ?>

    <div style="clear:both"></div>  
    <br />  
    <div class="table-responsive">  
    <table class="table">  
        <h3>Order Details</h3></th></tr>   
    <tr>  
         <th width="40%">Product Name</th>  
         <th width="10%">Quantity</th>  
         <th width="20%">Item Price(£)</th>  
         <th width="15%">Total(£)</th>  
         <th width="5%">Action</th>  
    </tr>
    <?php   
    if(!empty($_SESSION['shopping_cart'])):  
        
         $total = 0;  
    
         foreach($_SESSION['shopping_cart'] as $key => $product): 
    ?>  
    <tr>  
       <td><?php echo $product['name']; ?></td>  
       <td><?php echo $product['quantity']; ?></td>  
       <td>£<?php echo $product['price']; ?></td>  
       <td>£<?php echo number_format($product['quantity'] * $product['price'], 2); ?></td>  
       <td>
           <a href="index.php?action=delete&id=<?php echo $product['id']; ?>">
                <div class="btn-danger">Remove</div>
           </a>
       </td>  
    </tr>  
    <?php  
              $total = $total + ($product['quantity'] * $product['price']);  
         endforeach;  
    ?>  
    <tr>  
         <td colspan="3" align="right">Cart Total:</td>  
         <td align="right">£<?php echo number_format($total, 2); ?> GBP</td>  
         <td></td>  
    </tr>  
    <tr>
        <!-- Show checkout button only if the shopping cart is not empty -->
        <td colspan="5">
         <?php 
            if (isset($_SESSION['shopping_cart'])):
            if (count($_SESSION['shopping_cart']) > 0):
         ?>
         <div class="checkout">
         <form action="<?=$_SERVER['PHP_SELF'];?>" method="POST">
            <input type="submit" class="btn btn-info" name="checkout" value="Buy Now"/>
         </form>
     	</div>
         <?php endif; endif; ?>
        </td>
    </tr>
    <?php  
    endif;
    ?>  
    </table>  
     </div>

[/code]

Any help would be greatly appreciated. Thanks.

phdr · March 2, 2018, 5:05pm

Troubleshooting runtime errors involves tracing backwards from the error until you find what’s causing it.

So, starting with the line where the error is at, if (mysqli_num_rows($updateStock) == 0) {, what does mysqli_num_rows() do and what is the input parameter supposed to be? From the php.net documentation -

Description Returns the number of rows in the result set.

Parameters Procedural style only: A result set identifier returned by mysqli_query(), mysqli_store_result() or mysqli_use_result().

Does an UPDATE query return rows in a result set at all? No.

The way to test how many rows are affected by an UPDATE, INSERT, or DELETE query is to use mysqli_affected_rows().

However, are you sure about the update query logic? If the number of affected rows for any of the UPDATE queries is zero, do you really want to unset the whole session cart? Hint: if $quantity is zero, which your code isn’t preventing, the number of affected rows for the query will be zero, because the initial value and the final value is the same for the column and the UPDATE query doesn’t actually write the data back to the database in this case.

So, two problems with the current logic - 1) Any zero quantity items should be removed from the cart or not processed at all and 2) you need to decide what action to take if the UPDATE query affects a row or not, and un-setting data probably isn’t a good choice for either case.

There’s actually a number of problems with this code, that has resulted in two to three times more code than is necessary, is insecure, and is doing things that if you were doing this for real, wouldn’t be done this way. The major things are -

The cart definition is too complicated (Keep It Simple - KISS.) If you use the item id as the cart’s array index and just store the quantity as the value, all the code dealing with the cart will be greatly simplified. Once you simplify the cart definition, removing zero quantity items can be accomplished by using a simple call to array_filter(). You can also directly reference any item in the cart, so the loops in the add/delete code will go away.
Using filter_input(), without specifying any filter type, isn’t doing anything except to hide programming mistakes. All these in your code are just hiding php errors due to any typo mistakes between your form and your form processing code and you would want to see any php errors in this case so that php will help you. You are also calling filter_input() multiple times for the same input (Don’t Repeat Yourself - DRY). This is just taking up your time typing things. If you find yourself repeating the same lines or blocks of code, you need to rearrange your code so that you only do things once.
You should be using prepared queries when supplying data values to the sql query statement and for the case where you need to execute a query inside of a loop, using a prepared query will save about 5% of the processing time, since the query would be prepared once, before the start of any looping, and you would only supply the different data values and execute the query inside of the loop.
In real life, you would not use an UPDATE query to record any quantity/money amounts. A programming mistake, hacking, or even just a duplicate form submission will corrupt your data and you won’t have any way of knowing what the values should be. You would instead INSERT a row for each transaction that affects a real value, like what your bank or credit card issuer does. Leave UPDATE queries for editing data, like changing an email address or editing a product description.
You should not use a GET request to modify data or preform an action on the server, such as the delete from cart action. You should always use a POST request when modifying data or performing an action on the server.
$_SERVER[‘PHP_SELF’] is not secure. To cause a form to submit to the same page, just leave the action=’…’ attribute out of the form.