检查可用租赁库存的运行库存

Question

I'm building a website for a hypothetical rental business and I'm trying to check for available inventory over a specified time span when the user clicks the "Add to Cart" button. I'm having some trouble with the SQL query that is run, mostly (I think) due to my $product_id variable being an integer but the columns I want to query being in the form "item1", "item2", etc.

The logic I was attempting to use was to subtract the desired quantity from the number available in the table, take the minimum of those values over the given time span, and then check if, at any point, that number went below zero.

Here is the relevant code:

switch ($action) {
case 'view':
    $cart = cart_get_items();
    break;
case 'add':
    $product_id = filter_input(INPUT_GET, 'product_id', FILTER_VALIDATE_INT);
    $quantity = filter_input(INPUT_GET, 'quantity');
    $pickup = filter_input(INPUT_GET, 'pickup');
    $return = filter_input(INPUT_GET, 'return');

    // validate the quantity entry
    if ($quantity === null) {
        display_error('You must enter a quantity.');
    } elseif (!is_valid_number($quantity, 1)) {
        display_error('Quantity must be 1 or more.');
    }

    // check for available inventory
    $query = "SELECT MIN((SELECT CONCAT('item',:product_id)) - :quantity) FROM running_inventory WHERE date BETWEEN :pickup AND :return";
    $statement = $db->prepare($query);
    $statement->bindValue(':product_id', $product_id);
    $statement->bindValue(':quantity', $quantity);
    $statement->bindValue(':pickup', $pickup);
    $statement->bindValue(':return', $return);
    $statement->execute();
    $available = $statement->fetch();
    $num_avail = $available[0];
    var_dump($num_avail);
    $statement->closeCursor();
    if ($num_avail - $quantity >= 0) {
    cart_add_item($product_id, $quantity);
    } else {
        display_error('The indicated quantity of stock is not available for your rental period.');
    }
    $cart = cart_get_items();
    break;

And this is the var_dump for $available:

array(2) { ["MIN((SELECT CONCAT('item','6')) - '3')"]=> string(2) "-3" [0]=> string(2) "-3" } string(2) "-3"

It's obviously just subtracting $quantity from 0, but I can't seem to figure out exactly where the problem is.

Answer 1

Try this:

$query = "SELECT item{$product_id} - :quantity FROM running_inventory WHERE  date BETWEEN :pickup AND :return";