PHP Update data to sql

Question

In order to insert some data to mysql I use:

$sql = "INSERT INTO MyGuests (fullname, email) VALUES ('John Doe', 'john@example.com')";

But I'd like, before that, if the fullname John Doe for example is already used in a query of MyGuests table then that to be updated with the e-mail. If not, then data to be inserted normally like above. How could I do that? Thanks a lot

Answer 1

You have to query MySQL first to check if there's any results already and if so run a UPDATE query instead of INSERT.

Answer 2

you can use on duplicate key update like this:

INSERT INTO MyGuests (fullname, email) VALUES ('John Doe', 'john@example.com') ON DUPLICATE KEY UPDATE email='john@example.com';

for this to work as expected make sure that fullname is the primary key.

Answer 3

Just check rows using mysql num rows. Use the code below

$query = mysql_query("SELECT * FROM MyGuests WHERE fullname='John Doe'");
if(mysql_num_rows($query)==0){
mysql_query("INSERT INTO MyGuests (fullname, email) VALUES ('John Doe', 'john@example.com')");
}
else{
mysql_query("UPDATE MyGuests SET email='youremailname@name.com' WHERE fullname='John Doe'");
}

If you are using mysqli_* functions then replace mysql with mysqli. I am not using prepared statement , you have to use them to stop sql injection. I suggest you to use PDO or mysqli prepared statements to stop sql injection. Hope tis helps you