new to MYSQL need help

@mulesy — concise, practical plan that builds on @robbyd and @bhuvan83 without repeating what they already suggested.

Start with a minimal, normalized schema and an audit trail so stock changes are traceable:

products
  id INT PK
  sku VARCHAR(50)
  title VARCHAR(255)
  price DECIMAL(10,2)
  available_qty INT
  updated_at TIMESTAMP

orders
  id INT PK
  status ENUM('pending','confirmed','cancelled')
  total DECIMAL(10,2)
  payment_ref VARCHAR(100)
  created_at TIMESTAMP

order_items
  id INT PK
  order_id INT FK
  product_id INT FK
  quantity INT
  unit_price DECIMAL(10,2)

stock_movements
  id INT PK
  product_id INT FK
  delta INT
  reason VARCHAR(50)    -- e.g. "order", "refund", "manual"
  related_order INT
  created_at TIMESTAMP

A reliable runtime flow (server side only) keeps stock correct under concurrent purchases:

• create an order row with status = "pending" when checkout starts;
• rely on the payment gateway's server-to-server callback (IPN/webhook) to confirm payment;
• on payment confirmation, start a DB transaction, lock the product row(s) (SELECT ... FOR UPDATE), check quantity, write the order_items and stock_movements, decrement stock, set order status = "confirmed", then commit; if stock is insufficient rollback and mark order accordingly.

Example pseudocode flow:

BEGIN;
SELECT available_qty FROM products WHERE id = ? FOR UPDATE;
IF available_qty >= qty_requested THEN
  UPDATE products SET available_qty = available_qty - qty_requested WHERE id = ?;
  INSERT INTO stock_movements(...);
  UPDATE orders SET status='confirmed' WHERE id = ?;
  COMMIT;
ELSE
  ROLLBACK;
  UPDATE orders SET status='cancelled' WHERE id = ?;
END IF;

Practical notes: use InnoDB (transactions, row locks), parameterized/prepared statements to avoid SQL injection, auto-cancel "pending" orders after a timeout to release reservations, keep an audit trail for refunds, and load-test concurrent checkouts. For @sibotho: Visual Studio 2003 can connect to MySQL via MyODBC or a Connector/NET version that supports .NET 1.1 (or use ODBC).