How to minus from several MySQL fields a certain value

Question

I am writing products stock script. I have a MySQL table "products_stock":

id   zid   pid  amount
1    123   321   1
2    124   321   5
4    124   566   7
3    125   321   10

So, total amount of product id = 321 in stock is 16. Somebody makes order with pid=321 and qty = 7. I need some php-function, which will minus 7 from column amount starting from the first zid, and update records in table products_stock so that it lookes after like this:

id   zid   pid  amount
1    123   321   0
2    124   321   0
4    124   566   7
3    125   321   9

What I have tried to do:

function mysql_fields_minus_value ($pid, $amount) {
    $q_select_stock_data = "SELECT * FROM `products_stock` WHERE `pid` = '".$pid."'";
    $r_select_stock_data = mysql_query($q_select_stock_data);
    if (mysql_num_rows($r_select_stock_data)>0) { // First we check if there is such product in stock
        // Then we go through all records which have that pid
                    while ($a_select_stock_data=mysql_fetch_array($r_select_stock_data)) {
              //                            echo "<p>".$a_select_stock_data['stock']."</p>";
                        $product_pid = $a_select_stock_data['sku'];
                        $product_qty_order = 7; // qty of pid in order
                        // than we check if we have enough qtys of product in stock
                        if ($a_select_stock_data['amount'] - $amount)>=0)       { // ok- enough

From this point I was stucked.

Thank you for answers!

Can you share what you have tried or do you want us to just write that out for you? — Jay Blanchard, Oct 20 '14 at 21:44
please try to write your function and post the code here if you're stuck at some point. Your question is too broad for stackoverflow. — Ejaz, Oct 20 '14 at 21:44
I have to agree with every one this is a poor question and you should give the community more to go on to help you out — MZaragoza, Oct 20 '14 at 21:50
sorry for poor question, I have added what i was trying to do — Kamnibula, Oct 20 '14 at 21:58
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 20 '14 at 21:58

score 0 · Answer 1 · answered Oct 20 '14 at 22:01

There is no function to do that, you will need to iterate over all the rows until you have finished. I recomend you to get the rows from the DB with the PID you want ordering them by ZID ascendent. After that, try to do something like that:

$i = 0;
while($qty > 0 && $i < count($rows)) {
    if ($row[$i]['amount'] >= $qty) {
        $row[$i]['amount'] -= $qty;
        $qty = 0;
    }
    else {
        $qty -= $row[$i]['amount'];
        $row[$i]['amount'] = 0;
    } 
    ++$i;
}

After that you can store the values again into the DB.

EDIT: Try to use PDO or mysqli instead of mysql to obtain the rows from the DB.

score 0 · Answer 2 · 2014-10-20T22:29:35.950

0

You're not far from achieving your goal. What you need to do is change your MySQL syntax from a SELECT statement, to an UPDATE statement.

This line:

$q_select_stock_data = "SELECT * FROM `products_stock` WHERE `pid` = '".$pid."'";

Should be:

$q_select_stock_data = "UPDATE `products_stock` SET `amount` = '".$qty."' 
WHERE `pid` = '".$pid."'";

Now, you will need to call this function with each pid you would like to update. I suggest something like this: (Keeping in mind that my_sql commands are now depreciated, and that we will also need to provide the function with a database connection object.

function delete_record($db, $qty, $pid){
mysqli_query($db, "UPDATE `products_stock` SET `amount` = '".$qty."' 
WHERE `pid` = '".$pid."'");
}

edited Oct 20 '14 at 22:29

answered Oct 20 '14 at 22:05

I can not delete records, I wrote here simple example. In real life table has much more fields which are needed for statistics of movement of products in stock. – Kamnibula Oct 20 '14 at 22:17
Okay, I've adjusted the query to `UPDATE` the records! – Oct 20 '14 at 22:31

How to minus from several MySQL fields a certain value

2 Answers2