Adding to a database field instead of overwriting it (MySQL UPDATE function)

Question

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!

Update

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;

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, ',', 'new@email.com') WHERE a=1;

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

Use INSERT ... ON DUPLICATE KEY UPDATE Check this - http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html — CodeGuy, Feb 05 '16 at 12:18
Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — CodeGuy, Feb 05 '16 at 12:19
Jens I am learning as I go so if you see something amiss please let me know... — Jethro Hazelhurst, Feb 05 '16 at 12:20
@ Rahul & Dan excellent information, especially the duplicate entry, going to try that right now. Thanks so much! — Jethro Hazelhurst, Feb 05 '16 at 12:21

CodeGuy · Accepted Answer · 2017-12-13T07:32:52.010

Try to use INSERT ... ON DUPLICATE KEY 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.

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;

(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.)

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

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.

Hope this will help.

This is the sql statemnt I have been using...: $sql = ("UPDATE `database1`.`instances` SET `invitemail` = (`invitemail` + '{$recipientemail}') WHERE (`instances`.`instance` = '{$instance}');"); unfortunately when I try and store an email the field is 0... — Jethro Hazelhurst, Feb 05 '16 at 12:59

Adding to a database field instead of overwriting it (MySQL UPDATE function)

Update

1 Answers1

Linked