Jump to content

UPDATE VALUES


Obodo
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

  • Great Answer 1
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

  • 3 weeks later...

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.