php - A script to update all columns in a mysql database -

hi using php manipulate information in mysql database. looking way update table (all records need updated) based on information within table.

for example have list of products lets 10 each unique id stored in products table. have purchases table has same product id , amount of purchases done each product. want update each product in products table reflect total purchases made each product , store in column called instock part of products table.

how can done?

if understand situation correctly, you're dealing stock-count. when item purchased (represented entry in products table) stock count figure should decreased. should happen within same transaction new entry products table keep data consistent. recommend using trigger on table implement this. you'll find lots of information implementing triggers in mysql on site. trigger use might this:

create trigger update_stock_count     before insert on purchases     each row     begin          update products set stock_count = stock_count - new.quantity_ordered             product_id = new.product_id;     end; 

this trigger doesn't take account there might not enough stock of product, nor handle updates or deletes on purchases table modified so.