Insert and/or update

[phppup]

Another episode of brainstorming has left me with more questions than answers. Guidance, advice, and solutions would be welcome.

I'll try to make it simple (as I haven't even gotten off the launchpad yet): table "info" holds id, fname, lname, email, phone, fav_song, fav_pet, fav_food.

Initially, a user will fill out a form with only fname, lname, email as required.

Hypothetical situation: user submitted form with required fields and listed LaLaLa as fav_song.  Next day, user got a dog.

My thought process: if email does not exist... insert as new user

If email does exist UPDATE fav_pet as 'dog' only when ==null.  

As a side note, will identifying 'email' as a unique key help me?  And if I do use it as a unique key, can I shut off or edit the SQL error message (so it is not visible to users in its complete statement)?  I've played with UPDATE ON UNIQUE KEY but haven't been able to UPDATE two or more values (ie: if all favs were null) and am not certain it is the right direction to go.

[Barand]

I'll answer the last bit first. In development, set display_errors on. In production set log_errors on instead. That way users don't see the errors but you do when developing.

Retrieve the current record and display its content in the form input fields for the user to edit. If it's a new user the inputs will be blank. Your id should be an auto_increment primary key. Store that in a hidden field i your form.

When the user submits the form

INSERT INTO info (id, fname, lname, email, phone, fav_song, fav_pet, fav_food) VALUES (?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE
    fname = VALUES(fname),
    lname = VALUES(lname),
    email = VALUES(email),
    phone = VALUES(phone),
    fav_song = VALUES(fav_song),
    fav_pet = VALUES(fav_pet),
    fav_food = VALUES(fav_food);

setting the id value to null for new records. If the id already exists then the fields are updated.

Adding a unique index on email is optional, but doing that will make it impossible to add someone else with the same email.

[phppup]

Good to hear from you again, Barand.

I'll give it a try and see if it suits my needs. THANKS.