11

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
Nick James
  • 153
  • 2
  • 2
  • 11
  • Looks like invalid `CONCAT` syntax.. You should have some parentheses. [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. – Kermit Feb 11 '13 at 15:52

1 Answers1

29

it should be

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

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.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • what do you mean by *run only once*? – John Woo Feb 11 '13 at 15:58
  • 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. – Nick James Feb 11 '13 at 16:06
  • uhm.. can you give sample records with desired result? – John Woo Feb 11 '13 at 16:07
  • 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' – Nick James Feb 11 '13 at 16:08
  • 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 ....` – John Woo Feb 11 '13 at 16:13
  • ok what thats doing now is, the first time the user submits the form the subject changes from 'subject' to just the suffix so 'newvalue' and then the second time they submit the form the message subject is empy. how can i get it to go from 'subject' to 'subjectNewValue' and then just stay at 'subjectNewVlaue' forever more – Nick James Feb 11 '13 at 16:18
  • oh sorry, it should be `UPDATE ptb_messages SET subject = IF(subject LIKE '%newValue', subject, CONCAT(subject, 'newvalue')) WHERE ....` – John Woo Feb 11 '13 at 16:19