MySQL - insert values ​​if they do not exist, or update and return the identifier

Question

I have a table that looks f.ex. eg:

id (primaryKey, auto_increment) | fruits | Colour

I would like to insert values ​​for fruits and flowers if the fruit value does not exist, otherwise I would like to update only the color and return the identifier of the inserted or updated row.

f.ex. if I have a line with:

1234 | apple | red

and you want to update the apple color to green, not knowing that there is already a line containing the apple. I am using this code:

$sqli = get_db();
$q1 = "INSERT INTO table (fruit, color) VALUES ('apple', 'green') ON DUPLICATE KEY UPDATE color='green', id=LAST_INSERT_ID(id)";
$r1 = $sqli->query($q1);
$insertedOrUpdatedID = $sqli->insert_id();

I want to update an existing line to:

1234 | apple | green

and return the identifier (1234) in $ insertOrUpdatedID.

, . , (123 5 | apple | green) .

:

'fruit' varchar (100) KEY UNIQUE . , :

$insertedOrUpdatedID = mysqli_insert_id( $sqli );

.

!

Answer 1

, fruite UNIQUE KEY

REPLACE INTO table_name (fruit,color) VALUES ('apple','green');

INSERT INTO table_name (fruit,color) VALUES ('apple','green') ON DUPLICATE KEY UPDATE color = 'green;