CODEWITHSUNDEEP
phpmysqlsqlmysqli
Alex Lungu
Alex Lungu

Reputation: 1114

mysql: Concatenate string to column

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?

Upvotes: 0

Views: 312

Answers (1)

zedfoxus
zedfoxus

Reputation: 37069

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.

Upvotes: 1

Related Questions