Hello. The database has a table prom_item

id - AUTO_INCREMENT

trip - INT

name - varchar unique

date - varchar

I try to make sure that if there is already an entry in the database that matches the value of the name cell, and this field is edited, then it is necessary that the row is updated in the database, and if not, a new entry is created.

On server:

 foreach ($_POST['prpunkt'] as $k => $f) { $namepp = $_POST['prpunkt'][$k]; mysql_query("INSERT INTO `prom_item` (`trip`, `name`) values('$nw_id', '$namepp') ON DUPLICATE KEY UPDATE name='$namepp')"); }

As a result, it turns out that if I change the record, (the value of $ _POST ['prpunkt'] [$ k]), the string in the database is not updated, but a new one is written, and the old one remains.

what am i missing

  • So no name in UPDATE should be changed, but trip. - Visman
  • @Visman trip is the id of the article to which I attach additional fields. what does it have to do with it? - iKey
  • So you have a unique key hung on the wrong field. If you need to update the name of a unique article with a number from trip, the unique key should be on the trip, and not on the name. - Visman
  • @Visman articles are stored in a separate table. and add. fields in the prom_item table - additional fields may have an indefinite number of each article. that is, the trip values ​​— the id of the article to which this field is attached cannot be unique already. - iKey
  • Apparently, the query INSERT ON DUPLICATE KEY UPDATE does not suit you, since you are changing the field with a unique key, and not the fields accompanying it. - Visman

1 answer 1

There are several options.
1) Attached requests. Since you first need to ask - "Is there any meaning in the database?", And if not, write it down. Example:

Data which is now in the table:
SELECT * FROM user ; +----+--------+-----------+ | id | name | phone | +----+--------+-----------+ | 1 | John | 123123123 | +----+--------+-----------+

We are trying to write something that is already in the table: INSERT INTO user (name, phone) SELECT * FROM (SELECT 'John', '123123123') AS tmp WHERE NOT EXISTS ( SELECT name FROM user WHERE name = 'John' ) LIMIT 1;
SELECT * FROM user ; +----+--------+-----------+ | id | name | phone | +----+--------+-----------+ | 1 | John | 123123123 | +----+--------+-----------+

Trying to write something that is not already in the table: INSERT INTO user (name, phone) SELECT * FROM (SELECT 'Bill', '111222333') AS tmp WHERE NOT EXISTS ( SELECT name FROM user WHERE name = 'Bill' ) LIMIT 1;
SELECT * FROM user ; +----+--------+-----------+ | id | name | phone | +----+--------+-----------+ | 1 | John | 123123123 | +----+--------+-----------+ | 2 | Bill | 111222333 | +----+--------+-----------+

2) ON DUPLICATE. Since there is no upsert in mysql, you can still dig in the direction of "ON DUPLICATE" .