Updating Inventory Quantity using PHP and mySQL

Beginners - Learning PHP
#1

One last question, I promise! This site has been amazing for me in terms of understanding PHP; much better than any other resource I have been given in class. The last part of my assignment is to reduce inventory quantity during a movie rental process by 1, but the things I have tried are not using the movie IDs to reduce quantities for any movies that were in the cart.

<?php
$page_title = 'Rental Complete';
session_start();
require '../includes/header.php';

echo '<pre>'; print_r($_SESSION); echo '</pre>';

$customer_id = $_SESSION['customer_id']; 
$total = $_SESSION['total']; 
$paytype = $_POST['paytype'];
$cashed = $_POST['cashamt'];
$currency = '$';

require '../../mysqli_connect.php';
mysqli_autocommit($dbc, FALSE);

$q = "INSERT INTO rentals (customer_id, total, due_date, paytype) VALUES ($customer_id, $total, DATE_ADD(NOW(),interval 7 day), '$paytype')";
$r = mysqli_query($dbc, $q);

if (mysqli_affected_rows($dbc) == 1) {

	$rid = mysqli_insert_id($dbc);
	
	$q = "INSERT INTO rental_contents (rental_id, movie_id, quantity, price) VALUES (?, ?, ?, ?)";
	$stmt = mysqli_prepare($dbc, $q);
	mysqli_stmt_bind_param($stmt, 'iiid', $rid, $id, $qty, $price);	

	$affected = 0;
	foreach ($_SESSION['cart'] as $id => $item) {
		$qty = $item['quantity'];
		$price = $item['price'];
		mysqli_stmt_execute($stmt);
		$affected += mysqli_stmt_affected_rows($stmt);

		$c = "UPDATE movies SET inventory = inventory-1 WHERE id='$id'"; 
		$i = mysqli_query($dbc, $c);
	}

	mysqli_stmt_close($stmt);

	if ($affected == count($_SESSION['cart'])) { 	
		mysqli_commit($dbc);
		unset($_SESSION['cart']);
		
		if ($paytype != 'credit'){		

			$refund = $cashed - $total;
			echo '
			<h2>Change Due: '.$currency.number_format($refund, 2) .'</h2>
			<h3>Rental Complete!</h3>
			';
		}else{
			echo '<h3>Rental Complete!</h3>';
		}

	} else { 	
		mysqli_rollback($dbc);		
		echo '<p>Error 2: The rental could not be processed due to a system error.</p>		
		<a href=index.php>View Cart</a>';
	}

} else {
	mysqli_rollback($dbc);
	echo '<p>Error 1: The rental could not be processed due to a system error.</p>		
	<a href=index.php>View Cart</a>';
}

mysqli_close($dbc);

echo '</div></div>';
require '../includes/footer.php';
?>

When I print what is in SESSION I see this:

Array
(
    [id] => 46
    [total] => 0.5275
    [customer_id] => 1025
    [cart] => Array
        (
            [3026] => Array
                (
                    [quantity] => 1
                    [price] => 0.50
                )
        )
)

The [3026] is the movie ID I am trying to update with this:

foreach ($_SESSION['cart'] as $id => $item) {
	$qty = $item['quantity'];
	$price = $item['price'];
	mysqli_stmt_execute($stmt);
	$affected += mysqli_stmt_affected_rows($stmt);

	$c = "UPDATE movies SET inventory = inventory-1 WHERE id='$id'"; 
	$i = mysqli_query($dbc, $c);
}

The update query is formatted the same as on another page where I am returning the movie and adding to the inventory quantity, which does work. Is there something else I need to use for id=’$id’, or a different way to run the update? Should I even be including those $c and $i lines inside the foreach?

Any suggestions are appreciated!

#2

Actually, you would reduce the value by the quantity, which could be one or more.

Are all the INSERT queries inserting the expected data, and specifically, are the id values being inserted into rental_contents?

To get some more specific debugging information, add the following after the $i = … line of code -

var_dump($id,$i);

You probably don’t want to know this, but you should NOT update a value in a column to keep track of information like this. Your bank, credit card, utility, … companies don’t do it this way and neither should you. Instead, insert a row in a table for each transaction that affects the value, which is what your rental_contents table is doing. You would then write a UNION query between the master inventory table (that holds records for things like inventory received, lost, breakage, …), the rental_contents table, and however you are recording the rental returns, to produce the current inventory amount(s) any time it is needed.

1 Like
#3

Yes, the INSERT queries are working along with everything else. The only thing I need to do for the assignment at this point is the inventory update, which you’ve made a great point about using quantity for instead of just 1.

#4

This helped, once again, I thank you!

Sponsor our Newsletter | Privacy Policy | Terms of Service