php mysql stock deduction

when user order  a pro duct I prepaire the order like the picture below  but how can i deduct the product from ' product ' table
if some item order more than I have on stock i must let user correct the quantity that they want until it fit my stock that i have

Please suggest php or sqlstate ment
CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductId` varchar(80) DEFAULT NULL,
  `ProductName` varchar(255) DEFAULT NULL,
  `ProductType` varchar(255) DEFAULT NULL,
  `ProductPrice` varchar(255) DEFAULT NULL,
  `ProductQuantity` varchar(255) DEFAULT NULL,
  `DateTime` datetime DEFAULT NULL,
  `TimeStamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ProductId` (`ProductId`),
  KEY `ProductId_2` (`ProductId`),
  KEY `ProductName` (`ProductName`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

Open in new window

new.JPG
teeraAsked:
Who is Participating?
 
igni7eConnect With a Mentor Commented:
This is kinda an ugly way, but it's quick:

SQL:
$QUERY = "SELECT QUANTITY-" . $SELECTED_AMOUNT . " AS REMAINING_AMOUNT FROM PRODUCT WHERE PRODUCTCODE = " . $PROD_CODE

PHP:
$PROD_CODE will be the product the user selected
$SELECTED_AMOUNT will be the quantity the user selected
The sql result will be the remaining quantity you have for sale

0
 
leakim971Connect With a Mentor PluritechnicianCommented:
Hello teera,

You don't want negative number for the stock ?
You may have two column/field for the stock :

current_stock (0 to unlimited)
coming_stock (negative or positive)

Regards.
0
 
leakim971PluritechnicianCommented:
Thanks for the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.