HELP NEEDED ... urgent!

[rsammy]

not sure how to put it.

 

i have a table: temp_pat_id_history with four fields: temp_pat_id, temp_enter_date, merged_pat_id, merged_enter_date.

 

temp_pat_id is the id (a 13 or 18 digit autogenerated id) for a new patient added(registered) to the system.

temp_enter_date is the date the patient is entered(registered) into the system

 

merged_pat_id is the id(a 9 digit id) that the Office Staff assigns to that patient to replace the temp id.

merged_enter_date is the date this change was effected.

 

The scenario is:

 

the doctor ADDs(registers) a new patient on his device(mobile device) and it assigns a temp id to this patient. He sends in the diagnosis/procedure/medication info and the date he saw the patient to the server. All info gets into different tables and the temp_pat_id_history table receives values for the two temp fields(temp_pat_id and temp_enter_date).

 

lets say the patient is John Doe, d-o-b: 12/10/1967

 

when the doctor sends in the info it comes in with a pat_id: P12220070822121554 and enter date: 08-16-2007 11:39:54.

 

Office staff changes the temp_pat_id(18 digit id) to a 9 digit id(this step is mandatory). the temp_pat_id_history table gets updated with the merged_pat_id and merged_enter_date fields getting populated in this step.

 

			$inserttempidquery=("INSERT INTO temp_pat_id_history(temp_pat_id, temp_enter_date) VALUES ('$pat_num', '$visit_date_reformat')"
) ;
			$resulttempidquery=mysql_query($inserttempidquery);
			$Id = mysql_insert_id();

 

John Doe's info in temp_pat_id_history table now shows up as:

 

temp_pat_id:  P12220070822121554; temp_enter_date: 08-16-2007 11:39:54; merged_pat_id: 7410022166; merged_enter_date: 2007-08-19 14:22:59

 

Next Step:

 

Office Staff (erroneously) adds the same patient all over again on the server side. (Checks are in place to prevent duplicates from being entered, but she uses a different last name or date-of-birth and then edits the info).

 

when the OFfice Staff enters this patient, it comes in with a pat_id:714002146 and enter date: system or current date(say, 08-16-2007 15:13:06).

 

There are now two entries for the same patient with the same id.

 

Office Staff realizes that two entries for the same patient exist with different ids when she changes the pat_id from a temporary (18 digit) id to the mandatory permanent id(9 digit) and decides to eliminate one of them by merging the two patients. (this process eliminates one of them - criteria of elimination follows an algorithm already in place).

 

during the merge, the temp_pat_id_history is again updated with the new permanent pat_id and the merged_enter_date information.

 

			$updateidquery="UPDATE temp_pat_id_history
							  SET temp_pat_id= '$SavedID',
								  merged_pat_id= '$PatientID'
							  WHERE temp_pat_id='$SavedID'
							  " ;
			$resultupdateid= mysql_query($updatedidquery) or die("Error updating temp id table: ".mysql_error());

 

the issue is:

 

while doing this: i would like to change the merged_pat_id and merged_enter_date fields for the already updated patient(already changed from temp_pat_id to a permanent(merged_pat_id)).

 

For instance:

the merged_pat_id for John Doe from the doctor's device is 741002164 with the enterdate of the merge time.

The pat_id for John Doe from the Office Staff is 741002166 with the enter date being the time this patient was registered.

 

I need the query to change the merged_pat_id and merged_enter_date of John Doe that came from the doctors device also. In other words, all John Doe s should show the same merged_pat_ssn and enter_date irrespective of where they came from.

 

How can I tweak the query here?

 

Hope I am being clear here@!@!