0

I have problem, I would like to make update when hash is same. hash is unique at table. I cant find the right way to do this.

mysql_query("INSERT INTO shop_product_search3_muokattu2 
            (id, hash, product_name, type, manufacturer, mini_products) VALUES
            (NULL, '".$hash."', '".$row['product_name']."', '".$row['type']."', '". $row['manufacturer']."', '".$row['mini_products']."') 
            ON DUPLICATE KEY 
               UPDATE mini_products += VALUES('".$mini_products."') ") 
            or die(mysql_error());
ʰᵈˑ
  • 11,279
  • 3
  • 26
  • 49
Marko Ylitalo
  • 1
  • When I try to add new $row['mini_products'] to behind of old $row['mini_products'] when hash matches. I dont know to update database like that. I just made it to add new to replace old data, but I want to make them add together. mini_products = '".$row['mini_products']."' ") or die(mysql_error()); – Marko Ylitalo Sep 05 '14 at 11:13
  • Obligatory: If this is PHP (which it looks like), mysql_query is deprecated http://php.net/manual/en/function.mysql-query.php – Jaydee Sep 05 '14 at 12:00
  • Yeah its PHP, I try to search right way to do this. – Marko Ylitalo Sep 05 '14 at 12:09
  • MySQLi or PDO_MySQL Are both referenced in the link – Jaydee Sep 05 '14 at 12:14

1 Answers1

1

This is your query:

INSERT INTO shop_product_search3_muokattu2(id, hash, product_name, type, manufacturer, mini_products) 
    VALUES (NULL, '".$hash."', '".$row['product_name']."', '".$row['type']."', '". $row['manufacturer']."', '".$row['mini_products']."') 
    ON DUPLICATE KEY UPDATE mini_products += VALUES('".$mini_products."'

I believe you have a problem with the on duplicate key update statement. The argument to VALUES should be a column name. Alternatively, you can put the value in directly. So, either of the following should work:

    ON DUPLICATE KEY UPDATE mini_products += VALUES(mini_products)
    ON DUPLICATE KEY UPDATE mini_products += $mini_products

As a note: I assume that += works, but I would write these as:

    ON DUPLICATE KEY UPDATE mini_products = coalesce(mini_products, 0) + VALUES(mini_products)
    ON DUPLICATE KEY UPDATE mini_products = coalesce(mini_products, 0) + $mini_products
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ON DUPLICATE KEY UPDATE mini_products = mini_products + '".$row['mini_products']."' ") or die(mysql_error()); when I try add them database only show me result of 0. – Marko Ylitalo Sep 05 '14 at 11:18
  • I dont have any clue how to make update statement. It should push new data with old to mini_products column. And I am writing PHP. – Marko Ylitalo Sep 05 '14 at 12:13