Updating stock in MySQL with PHP

[luismg]

Hi everyone,

I need help with a piece of code wich is supposed to update the stock quantity on table "products" each time a new transaction is entered in table "transactions".
When the transaction type is "purchase" the quantity entered should be added to the stock qty, and when the type is "sell" the quantity should be substracted from the stock quantity.

This is what I've done so far, and even though the transaction is entered, I can't manage to update the stocks in "products":

<?php
$db = mysql_pconnect("localhost", "root");
mysql_select_db("data",$db);

mysql_query("INSERT INTO transactions (type, idproduct, quantity) values ('$type', $idproduct, $quantity)",$db);

if ($type == 'purchase')
{
mysql_query("UPDATE products SET stock = stock + $quantity where idproduct=$idproduct;",$db);
}
else
{
mysql_query("UPDATE products SET stock = stock - $quantity where idproduct=$idproduct;",$db);
}

?>

Any advice should be highly appreciated...
Thanks!

LUIS

[Tewl]

you might want to retrieve the value of stock then do your math
$stock = $stock + $quantity
then use SET stock=$stock in ur query
im not sure but its just an idea

[cgraz]

couple things:

use single quotes around the mathematics in your query
don't use a semi-colon within the query, only at the end
use mysql_error() to print out the error if there is any

Try this

if ($type == 'purchase') { 
   mysql_query("UPDATE products SET stock = 'stock + $quantity' WHERE idproduct='$idproduct'") or die(mysql_error()); 
} else { // maybe wise to use an elseif here, incase they get to the page by mistake
   mysql_query("UPDATE products SET stock = 'stock - $quantity' WHERE idproduct='$idproduct'") or die(mysql_error()); 
} 

Cgraz

[luismg]

Thanks TEWL and CGRAZ for your tips.
I tried CGRAZ way first and it worked great!

I guess I will have to pay more attention to those single and double quotes and when to use them properly...