CODEWITHSUNDEEP
phpmysql
Mansimar Singh
Mansimar Singh

Reputation: 45

Adding to an existing value in mysql

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

Upvotes: 1

Views: 1092

Answers (3)

saan
saan

Reputation: 333

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) ");

Upvotes: 1

ccjjmartin
ccjjmartin

Reputation: 66

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

Upvotes: 0

Halayem Anis
Halayem Anis

Reputation: 7785

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) ");

Upvotes: 0

Related Questions