add to existing value in mysql column using CONCAT function?

Question

trying to add text to an existing value in mysql using mysql's concat function?

so existing value is currently 'john' and after function has run will be 'johnewvalue'

i am trying to add a piece of text ':reply' to the existing text which is in my subject column in my database ptb_messages.

i am trying to do this using mysql's concat function but im not getting any result what so ever.

$sql = mysql_query("UPDATE ptb_messages SET subject = CONCAT subject, 'newvalue' WHERE id='".$message_id."'");

can someone please show me a way of getting it to do what i want. thanks.

Looks like invalid CONCAT syntax.. You should have some parentheses. Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. — Kermit
– Kermit, Commented Feb 11, 2013 at 15:52

Community · Accepted Answer · 2017-05-23 11:54:57Z

31

it should be

UPDATE ptb_messages 
SET subject = CONCAT( subject, 'newvalue')
WHERE ...

MySQL CONCAT

in PHP

$sql = mysql_query("UPDATE ptb_messages SET subject = CONCAT(subject, 'newvalue') WHERE id='".$message_id."'");

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Feb 11, 2013 at 15:52

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

John Woo Over a year ago

what do you mean by run only once?

Nick James Over a year ago

well when the user replys to a users message this puts :reply after the subject heading, but if :reply exists then leave subject as is and do not add :reply any more times.

John Woo Over a year ago

uhm.. can you give sample records with desired result?

Nick James Over a year ago

example: subject 'test' when submitted becomes 'test:reply' then if user submits again where message_id = '' then subject DOES NOT update to 'test:reply:reply' instead it stays as 'test:reply'

John Woo Over a year ago

i get your point now but i'm still confuse. how will you know that the last concatenated value is equal to the current subject, well give this a try, UPDATE ptb_messages SET subject = IF(subject LIKE '%newValue', '', CONCAT(subject, 'newvalue')) WHERE ....

|

Collectives™ on Stack Overflow

add to existing value in mysql column using CONCAT function?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related