[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

php insert or update

Posted on 2010-01-01
4
Medium Priority
?
240 Views
Last Modified: 2012-05-08
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
$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 ";

Open in new window

rec.GIF
rec2.GIF
0
Comment
Question by:teera
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
rpkhare earned 1000 total points
ID: 26160787
I suspect this has to do with the following line in your code:

on duplicate key update  qutiy = qutiy + $quantity ";

If you defined CODE as a key then if you reenter a duplicate CODE, you are firing the Update Query.

Either you remove the CODE from the key or you remove the above mentioned Duplicate Key statement.
0
 

Author Comment

by:teera
ID: 26160970
Hi  rpkhare
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 ;
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 26161541
You mean that when you insert a record with Product Code '001', it gets updated instead of inserting?
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 26164189
Suggest you define the CODE column as MySQL UNIQUE.  MySQL will throw error 1062 if you attempt to insert a duplicate value into a unique column.  This will help with data integrity.

Then to process your increased quantity, you might want to select the row with the appropriate code.  If it is not there, use INSERT.  If it is there, use UPDATE.  The "replace into" concept of MySQL is not standard SQL, so I avoid it.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month18 days, 13 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question