SQL Query

OpenEMR Development
1

arnabnaha wrote on Tuesday, January 26, 2016:

Hello,

I have a database with two tables - “appointments” and “oldpt_appointments”. The table “appointments” have fieldnames like - pt_name, mnum, email, appt_id, appt_date which are same as the field names in oldpt_appointments table, the other fieldnames are different.

I am trying to build a query which will fetch the informations/data contained in these common fields in a table fashion.

I am using this query, but it seems to fetch data only from a single table.

$sql=“SELECT * FROM appointments INNER JOIN oldpt_appointments WHERE oldpt_appointments.appt_date = ‘$search’”;

can anybody please help what should be the appropriate query to display data from both the tables with the common fields as stated above and I will be searching/querying the database with the appointment date…which is appt_date in both the tables.

Thanks and Regards,
Dr. Arnab Naha

2

arnabnaha wrote on Tuesday, January 26, 2016:

this is the php file which has the full code

3

aaricpittman wrote on Tuesday, January 26, 2016:

Hey Arnab,

What is the relationship between your “appointments” table and your “oldpt_appointments” table?

Aaric

4

arnabnaha wrote on Tuesday, January 26, 2016:

I need to have two table datas to show up for a particular date. Suppose i choose 26th jan 2016 and it should show me all the appointments from both the tables which are on 26th.

The field names are common for a few fields like the patient name, mobile, email but data inside is different. The relationship as such is the appointment date which would be same in both the tables for the query

5

aaricpittman wrote on Tuesday, January 26, 2016:

You are going to have to make a separate query for each table. I’ve attached a modified copy of your php that should get you close.

6

aaricpittman wrote on Tuesday, January 26, 2016:

Sorry, I had some unsaved changes in that file. Here is the correct file.

7

arnabnaha wrote on Tuesday, January 26, 2016:

Hi Aaric

Thanks a lot for the help. I ran your script and it gave me no data. is there anything i need to do on it?? Please see the attachment

8

arnabnaha wrote on Tuesday, January 26, 2016:

This is my database schema attached below. If it gives you any help

9

aaricpittman wrote on Tuesday, January 26, 2016:

Thanks. Could you post an example of the format of the select_date parameter?

10

arnabnaha wrote on Tuesday, January 26, 2016:

sure…this is the form php file which connects with the executing script. See attached

11

aaricpittman wrote on Tuesday, January 26, 2016:

Thanks. One question in looking at that. Will this report be going back into the last century? Or is it only going to be as of at least 2000?

12

arnabnaha wrote on Tuesday, January 26, 2016:

Its going only to be atleast a few years :slight_smile: I will fix the datepicker for sure…my bad though

13

aaricpittman wrote on Tuesday, January 26, 2016:

I got it working on my computer. I’ve attached two copies. One, “total_appointment_bydate.old.php”, uses the mysql methods you were already using which are deprecated and the other, “total_appointment_bydate.new.php”, use newer method of accessing mysql in php.

Also, if you update the date picker format to use a four digit year you will need to update the DateTime::createFromFormat to DateTime::createFromFormat(‘Y/m/d’, $_POST[‘select_date’]);

14

arnabnaha wrote on Tuesday, January 26, 2016:

Thanks you so so much…That was huge help for sure…Its working like a charm now…I used the old one, the “->” was giving me a fatal error, so removed it and it started working smooth. Thnaks for all the help Aaric. Thank you very very much.