MrTV
asked on
php insert or update
i am not sure the code below can adjust so that it not insert into the record that have duplicate productcode but it just update the quantity
like picture below
when addjust the product 001 it just update the quantity not insert new record
rec2.GIF
like picture below
when addjust the product 001 it just update the quantity not insert new record
$query = "insert into presell (refid
,productcode,ProductName,qutiy
,price,datecreate,timestamp
)
select $invoiceno, product.ProductId, product.ProductName
, if( ProductQuantity < $quantity, ProductQuantity, $quantity )
, product.ProductPrice,Now(),Now()
from product
where product.ProductId = '$AccNo'
on duplicate key update qutiy = qutiy + $quantity ";
rec.GIFrec2.GIF
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You mean that when you insert a record with Product Code '001', it gets updated instead of inserting?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
plesae explain more
CREATE TABLE IF NOT EXISTS `presell` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`refid` int(150) NOT NULL,
`productcode` varchar(100) NOT NULL,
`productname` varchar(200) DEFAULT NULL,
`qutiy` int(11) NOT NULL,
`price` decimal(11,2) NOT NULL,
`stockcut` enum('n','y') NOT NULL DEFAULT 'n',
`datecreate` datetime DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=336 ;