Member Avatar for mionazraelmiranda

How can I avoid negative numbers from being inserted in my database table?

i have a code that works but will not prompt until the value itself in the table is below 0.

$nega = mysql_query
            ("SELECT * from `bgl` where product_code = '$product_code' and stock > 0"); 
            $row = mysql_fetch_array($nega);

    if ($row['stock'] > 0){
        $query1 = mysql_query("UPDATE `bgl` SET stock = stock - '$quantity' 
                where product_code = '$product_code'");

        die ("<center><font size = 5><br><br><br>Item added to Cart.Continue shopping?
        <a href = BGL.php>Y</a>   |<a href = homepage.php>N</a>");
            }
        else echo "</ br>Out of Stock.";

How can I modify this so when the user enters a value, let's say 20 but the value is only 10 in the database, it will not be updated?

Edited by mionazraelmiranda
  • 2 Contributors
  • 4 Replies
  • 229 Views
  • 45 Minutes Discussion Span
  • Latest Post Latest Post by mionazraelmiranda
Member Avatar for pritaeas

I'd suggest something like this:

UPDATE `bgl` SET stock = stock - '$quantity' WHERE product_code = '$product_code' AND stock - $quantity >= 0
Edited by pritaeas
Member Avatar for mionazraelmiranda

it does not update anymore, thank you very much. but the data is still inserted in the table order.
oh yeah. here's the full sql code:

        `$nega = mysql_query
        ("SELECT * from `bgl` where product_code = '$product_code' and stock > 0"); 
        $row = mysql_fetch_array($nega);

if ($row['product_code'] = $product_code && $row['stock'] > 0){
    mysql_query("INSERT INTO `order` VALUES ('','$user','$product_code',
    '$total','$quantity','$date','$img')");


    $query1 = mysql_query("UPDATE `bgl` SET stock = MAX(stock, stock - '$quantity') WHERE product_code = '$product_code'");

    die ("<center><font size = 5><br><br><br>Item added to Cart.Continue shopping?
    <a href = BGL.php>Y</a>
    |<a href = homepage.php>N</a>");
        }
    else echo "</ br>Select Product Code";`

I tweeked it a bit.

Edited by mionazraelmiranda
Member Avatar for pritaeas

but the data is still inserted in the table order

That part was not in your original question. I am not psychic ;)

Member Avatar for mionazraelmiranda

oh sorry haha. I thought it would work so I didn't post the part about the insertion.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.