CONCAT with WHERE clause?

[ChenXiu]

TL;DR
Best way to CONCAT with a "WHERE" clause?

Here is my "flavor" table, where flavors get added.

CREATE TABLE mytable (
sessionid varchar(255) DEFAULT NULL,
flavor varchar(25) DEFAULT NULL,
id int(4) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
UNIQUE KEY sessionid (sessionid)
)

I run this query:

$query = "insert into mytable ( sessionid , flavor ) values
( '".session_id()."' , 'vanilla, ' )
ON DUPLICATE KEY UPDATE
flavor = CONCAT(flavor, VALUES(flavor))";

The "flavor" column now has "vanilla."

I run subsequent queries using "chocolate" and now the "flavor" column now contains "vanilla, chocolate, "

Running the query again using "strawberry" will make the column contain "vanilla, chocolate, strawberry, "

PROBLEM: if I run the query again using "chocolate", I do not want chocolate to appear twice!
( I just want "vanilla, chocolate, strawberry" I don't want chocolate to be doubled up "vanilla, chocolate, strawberry, chocolate" )

I tried appending a "WHERE" clause (... where flavor not like 'chocolate' ...) but that doesn't work because it's against the mySQL rulebook.

How can this be accomplished without having the additional "overhead" of a second mySQL query?
(and without the yearning for an ice cream sundae?)

Thank you.

Edited June 4, 2021 by ChenXiu

[requinix]

You accomplish this by not storing multiple values in a single column.

Create another table that will remember each sessionid / flavor pair. There will be one row for session_id() and vanilla, then a second row for chocolate, then a third row for strawberry. If you make the sessionid + flavor unique then you can try to insert another chocolate row but MySQL will tell you it can't (because there already is one).

[ChenXiu]

10 hours ago, requinix said:

Create another table that will remember each sessionid / flavor pair.

Already doing this while I await a solution.

Hopefully there are some mySQL experts out there.

My question is: How can I make "on duplicate key update" update a column only if a specified condition is met?

Edited June 4, 2021 by ChenXiu

[Barand]

What do want to update if there is a duplicate key?

[ChenXiu]

5 hours ago, Barand said:

What do want to update if there is a duplicate key?

My table structure is:

CREATE TABLE mytable ( 
sessionid varchar(255) DEFAULT NULL,
flavor varchar(25) DEFAULT NULL,
id int(4) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
UNIQUE KEY sessionid (sessionid)
)

And my query updates the 'flavor' column:

$query = "insert into mytable
(sessionid , flavor ) values
( '".session_id()."' , 'vanilla, ' )
ON DUPLICATE KEY
UPDATE flavor = CONCAT(flavor, VALUES(flavor)
)";

Basically, if the flavor column already has the strings vanilla chocolate and strawberry, I would not want the mySQL query to concat "chocolate" to the flavor column, because the flavor column already contains "chocolate"

If I have a hot fudge sundae with pinapple sauce and strawberry sauce, and a new waiter comes by offering chocolate sauce, I will say "Yes, UPDATE my sundae, please concat it with chocolate."
However, if a waiter comes by with more strawberry sauce (which is already on my sundae), I will tell the waiter, "No, do not insert chocolate into my sunday. Do not update it or concat it."

 

Edited June 4, 2021 by ChenXiu

[Barand]

You seem to have missed the plot.

CREATE TABLE `mytable` (
  `user_id` int(11) NOT NULL,
  `flavor` varchar(45) NOT NULL,
  PRIMARY KEY (`user_id`,`flavor`)
);

Add 3 records, 1 vanilla and 2 chocolate

insert ignore into mytable (user_id, flavor) 
values
(1, 'vanilla'), 
(1, 'chocolate'), 
(1, 'chocolate');

View the table

mysql> select * from mytable;
+---------+-----------+
| user_id | flavor    |
+---------+-----------+
|       1 | chocolate |
|       1 | vanilla   |
+---------+-----------+
2 rows in set (0.00 sec)

 

Edited June 4, 2021 by Barand

[ChenXiu]

No, you missed the plot.
Remember a few days ago you taught me how to CONCAT the "flavor" column?
You said to make Sessionid a "unique" key, thus your "ON DUPLICATE KEY UPDATE" suggestion.
So, my question is when using an "Insert.....on duplicate key update...concat" how do I NOT concat if the value is already there?
I am trying to learn mySQL and all the things I can and can't do. I am trying to learn if I can add a condition to an "Insert.....on duplicate key update...concat" query.
If you scroll back up to the top to my table definition, you will regain the plot, rather than going off on a tangent.

 

[Barand]

On 6/4/2021 at 2:31 AM, requinix said:

You accomplish this by not storing multiple values in a single column.

Create another table that will remember each sessionid / flavor pair. There will be one row for session_id() and vanilla, then a second row for chocolate,

Having agreed with the solution that @requinix suggested, and which I showed you how to implement, why are you still persisting with your "ON DUPLICATE ... CONCAT" ?

Or do you want to end up with...

+---------+-----------------------------+
| user_id | flavor                      |
+---------+-----------------------------+
|       1 | chocolate, chocolate        |
|       1 | vanilla                     |
+---------+-----------------------------+

 

[ChenXiu]

Thank you Barand. Appreciation offered.
Fortunately (and unfortunately) I realized that there is no answer to my original question (it can't be done).
QUESTION: Can a "where clause" be added to an "insert.....on duplicate key update" query without additional queries?
ANSWER: NO.
REASON: Because for the "...on duplicate key update" portion to know whether some string exists in some column, there must, by definition, be an additional query.

Although I appreciate Your's and Req's answers because they offer alternative "mysql-based" solutions, I don't like them.
Req's answer of creating additional tables and then having to tie them creates all kinds complexity I don't need in my life.
And your answer would result in an abominably large, and endlessly growing, table.

My solution ended up being that $_SESSION["flavor"] captures vistor's $_POST["flavor"], with the following simple line of code:
if ( strpos( $_POST["flavor"] , $_SESSION["flavor"] ) == FALSE ) { mysqli_query( $conn , $barands_concat_query ); }