INSERT/UPDATE Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

INSERT/UPDATE Help

Post by fullfocus »

Hello:

I have a form which contains some fields that are populated with data from a table. This same form also has fields that are empty because the data has not been collected yet. The main table is called client with a primary key of client_id.

What I need to do with this form is select the client and update the record into a new table with the missing information. If the client doesn't not exist in this second table, I need to insert the row otherwise if the client does exist in this table because of a previous update, update the record.

When I run my script, the insert works. When I try to run the script again and try to update the record, the script not only updates the record but also inserts another record with the same information.

How can I write my script so it checks whether a client exists in a table and if it does, update the record and if the client does not exist in the table, add the client?

Thanks for the help.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Use REPLACE instead.

This will either INSERT or UPDATE - whichever is needed
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post by fullfocus »

Hello:

Do I change the syntax from:

Code: Select all

$query4 = "INSERT  INTO face_sheet (client_id, agency_email, current_placement, after_hours, current_place_address, current_place_city, current_place_state,
                   current_place_zip, current_place_phone, current_place_fax, director, director_cell_phone, director_email, hsp_name,
                  hsp_cell_phone, hsp_email, medical_problems, therapist, therapist_phone, psychiatrist, psychiatrist_phone, group_therapist, 
                   group_therapist_phone, pcp_name, pcp_phone, dentist, dentist_phone, wrap_around_provider, wrap_around_phone)
          VALUES ('$_POST[client_id]', '$_POST[agency_email]', '$_POST[current_placement]', '$_POST[after_hours]', '$_POST[current_place_address]',
                 '$_POST[current_place_city]', '$_POST[current_place_state]', '$_POST[current_place_zip]', '$_POST[current_place_phone]', '$_POST[current_place_fax]', 
                 '$_POST[director]', '$_POST[director_cell_phone]', '$_POST[director_email]', '$_POST[hsp_name]', '$_POST[hsp_cell_phone]', 
                 '$_POST[hsp_email]', '$_POST[medical_problems]', '$_POST[therapist]', '$_POST[therapist_phone]', '$_POST[psychiatrist]', '$_POST[psychiatrist_phone]', 
                 '$_POST[group_therapist]', '$_POST[group_therapist_phone]', '$_POST[pcp_name]', '$_POST[pcp_phone]', '$_POST[dentist]', '$_POST[dentist_phone]', 
                 '$_POST[wrap_around_provider]', '$_POST[wrap_around_phone]')";


                		
$result4 = mysql_query ($query4) or die (mysql_error());
to:

Code: Select all

$query4 = "REPLACE INTO face_sheet (client_id, agency_email, current_placement, after_hours, current_place_address, current_place_city, current_place_state,
                   current_place_zip, current_place_phone, current_place_fax, director, director_cell_phone, director_email, hsp_name,
                  hsp_cell_phone, hsp_email, medical_problems, therapist, therapist_phone, psychiatrist, psychiatrist_phone, group_therapist, 
                   group_therapist_phone, pcp_name, pcp_phone, dentist, dentist_phone, wrap_around_provider, wrap_around_phone)
          VALUES ('$_POST[client_id]', '$_POST[agency_email]', '$_POST[current_placement]', '$_POST[after_hours]', '$_POST[current_place_address]',
                 '$_POST[current_place_city]', '$_POST[current_place_state]', '$_POST[current_place_zip]', '$_POST[current_place_phone]', '$_POST[current_place_fax]', 
                 '$_POST[director]', '$_POST[director_cell_phone]', '$_POST[director_email]', '$_POST[hsp_name]', '$_POST[hsp_cell_phone]', 
                 '$_POST[hsp_email]', '$_POST[medical_problems]', '$_POST[therapist]', '$_POST[therapist_phone]', '$_POST[psychiatrist]', '$_POST[psychiatrist_phone]', 
                 '$_POST[group_therapist]', '$_POST[group_therapist_phone]', '$_POST[pcp_name]', '$_POST[pcp_phone]', '$_POST[dentist]', '$_POST[dentist_phone]', 
                 '$_POST[wrap_around_provider]', '$_POST[wrap_around_phone]')";


                		
$result4 = mysql_query ($query4) or die (mysql_error());
Is that correct?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

That should do it, then, if the client already exists, the data will be updated, if not, a new row will be inserted
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post by fullfocus »

Hi,

Well, I tried it. The result turned out to be that a new row was inserted. So I have the original record for that client as well as the updated record.

So I'm not sure what I'm doing wrong.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

is client_id a primary key?
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post by fullfocus »

No, client_id is a unique key.
Post Reply