添加到数据库字段而不是覆盖它(MySQL UPDATE 函数) [英] Adding to a database field instead of overwriting it (MySQL UPDATE function)

问题描述

我正在尝试更新我数据库中的电子邮件字段...当我们的一位老师通过我们的系统发送邀请时，受邀电子邮件会记录在我们的数据库中.

I am trying to update an emails field in my database... when one of our teachers sends an invitation through our system the invited email is recorded in our database.

我希望老师能够发送电子邮件，然后如果他们忘记某人，他们可以发送另一个邀请，然后数据库字段将保存例如两封电子邮件(原始电子邮件和添加的电子邮件).

I want the teacher to be able to send the email, and then if they forgot someone they can send another invite and the database field will then hold for example two emails (the original and then the added one).

这是我必须将电子邮件存储在数据库中的代码...

Here is the code that I have to store the emails in the DB...

$recipientemail = $_POST['recipientemail'];

// Stores the  (instance) in the instance database
include_once("$_SERVER[DOCUMENT_ROOT]/classes/includes/dbconnect.php");
$sql = ("UPDATE `database1`.`instances` SET `invitemail` = '{$recipientemail}' WHERE `instances`.`instance` = '{$instance}';"); 
$query = mysqli_query($dbConnect, $sql)or die(mysql_error());

每当我邀请新人时，此代码都会覆盖最初邀请的电子邮件...非常感谢您的考虑！

This code overwrites the originally invited email whenever I invite a new person... many thanks for your consideration!

解决方案是采用 MySQLconcat()"函数的形式.我应该更清楚的是，我不是使用数值，而是使用字符串(电子邮件地址).因此，如果我们查看以下答案中的示例:

The solution was in the form of the MySQL "concat()" function. I should have probably been clearer that I am not working with numerical values but rather strings (email addresses). So if we look at the example in the answer below:

UPDATE table SET c=c+1 WHERE a=1;

这里在数学上添加了 c 和一个，我想将电子邮件添加到我的数据库中，甚至用逗号分隔，所以我只是这样做了...

Here it's adding c and one mathematically, I wanted to add the emails to my database even separated by a comma so I simply did this...

UPDATE table SET c = concat(c, ',', '[e-mail]') WHERE a=1;

像魅力一样工作！;-) 并感谢所有的答案！

Works like a CHARM! ;-) And thanks for all the answers!

推荐答案

尝试使用 INSERT ... ON DUPLICATE KEY UPDATE

如果您指定 ON DUPLICATE KEY UPDATE，并且插入的行会导致 UNIQUE 索引或 PRIMARY KEY 中的重复值，MySQL 将执行旧行的 UPDATE.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

例如，如果列 a 声明为 UNIQUE 并包含值 1，则以下两个语句具有类似的效果:

For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

(对于 a 是自增列的表，效果不一样.对于自增列，INSERT 语句增加自增值，但 UPDATE 没有.)

(The effects are not identical for an table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

ON DUPLICATE KEY UPDATE 子句可以包含多个列赋值，用逗号分隔.

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

使用 ON DUPLICATE KEY UPDATE，如果将行作为新行插入，每行的受影响行值为 1，如果更新现有行，则为 2.

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, and 2 if an existing row is updated.

希望这会有所帮助.

这篇关于添加到数据库字段而不是覆盖它(MySQL UPDATE 函数)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！