Jump to content

CONCAT with WHERE clause?


ChenXiu
 Share

Go to solution Solved by ChenXiu,

Recommended Posts

Posted (edited)

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 by ChenXiu
Link to comment
Share on other sites

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).

Link to comment
Share on other sites

Posted (edited)
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 by ChenXiu
Link to comment
Share on other sites

Posted (edited)
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 by ChenXiu
Link to comment
Share on other sites

Posted (edited)

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 by Barand
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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                     |
+---------+-----------------------------+

 

Link to comment
Share on other sites

  • Solution

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 ); }


 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.