如果值尚未存在，则更新单元格值

Question

I have to update the column File on the TABLE TEST. This column contains the files related to the row. Each file is separated by a |.

An example could be

ID    NAME    FILE
 1    apple   fruit.png | lemon.png

Now when I add a new file to the FILE column I use this query:

$link->query("UPDATE TEST SET File = CONCAT(File, '$dbfilename') WHERE id = '$p_id'")

where $dbfilename can be e.g. pineapple.jpg |

The problem is that, if $dbfilename is already on the File values, it will be added another time, resulting double.

How can I check if File contains already $dbfilename, and if yes, don't add id, or even don't execute the query?

Answer 1

This is not a good way of storing information in a database. But I'll get to that in a second. To directly answer your question, you could use this as your SQL query:

UPDATE TEST SET File = CONCAT(File, '$dbfilename')
WHERE id='$p_id'
    AND File NOT LIKE '%$dbfilename%'
    AND Lingua='$linguadilavoro'

However, this may cause some issues when one file pineapple.jpg and you try to add another-pineapple.jpg

Really, I think you should consider how this is a horribly bad approach to databases. Consider breaking the files off into a second table. For example:

# a table for the fruit names
CREATE TABLE fruits (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(250) NOT NULL,
    UNIQUE INDEX(name)
);

# a table for file names
CREATE TABLE files (
    fileid INT UNSIGNED NOT NULL DEFAULT PRIMARY KEY AUTO_INCREMENT,
    fruitid INT UNSIGNED,
    filename VARCHAR(250),
    UNIQUE INDEX(fruitid, filename)
);

# find all of the fruits with their associated files
SELECT fruits.id, fruits.name, files.filename
FROM fruits LEFT JOIN files ON fruits.id=files.fruitid

# add a file to a fruit
INSERT INTO files (fruitid, filename)
VALUES ('$fruitID', '$filename')
ON DUPLICATE KEY UPDATE fruitid=LAST_INSERT_ID(id)