Stock Level

[zimmo]

I have a site set up where customers come in order multiple products in numbers and then place an order. What I am trying to do is reduce the stock levels in the literature table from the results of the ordering table.

 

The set up I have is.

 

One table with all products in, there are 20 products in here.

The products table has a field for the stock levels that the client controls. They can adjust this as they see fit via a cms, but I need the levels to adjust as people order.

 

The orders are stored in a session, the orders table has a qty field in that the user chooses, so say they order 200 items of product A and 150 items of product B. I need to query the database and adjust the stock levels. I am unsure how to do this.

 

Here is my code so far, which I probably assume is totally incorrect, as nothing is happening. I am no guru.

 

$sql = "SELECT * FROM orders WHERE sid = '$PHPSESSID' "; 
$sql_result = mysql_query($sql); 
if (mysql_num_rows($sql_result) ==0)
{
header("Location: http://www.*****.co.uk/index.php");
exit;
}
else
{
        $SQL = " UPDATE literature SET stock_level = 'stock_level' - '$qty' WHERE pnumber = '$pnumber' ";

        #execute SQL statement
        $result = mysql_db_query( *****,"$SQL",$connection );

        # check for error
        if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    }
}

 

I know I have not got this right, just trying to understand how to sort this, have read and read all day long.

[Mchl]

First try:

$SQL = " UPDATE literature SET stock_level = stock_level - '$qty' WHERE pnumber = '$pnumber' ";

when you put stock_levels in quotes, it is treated as string, not as column name.

 

 

 

 

[zimmo]

I had also tried that, but it is still not deducing the stock level number. That is what I am stuck on getting it to reduce. I dont get any errors either though??

[Mchl]

Are you sure that the first query returns any results?