I have a table (id, name, price, menu_id)
I have a page in which every item's name and price is shown, and the users can edit it.
But when I submit, it creates new rows.
The index is on the primary key. Should it be on something else?
I tried the following:
INSERT INTO gerechten
SET gerecht_naam = :naam, gerecht_prijs = :prijs, menu_id = :menuid
ON DUPLICATE KEY
UPDATE gerecht_naam = :naam, gerecht_prijs = :prijs
your query will transform to an update whenever any unique key crashes in your table. Up to you to decide where this key should be placed on. For example, your query could be:
INSERT INTO `gerechten` (`name`, `price`, `menu_id`)
VALUES (:name, :price, :menuid)
ON DUPLICATE KEY UPDATE
name = :name,
price = :price
with a unique index on name AND menu_id for example. (I believe there can be several times the same name as long as it is in a different menu)
Of course if there are various fields combinations that must be unique this is a problematic situation and maybe you should use a different approach, such as a select ... lock statement to check previous existence of the values being edited.
You need to add a UNIQUE key to the gerech_naam column and/or to the gerech_prijs (you can have two columns define uniqueness of a row).
If you were to implement these unique keys to the table, you have to be aware that you would still have a problem because if you change the name or the price it could possibly insert in the DB because the data you provide doesn't exist in the table.