mysql/php insert/update on duplicate key

Question

I have a product info table with more than 130 columns/fields.

I want to write a php script that adds a new product to the table OR updates the existing product if it already exist. The first field is the product key.

The product information is stored in a numbered php array : $product_info[0] to $product_info[130].

Basically something like this :

INSERT INTO table (a,b,c) VALUES ($product_info[0],$product_info[1],$product_info[2])
  ON DUPLICATE KEY UPDATE a='$product_info[0]', b='$product_info[1]', c='$product_info[2]'

Is there something more efficient than typing each of the 130 fields twice?

Answer 1

Yes, there is, use the VALUES() function:

INSERT INTO `table` (a, b, c) VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES (b), c = VALUES(c)

Basically, in the UPDATE part, VALUES(column) will return the specified value for that column for the current row in question. So you can do interesting things like:

ON DUPLICATE KEY UPDATE 
    a = VALUES(a), 
    b = VALUES(b) + VALUES(c), 

The beauty of that syntax, is it also supports multiple insert rows:

INSERT INTO `table` (a, b, c) 
    VALUES (?, ?, ?), 
    VALUES (?, ?, ?), 
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES (b), c = VALUES(c)