How can I subtract a user input value from a value in a MySQL table only if the value in the field is equal to or greater than the input value?

Question

I have a form which contains input fields item id, staff id, and quantity. I want to update a table whenever is press issue. I want the value I submit, that is the quantity to be subtracted only if it is less than or equal to the quantity value already in the destination table. Below is my code.

The form:

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("dbtest", $con);

$result = mysql_query ("SELECT * FROM recieved_orders");
echo "<table border = '1' style='margin-left:18px;margin-right:18px;' bgcolor='#CFC'>
                <tr>
                    <th bgcolor='#34495E' colspan='9'>
                        <h1><font color='white' align='center'>&nbsp&nbsp&nbspORDER OFFICE SUPPLIES</font></h1>
                    </th>
                </tr>
                <tr bgcolor='#CFC' font size='18'>
                    <th>Item Id</th>
                    <th>Staff Id</th>
                    <th>Quantity</th>
                </tr>";

        while ($row = mysql_fetch_array($result))
        {
            echo "<form action=\"Updateisue.php\" method=\"post\" enctype=\"multipart/form-data\">";
                echo "<tr>";
                    echo "<td><input type=\"text\" name=\"ItemId\"  size=\"30\" value=\" ". $row ['ItemId'] . "\" readonly></td>";
                    echo "<td><input type=\"text\" name=\"StaffId\" value=\" ". $row ['StaffId'] . "\" readonly></td>";
                    echo "<td><input type=\"text\" name=\"Quantity\" value=\" ".$row ['Quantity'] . "\" readonly></td>";
                    echo "<td><input type=\"submit\" name=\"submit\" size=\"30\" style='background-color:#3366FF' value=\"ISSUE  \"></td>";
                echo "</tr>";
            echo "</form>";
        }
echo "</table>";
mysql_close($con);
?>

Form action:

<?php
include './database-config.php';
$searchError = "";
$searchMessage = "";

function sanitizeString($var) {
    $var = htmlentities($var);
    $var = strip_tags($var);
    $var = stripslashes($var);
    $var = trim($var);
    return $var;
}
$ItemId = sanitizeString($_POST['ItemId']);
$Quantity = sanitizeString($_POST['Quantity']);

if($Quantity<=Quantity){
    $updatePassQuery = "UPDATE stationery SET Quantity=Quantity-$Quantity WHERE ItemId='$ItemId'";
    $executeQuery = mysqli_query($dbh,$updatePassQuery);
if($executeQuery){
    echo " update successful";
    $message  = "update was successful";
    header("location: procurementhome.php");

    } else{
        echo "unsuccessful";
        $error = "update failed";
        // header("location: upstationery.php");
    }
}
else
{
    echo "no more itmes";
}
?>

What is this `$Quantity<=Quantity` ? From where `Quantity` comes here ? — Nana Partykar, Apr 16 '16 at 14:11
Could you give some more details on your code, please? For example, what exactly is not working or where exactly are you missing the solution? — honk, Apr 20 '16 at 14:37

score 0 · Accepted Answer · edited May 23 '17 at 12:23

1) <form> is not allowed inside a <table>. Check form-inside-a-table

2) You have to keep one submit button for all the details. Do whatever changes there and submit.

3) According to Point 2, name of input have to be array type. (Check answer below)

4) In Updateisue.php, using for loop or foreach find each ItemId and execute query.

5) In this line if($Quantity<=Quantity){. I don't know from where you get Quantity value. But, still. What i assumed is : Quantity for that particular ItemId. So, I wrote one query to execute to find quantity.

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("dbtest", $con);
$result = mysql_query ("SELECT * FROM recieved_orders");

echo "<form action=\"Updateisue.php\" method=\"post\" enctype=\"multipart/form-data\">";
    echo "<table border = '1' style='margin-left:18px;margin-right:18px;' bgcolor='#CFC'>
                    <tr>
                        <th bgcolor='#34495E' colspan='9'>
                            <h1><font color='white' align='center'>&nbsp&nbsp&nbspORDER OFFICE SUPPLIES</font></h1>
                        </th>
                    </tr>
                    <tr bgcolor='#CFC' font size='18'>
                        <th>Item Id</th>
                        <th>Staff Id</th>
                        <th>Quantity</th>
                    </tr>";

            while ($row = mysql_fetch_array($result))
            {
                    echo "<tr>";
                        echo "<td><input type=\"text\" name=\"ItemId[]\"  size=\"30\" value=\" ". $row ['ItemId'] . "\" readonly></td>";
                        echo "<td><input type=\"text\" name=\"StaffId[]\" value=\" ". $row ['StaffId'] . "\" readonly></td>";
                        echo "<td><input type=\"text\" name=\"Quantity[]\" value=\" ".$row ['Quantity'] . "\" readonly></td>";
                        echo "<td><input type=\"submit\" name=\"submit\" size=\"30\" style='background-color:#3366FF' value=\"ISSUE  \"></td>";
                    echo "</tr>";
            }
            echo "<tr><td colspan='3'></td><td><input type=\"submit\" name=\"submit\" size=\"30\" style='background-color:#3366FF' value=\"ISSUE  \"></td></tr>";
    echo "</table>";
echo "</form>";

mysql_close($con);

?>

Updateisue.php

<?php
include './database-config.php';
$searchError = "";
$searchMessage = "";

function sanitizeString($var) {
    $var = htmlentities($var);
    $var = strip_tags($var);
    $var = stripslashes($var);
    $var = trim($var);
    return $var;
}

$totalItem = sizeof($_POST['ItemId']);
$Quantity = $_POST['Quantity'];
for($i=0;$i<$totalItem;$i++) {

    $CItemId = sanitizeString($ItemId[$i]);
    $CQuantity = sanitizeString($Quantity[$i]);

    $quantityAvailable = mysqli_query("SELECT Quantity FROM stationery WHERE ItemId='$CItemId ");
    $row = mysqli_fetch_array($quantityAvailable,MYSQLI_ASSOC); 
    $quantityDB = $row['Quantity'];

    if($CQuantity<=$quantityDB){
        $updatePassQuery = "UPDATE stationery SET Quantity=Quantity-$CQuantity WHERE ItemId='$CItemId'";
        $executeQuery = mysqli_query($dbh,$updatePassQuery);
        if($executeQuery){
            echo " update successful";
            $message  = "update was successful";
            header("location: procurementhome.php");
        } else{
                echo "unsuccessful";
                $error = "update failed";
        }
    }
    else
    {
        echo "no more itmes";
    }
}

?>

How can I subtract a user input value from a value in a MySQL table only if the value in the field is equal to or greater than the input value?

1 Answers1