pretty desparate

adrianqx

Hi i designed a website which displays a table containing name of product and number of products i have stored for the user ,below the table i have created a form which has the product name and next to it there is a input field for the user to enter the number of products he wants delivered to him ,i was wondering how will it be possible for PHP to subtract the figure entered in the form from the figure displayed in the table on the page when a user clicks on the submit button.

Please im pretty desparate

bough

If you want to update the amount in the database, you can first select it in your PHP script, then update it with the amount entered in the form, and then update the database with the new amount

$sql = "select id,no_products from table1";

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result))
{
$amount = $row['no_products'];
$prodID = $row['id'];
}

$amountin = ?(value from form)
$amount = $amount - $amountin;

$sql = "update table1 set no_products = $amount where id = $prodID";
$result = mysql_query($sql);

adrianqx

LIke i had hinted ealier im a newbie so if this sounds ignorant dont fall from ur sit
i took ur code and added to mine and im getting this error "unexpected ? in line ##" thats this line "$amountin = ?(value from form)"

any thoughts on where the problem may lie

cassius

If you have copied this line of code into your script:

$amountin = ?(value from form)

You'll probably get an error like that.

You'll have to assign a value to the variable $amountin and bough probably ment that you were suppose to assign a value to that variable from your form. Dependant on what the name of your form field(in this case name="amount"), assign the value like this(assuming you have method="post" in your <form>-tag:

$amountin = $_POST['amount'];

bough

Yes cassius is right,

sorry if i didn't explain it that well,

the line - $amountin = ?(value from form)

was meant to be the value which you get from the form depending on what you have called it, and what method you are using ie POST,GET

I hope it works okay now 🙂

Roger_Ramjet

Not neccessary, or advisable, to do that. You can write an update query that will subtract the input amount from the current amount in the column all in one operation.

$sql = "UPDATE table SET stock_on_hand = (stock_on_hand - $quantity_ordered ) WHERE p_id =  $p_id";

It is 1 query instead of 2 plus some php, which is more efficient.

It is also advisable to do it this way in a multi-user environment because someone else could be updating the table between your read and your write: in which case you would overwrite their update and loose their figures.

cassius

I most certainly agree with Roger Ramjet on the update query.

In this case though, I'm not even sure if adrianqx have come to the update part, so in that case perhaps just a simple javascript can do the display part of updated number of products and Roger Ramjet's update-sql could do the update itself in the database.

Adrianqx could perhaps supply some code to get more help/suggestions.

Roger_Ramjet

Well certainly you should try to use js to ensure that the figure entered is not greater than the figure displayed, but you also need to do that in the unpdate script that the form gets posted to in case they have js off.

Now, the query can be extended to also check when doing the update in case someone else has updated the stored value and the new input now exceeds it. You would do it with an insert .. update query that included a subquery that itself included CASE or IF to check the stored value and adjust the input accordingly.
Table constraint would also work, or triggers, if you had a db that supported them.

The single update query is still the best option even without any of the above. What you have to do immediately after is to query for the new count and check if it is negative. Gets so you need transactions, but then it was stuff like this that caused them to be invented in the first place.

Whatever method you choose, querying after the update to check stored value is best practice if this is at all mission critical data.

Good luck with it.