mysql: Concatenate string to column

Question

I'm trying to add a new message to a "messages" column in order to keep the history in one place. I'm able to update the field with a new message but I can't concatenate them. Can you please help?

This is my code:

$stmt = mysqli_prepare($mysqlCon, "INSERT  INTO history (`phone`, `message`) VALUES (?, ?) 
ON DUPLICATE KEY SET message = CONCAT(message,VALUES(message));");

mysqli_stmt_bind_param($stmt, 'ss', $phone, $message);

$phone= $_POST['from'];
$message = $_POST['message'];

EDIT: Why the down vote without explanation?

My assumption is that you received a downvote because your question does not state what errors you receive or in what way it fails. You would need to check echo mysqli_error($mysqlCon); if the mysqli_prepare() call is failing. It looks like your syntax is incorrect - ON DUPLICATE KEY UPDATE message =... instead of ON DUPLICATE KEY SET message = ... dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — Michael Berkowski
– Michael Berkowski, Commented Mar 7, 2015 at 19:18
Always do error checking when a piece of code doesn't yield the results you want. mysqli_prepare will result in either an object or false. After $stmt = ... is done, do an if ($stmt === false) { ... error handling ... } before proceeding with other operations. Whoever downvoted you should have added a comment to help improve the question. — zedfoxus
– zedfoxus, Commented Mar 7, 2015 at 19:36

zedfoxus · Accepted Answer · 2015-03-07 19:27:37Z

MySQL 5.6.14:

Use:

insert into history (phone, message) 
values (1, 'test') 
on duplicate key 
    update message = concat(message, values(message));

Let's take an example:

CREATE TABLE `history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone` int(11) DEFAULT NULL,
  `message` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB;

Let's add initial data:

insert into history (phone, message) values (1, 'test');

+----+-------+---------+
| id | phone | message |
+----+-------+---------+
|  1 |     1 | test    |
+----+-------+---------+

Let's add it again with on duplicate key using the set word:

insert into history (phone, message) values (1, 'test') on duplicate key set message = concat(message, values(message));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set message = concat(message, values(message))' at line 1

Let's add it again with on duplicate key using update word:

insert into history (phone, message) 
values (1, 'test') 
on duplicate key 
     update message = concat(message, values(message));

Query OK, 2 rows affected (0.01 sec)

+----+-------+----------+
| id | phone | message  |
+----+-------+----------+
|  1 |     1 | testtest |
+----+-------+----------+

Hope this example helps.

Collectives™ on Stack Overflow

mysql: Concatenate string to column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related