Adding to an existing value in mysql

Question

I am a newbie to mysql. I am making a table named inventory with 4 columns- id,name, price,quantity. Now if name already exists, I am to add the quantity to existing one else create a new row. (name is defined as UNIQUE). I am doing this on php. $name, $quant,$price contain the values.

mysqli_query($con,"INSERT INTO inventory (Name, Quantity, Price) VALUES ($name,$quant,$price)
ON DUPLICATE KEY UPDATE
   Quantity    =  Quantity + $quant ,
  Price = VALUES(Price) ");

I am unable to understand why it's not working correctly. Sometimes Quantity updates correctly, most of the times it doesn't. Rest everything working perfectly. Just the problem with Quantity. Thanks for any help

EDIT: I defined the table on phpmyadmin. Here are the details:
1 id int(11) AUTO_INCREMENT PRIMARY

2 Name varchar(15) latin1_swedish_ci UNIQUE
3 Quantity int(11)
4 Price int(11)

EDIT: It was a cache problem . SOLVED

Post the table definition please. (SHOW CREATE TABLE inventory) — Paul Spiegel
– Paul Spiegel, Commented Mar 27, 2016 at 7:38
Add mysqli_error() function in your query to show the error. — vitorio
– vitorio, Commented Mar 27, 2016 at 7:40
Sorry, it works fine for me :-) At the end i get "Quant=46" (10+5+6+10+15) — Paul Spiegel
– Paul Spiegel, Commented Mar 27, 2016 at 8:21
Might be a cache problem - happens to me again and again :-) - deactivate the cache while developing — Paul Spiegel
– Paul Spiegel, Commented Mar 27, 2016 at 8:29
And also check for negative numbers..i was able to set $quant as -1000 and your code happily accepted that ;) — saan
– saan, Commented Mar 27, 2016 at 9:37

saan · Accepted Answer · 2016-03-27 08:23:25Z

1

Is the $quant variable being updated correctly from your GET variable? Also please try the following query which should do the same as you are trying to do:

  mysqli_query($con,"INSERT INTO inventory (Name, Quantity, Price) VALUES ($name,$quant,$price)
ON DUPLICATE KEY UPDATE
   Quantity    =  Quantity + VALUES(Quantity),
  Price = VALUES(Price) ");

answered Mar 27, 2016 at 8:23

saan

3333 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Halayem Anis · Accepted Answer · 2016-03-27 07:50:12Z

0

problem with delaing with NULL values i guess Try as follow

mysqli_query($con,"INSERT INTO inventory (Name, Quantity, Price) VALUES ($name,$quant,$price)
ON DUPLICATE KEY UPDATE
Quantity    =  IF((Quantity IS NULL), $quant,Quantity + $quant) ,
Price = VALUES(Price) ");

edited Mar 27, 2016 at 7:50

answered Mar 27, 2016 at 7:47

Halayem Anis

7,8052 gold badges28 silver badges45 bronze badges

3 Comments

Paul Spiegel Over a year ago

Quantity = NULL won't work. Use Quantity IS NULL instead.

Halayem Anis Over a year ago

@PaulSpiegel Done Boss :)

Mansimar Singh Over a year ago

Problem is not with NULL values. It picks up the first entry correctly. It's just adding consecutive entries randomly.

ccjjmartin · Accepted Answer · 2016-03-27 08:06:40Z

Please remember that if $name, $quant, and $price are coming from users then this method is subject to SQL injection attacks and should not be used. Directly taking any user input without sanitizing it first is critical mistake that can lead security vulnerabilities.

We now have tools like PDO statements which prepare your SQL for entry into a databases. Please consider using a similar tool which prepares your statements when getting anything from a user.

Users are not to be trusted. Notice the binding of values below:

$query = $mysqli->prepare('
    SELECT * FROM users
    WHERE username = ?
    AND email = ?
    AND last_login > ?');

$query->bind_param('sss', 'test', $mail, time() - 3600);
$query->execute();

The above code is from this article and it discusses the differences between using straight PDO and the mysqli version:

http://code.tutsplus.com/tutorials/pdo-vs-mysqli-which-should-you-use--net-24059

Collectives™ on Stack Overflow

Adding to an existing value in mysql

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related