UPDATE VALUES

[Obodo]

Hello all,

In my shopping app. I want add a delete feature from the admin area. In case there is a dispute for a transaction, the admin can delete the transaction. The problem I am having is that when the transaction is deleted, i want the items bought to be added to their respective total. i.e if i have 200 pepsi, 30 cocacola, 20 fruit juice in stock and a customer bought 2 pepsi, 2 cocacola, 3 juice. definately the total in stock will reduce. I want a situation where after i delete the transaction the items bought will be added back to its remaining quantity.

previous quantity
pepsi 200
coke 30
fruit 20

after purchase
pepsi 180
coke 28
fruit 17

after delete
pepsi 200
coke 30
fruit 20

 

thanks

[gw1500se]

This should be a simple query update. What have you tried and what doesn't work as expected? Please show the code you tried (be sure to use the code icon (<>) at the top of the menu and specify PHP for your code) along with any errors or any difference between what you get and what you expect.

[mac_gyver]

no. do not update quantities or delete data to accomplish this. databases are for recording information. by updating/deleting data, you lose an audit trail that would let you know if a programming mistake, an accidental key was pressed, or nefarious activity changed a value.

you would INSERT data for every order/transaction that affects a value. a sale would insert a row into an order/transaction table with a 'type' indicating it is for a sale, then insert row(s) into an order_item table for each item that was sold with the order_id, item_id, and quantity. to void a sale, you would insert another row into the order/transaction table with a 'type' indicating it is for a void/return, with a reference to the original order_id, then insert row(s) into the order_item table, with a negative quantity for the items that are returned and will be restocked (some of the items might have been kept, some might have been damaged, and won't be restocked.)

to get the total quantity you would just SUM() the quantities per item_id.

[mac_gyver]

LOL, i see that the same/similar information as above was given in one of your previous threads.

[Obodo]

5 hours ago, mac_gyver said:

no. do not update quantities or delete data to accomplish this. databases are for recording information. by updating/deleting data, you lose an audit trail that would let you know if a programming mistake, an accidental key was pressed, or nefarious activity changed a value.

you would INSERT data for every order/transaction that affects a value. a sale would insert a row into an order/transaction table with a 'type' indicating it is for a sale, then insert row(s) into an order_item table for each item that was sold with the order_id, item_id, and quantity. to void a sale, you would insert another row into the order/transaction table with a 'type' indicating it is for a void/return, with a reference to the original order_id, then insert row(s) into the order_item table, with a negative quantity for the items that are returned and will be restocked (some of the items might have been kept, some might have been damaged, and won't be restocked.)

to get the total quantity you would just SUM() the quantities per item_id.

Thanks. But I am not totally deleting the data, i am moving them to a different table. I am still trying to comprehend the issue of not updating the records since the goods are returned. if we don't update the record, it means the record we have is not correct as the returned goods will be taken to the store for reselling. so we will have an excess that is not reflecting on the record. how do you deal with that? unless of course we do a different record and collation and i feel is one-to-many exercise. what do you think? 

[ginerjm]

He is saying to keep all 'transactions' on file.  With that, you can continue to calculate all of the inventory question you may have by simply doing a query.  If you want to know how much of any item you have currently you simply do a query to grab all the pluses and minuses, sum them, and apply the total to the inventory quantity.

[mac_gyver]

moving data is also not the correct way of doing this. it's a lot of work for nothing.

the record that was (should be) inserted for each sale is still correct. it defines all the who, what, when, where, and why information about the sale that was made. if something is returned, that's a different transaction that affects the quantity and it is defined by the who, what, when, where, and why information in the void/return record that gets inserted.

Edited June 21, 2022 by mac_gyver

[Obodo]

Sorry. I've been away for a while. Thanks all. I will do as suggested.

ps
btw: i have been able to do the reset and delete as i planned initially but wont be implementing it. i will go with your suggestion.

Thanks