Hello. Is there a way to keep the custom settings but delete the entire patient database? I can only see how to delete one patient at a time? Thanks.
You can truncate the patient_data table. That should work. Things like the insurance_data, billing_table, etc all have counters so truncating the patient_data table shouldnβt cause too many issues .
You really donβt want abandoned data floating around.
Suggest you check out the interface/patient_file/deleter.php script. Recm modifying a cloned version to batch delete patients by iterating from top level of script.
Then go back into database and reset you auto increment columns or truncate the appropriate tables.
Thanks, Daniel. Can you please explain how to truncate the patient_data table?
Thanks, Jerry. Can you please point to me where I can find that script and how to run it?
I have a few questions.
How many encounters are associated with the patients? Do you want to delete the encounters too?
Do you have insurance data associated with these patients?
Do you care about any of the data associated with the patients?
@sjpadgett offered great advice with checking out the interface/patient_file/deleter.php
script which could be called by a simple script to loop through all patients in the db.
Thanks. I have 9000 patients with many encounters each. I have no insurance information and no data with any of the patients that I want to save. Basically, we have another doctor starting in another clinic, and I want to create a blank database for her using the customized settings that were created for us in our current clinic.
Can you elaborate when you refer to βcustomized settingsβ? Are you referring to just the Administration-> Global settings or are you referring to custom Layout Based Forms, forms, the address book, users, etc.?
The deleter.php script does take care of most the tables related to patients, but may be incomplete for what you are looking for. In the openemr_600 database there are 77 tables that store pid values, but not every table that has patient data has a pid column (i.e. lbf_data).
A few of the tables that are missed by the deleter.php script (I searched the script to find these strings and could not find them):
amc_misc_data
amendments
ac_activity
audit_master
ccda
β¦
and there are quite a few more.
I think the following should be through enough:
- Query the patient data.
- For each pid, run the deleter script.
- Truncate the remaining tables that contain a reference to pids. (Note: some tables have columns like ct_pid, pc_pid, etc.)
- There are tables that hold the next value of things like encounters and such. It might not be necessary to reset these, but maybe Jerry or Stephen can elaborate.
@stephenwaite @sjpadgett - Is there anything that I am missing here?
Actually, I am not entirely sure as an IT consultant set up our system so I donβt know exactly what he customized. So I am thinking of at least the global settings and the layout based forms. I donβt need the address book nor users.
Sorry I am a very basic user. I have no idea how to run the deleter.php script. Can you explain how to do that or point me to a site? Thanks.
I think that Jerry and Stephen (and myself) assumed that you are a developer that has experience with php or mySQL. Currently Iβm not aware of a script that will do exactly what you are looking for so there really isnβt an easy button for this task.
Are you looking for the version of OpenEMR with an empty database to be used in a production environment or is this just for training purposes? If this is being used for training purposes I can write a quick set of instructions that will get you what you need with no guarantee that data issues wonβt happen in the future. This solution should not ever be used in Production.
If this instance of OpenEMR will be used in a production environment it will need more attention to detail and coding will be required, probably 4 - 6 (max) hours of coding and testing.
Thanks, Daniel, just need a quick set of instructions to create an empty database for training. Thank you.
Tom,
Do you have access to phpMyAdmin? This would be easiest. Also, are you running this on LAMP or XAMP?
Before reading this, make a backup of your current database.
Once you get a second instance of OpenEMR going, the following instructions should be fine for training purposes only. Here are a few things that this does:
- We remove all patient demographics, billing, and insurance data.
- We remove all form data and encounter data. The encounter increment is not reset to prevent unexpected behavior.
- We remove all calendar events associated with patients, keeping things like staff schedules.
- We load the sample_patient_data.sql info using the info provided. The current sample_patient_data.sql file has issues. This should work for you.
When you have confirmed that you are in the training database, you can put the following in phpMyAdmin screen. If you have not yet made a backup, read the first line of this post.
copy and paste in its entirety everything below this line.
Truncate table patient_data;
truncate table insurance_data;
truncate table billing;
truncate table form_encounter;
delete from openemr_postcalendar_events where pc_pid > 0;
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMs.β, βenglishβ, ββ, βFarrahβ, βRolleβ, βA.β, β1973-10-11β, β111 Main Streetβ, β92101β, βSan Luisβ, βCAβ, ββ, β456789123β, ββ, β(619) 555-2222β, β(619) 555-3333β, β(619) 555-1111β, ββ, βmarriedβ, βJoeβ, β2003-11-17 12:06:00β, βFemaleβ, βPhil Martinβ, ββ, NULL, βfrolle@pennfirm.comβ, βLatinaβ, ββ, ββ, β3β, β3200β, ββ, β2021-01-01 00:00:00β, β10β, 5,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
)VALUES ( βMr.β, βenglishβ, ββ, βTedβ, βShawβ, ββ, β1947-03-11β, β222 1st Avenueβ, β92101β, βSan Diegoβ, βCAβ, ββ, β920-24-2256β, βLandscape Architectβ, β(619) 555-7568β, β(619) 555-9867β, β(619) 555-7567β, ββ, βmarriedβ, βMarion Shawβ, β2003-11-17 13:47:42β, βMaleβ, βPhil Martinβ, ββ, NULL, βinfo@pennfirm.comβ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β24555β, 1,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMrs.β, βenglishβ, ββ, βEduardoβ, βPerezβ, ββ, β1957-01-09β, β789 Third Avenueβ, ββ, βSan Diegoβ, βCAβ, ββ, β234567891β, βManager of Transportationβ, β(619) 555-4859β, β(619) 555-7821β, β(619) 555-7823 x251β, ββ, ββ, ββ, β2004-01-20 15:43:30β, βMaleβ, βYnez Jonesβ, ββ, 4, βinfo@pennfirm.comβ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β789456β, 4,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMrs.β, βspanishβ, ββ, βNoraβ, βCohenβ, ββ, β1967-06-04β, β155 First Avenueβ, β92101β, βSan Luisβ, βCAβ, ββ, β891-23-4567β, ββ, β(213) 555-5555β, β(213) 555-4444β, β(818) 555-5555β, ββ, βmarriedβ, ββ, β2004-01-16 10:28:22β, βFemaleβ, βPhil Martinβ, ββ, 5, ββ, βLatinaβ, ββ, ββ, β3β, β1500β, ββ, β2021-01-01 00:00:00β, β8β, 8,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βBrentβ, βPerezβ, ββ, β1960-01-01β, β1234 1st Avenueβ, β92101β, βSan Diegoβ, βCAβ, ββ, β123456789β, βAirline Mechanicβ, β(619) 696-5050β, β(888) 480-5050β, β(619) 696-5050β, ββ, βmarriedβ, βMaria Perezβ, β2004-01-14 17:30:09β, βMaleβ, βPhil Martinβ, ββ, 5, βbperez@pennfirm.comβ, βLatinoβ, ββ, ββ, β3β, β2200β, ββ, β2004-04-02 00:00:00β, β1001β, 41,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, ββ, ββ, βJimβ, βMosesβ, ββ, β1945-02-14β, ββ, ββ, βLos Angelesβ, βCAβ, ββ, β545676767β, ββ, β(323) 555-4444β, β(909) 555-6767β, β(310) 555-1212β, ββ, ββ, ββ, β2004-01-15 13:45:08β, βMaleβ, βAdministrator Administratorβ, ββ, 1, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β17β, 17,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, ββ, ββ, βRichardβ, βJonesβ, ββ, β1940-12-16β, β400 West Broadwayβ, β92101β, βSan Diegoβ, βCAβ, ββ, β555-11-1111β, ββ, β(619) 555-5555β, β(619) 333-3333β, β(619) 444-4444β, ββ, βmarriedβ, ββ, β2004-01-16 10:02:41β, ββ, βYnez Jonesβ, ββ, 4, βrichard@pennfirm.comβ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β18β, 18,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βIliasβ, βJenaneβ, ββ, β1933-03-22β, β145 N. East Streetβ, β92111β, βLa Mesaβ, βCAβ, ββ, β111-11-2222β, βretired - school teacherβ, β(619) 555-2222β, β(619) 555-3333β, β(619) 555-1111β, ββ, βsingleβ, ββ, β2004-01-16 10:23:47β, βFemaleβ, βYnez Jonesβ, ββ, 4, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β22β, 22,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βJasonβ, βBinderβ, ββ, β1961-12-11β, β100 West Sepulvedaβ, β92020β, βLos Angelesβ, βCAβ, ββ, β222-11-8888β, βReal Estate Agentβ, ββ, ββ, ββ, ββ, βsingleβ, ββ, β2004-01-16 10:13:50β, βMaleβ, βYnez Jonesβ, ββ, 4, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β30β, 30,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βJohnβ, βDockertyβ, βDβ, β1977-05-02β, β800 West Wayβ, β92101β, βSan Diegoβ, βCAβ, ββ, β111-11-1111β, βPhysical therapistβ, β(567) 789-7891β, β(567) 789-1234β, β(567) 567-5678β, ββ, βsingleβ, ββ, β2004-01-16 10:21:05β, ββ, βPhil Martinβ, ββ, 5, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β25β, 25,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βJamesβ, βJanssenβ, ββ, β1966-04-28β, β111 North Streetβ, β90205β, βIrvineβ, βCAβ, ββ, β555-11-1111β, βOffice Managerβ, β(818) 555-2222β, β(818) 555-2323β, β(5555) 555-1111β, ββ, βsingleβ, ββ, β2004-01-19 14:21:25β, βMaleβ, ββ, ββ, 1, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β26β, 26,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βWallaceβ, βBuckleyβ, ββ, β1952-04-03β, β123 West Streetβ, β90400β, βBarstowβ, βCaliforniaβ, ββ, β999-99-1111β, βAccountantβ, β(555) 111-1111β, β(555) 111-1234β, β(555) 555-5555β, ββ, βdomestic partnerβ, βMike Hartβ, β2004-01-16 10:18:28β, ββ, βYnez Jonesβ, ββ, 4, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β40β, 40,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βRobertβ, βDickeyβ, ββ, β1955-04-12β, β111 North Kearnyβ, β91040β, βTorranceβ, βCAβ, ββ, β214-22-1111β, βProject Managerβ, β(808) 555-1234β, β(808) 555-4444β, β(808) 555-1111β, ββ, βmarriedβ, ββ, β2004-01-16 10:31:45β, ββ, βPhil Martinβ, ββ, 5, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β34β, 34,ββ,ββ,ββ,ββ);
INSERT INTO patient_data
(title
, language
, financial
, fname
, lname
, mname
, DOB
, street
, postal_code
, city
, state
, drivers_license
, ss
, occupation
, phone_home
, phone_biz
, phone_contact
, phone_cell
, status
, contact_relationship
, date
, sex
, referrer
, referrerID
, providerID
, email
, ethnoracial
, interpretter
, migrantseasonal
, family_size
, monthly_income
, homeless
, financial_review
, pubpid
, pid
, genericname1
, genericval1
, genericname2
, genericval2
) VALUES ( βMr.β, βenglishβ, ββ, βJillianβ, βMahoneyβ, ββ, β1968-08-11β, β444 North State Streetβ, β90204β, βSanta Anaβ, βCAβ, ββ, β222-11-1111β, ββ, β(808) 555-4444β, β(808) 555-3333β, β(808) 555-5555β, ββ, βmarriedβ, ββ, β2004-01-19 12:14:06β, βFemaleβ, βYnez Jonesβ, ββ, 4, ββ, ββ, ββ, ββ, ββ, ββ, ββ, β2021-01-01 00:00:00β, β35β, 35,ββ,ββ,ββ,ββ);
Thanks, Daniel. Yes, I do have access to phpMy Admin. And I am running this on XAMP. I am going to need some time to work through your instructions-thank you very much.