Urgent SQL Query Help needed

Day-to-Day Users
1

Without PhpMyAdmin, I’m struggling with Queries. We had a provider give notice and we need to send letters to all their patients as soon as possible but the Batch com page doesn’t have an option in the dropdown to sort and generate a .csv file by provider, what would I need to do to add it? or would anyone be willing to write a query that produces a zip code sorted file we would need for a mail merge?

2

Why not just install it outside EMR?

1 Like
3

Without details on your working environment, I’d start with something like this;

SELECT fname,lname,street,city,state,postal_code from patient_data WHERE providerID = ‘#’ ORDER BY postal_code INTO OUTFILE ‘/var/lib/mysql-files/pat_list_1.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’;`

Input the providerID in question to replace the # , and you might need to adjust the file output path.
Beware that if your SQL user does not have the FILE privilege, and your MySQL/MariaDB daemon is not configured to allow direct outfile writing to disk, then you’ll need to make adjustments before it will work. But, that will give you a list for the provider sorted by ZIP code, and you can import the CSV file into Calc or Excel and make further edits as necessary. Note that you’ll need to enclose the field & table names above with ` characters, the message board is erasing them in the example query string.

As MD Support said, if you’re doing any kind of work or maintenance with your DB, it’s well worth it to have PHPMyAdmin installed, even if you leave it disabled to reduce security risks and only make it available when needed for tasks such as this.

1 Like
4

There are several options. You can install adminer. I would go to their website, download the file.

Change the name from adminer-4.8.1.php to adminer.php, install it in the root openemr directory, then you can access it by going to openemr/adminer.php.

The other option is to install phpmyadmin outside of Openemr.

If you are looking for a report that you can access OpenEMR from the OpenEMR menu, that is something I can help you with. You will be able to download a pdf or a csv file.

2 Likes
5

False alarm.
My apologies to all who responded to my urgent request with suggestions and help.
I looked everywhere I thought would produce what I was looking for but didn’t find it.

After running into login problems with Adminer, (denies p/w logins to localhost) I went back into OpenEMR and looked again. Much to my chagrin (and relief), the system has the capability to produce exactly what I needed under Reports, Clients, List.

I’ll troubleshoot the Adminer login issue and put together a “how-to” in the near future.

Thanks everyone.

1 Like
6

One suggestion I’d make, regardless of the admin tools that you end up choosing to implement, (PMA, Adminer, etc).
Install them separately from your OpenEMR site, and require dedicated credentials to access those resources, maybe even disable them or make them unavailable when not actually in use.
This will reduce the attack surface and potential security risks, as any useful administrative tool is also a target for hackers.

7

The advent of AI has made this task so much easier. I used Grok to create a query that I can cut & paste into the SQL Query window in Adminer. For example;

create a maria database query in openemr for fname, lname, email of patients seen in the last 10 days

Result;
Let me help you create a MariaDB query for OpenEMR to retrieve the first name, last name, and email of patients seen in the last 10 days. In OpenEMR, patient demographic information is stored in the patient_data table, while encounter dates are typically stored in the form_encounter table.

Here’s the query:

SELECT DISTINCT 
    pd.fname AS 'First Name',
    pd.lname AS 'Last Name',
    pd.email AS 'Email'
FROM 
    patient_data pd
INNER JOIN 
    form_encounter fe ON pd.pid = fe.pid
WHERE 
    fe.date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
    AND fe.date <= CURDATE()
ORDER BY 
    pd.lname, pd.fname;

This query:

  1. Uses patient_data (pd) for patient information and form_encounter (fe) for visit dates
  2. Joins the tables using pid (patient ID)
  3. Filters for encounters within the last 10 days using DATE_SUB
  4. Uses DISTINCT to avoid duplicate patient entries if they had multiple visits
  5. Orders results alphabetically by last name and first name
  6. Returns results up to today (February 22, 2025, since that’s the current date)

Note:

  • The date field in form_encounter is typically in datetime format
  • If you need to include patients who might not have encounter records but were recently added, you might need to modify the JOIN type or add additional conditions
  • The exact table structure might vary slightly depending on your OpenEMR version

Would you like me to adjust this query in any way or add additional fields?