[SOLVED] edit a back dated record...

[rsammy]

i have this table, admission with the following fields - id, admit_date, admit_location, admit_status, room_no, pat_id, encounter, discharge_date

 

i have admisssion info of the patient with a particular pat_id. the records are as follows:

 

234, 2007/02/01, abc medical  center, follow, 115, 298, 1, 0000-00-00

 

now this patient was admitted on feb 01. each visit by the doctor is an encounter and the encoutner field is incremented for each visit till the patient is discharged(status is do not follow).

 

this way, the doctor visited the patient on feb 02(two times), feb 03, feb 04, feb 06 and feb 08 when he was released  - that is a total of 7 encounters and the chain is closed there. any future visits by the doctor is a new encounter for a new record in the table.

 

now, suddenly, the doctor realizes that he visited the patient on feb 07. so he enters this in the system. how do i just edit the table by just incrementing the encounter field - it shud be 8 as against 7 for the same record/patient.

 

so i am trying to first sort the records for this patient and then see where the new record(feb 07) fits to be able to increment the encounter field.

the query i use to select from the table is...

								$selectadmissionquery=("SELECT pat_id, admit_status, admit_loc, Date_Format(admit_date, '%m/%d/%Y') as admit_date, Date_Format(discharge_date, '%m/%d/%Y') as discharge_date, admit_encounter 
													FROM admission WHERE (pat_id = '$pat_id') ORDER BY admit_date ASC");
							$resultselectadmissionquery=mysql_query($selectadmissionquery);
							if (! $resultselectadmissionquery)
							{
								$error= "Error 333b";
							}
							$num_rowsselectadmissionquery = mysql_num_rows($resultselectadmissionquery);

 

this is the query i am using to insert into the table...

 

						$admissionquery=("Insert into admission( pat_id, admit_status, admit_loc, admit_date, admit_room_no, admit_encounter) VALUES ('$phy_id', '$pat_id', '$admit_status', '$location', '$visit_date_reformat', '$room_no', '$admit_id', '$client_id', '1')");
						$resultadmissionquery=mysql_query($admissionquery);
						if (! $resultadmissionquery)
						{
							$error="Error 334";
						}

[skali]

For this you will have to update all the records after the date that you want to insert in the table in between existing records. Fetch all the history of the patient put it in an array with may be id as the key and encounter as the value. Perform your operation in the array by adding an item properly and then update the database with all these records.

 

FYI: from this it seems like you have not properly normalized your tables and have not properly designed your database.

 

[rsammy]

thanks for your reply! but, can you help me do this? im kinda lost. i know the database is not fully upto standards. but i need to get this out and by today!

 

im not very comfortable with php...

 

thanx

[rsammy]

figured it out. thanx for ur replies tho!