Message Problem

OpenEMR Development
1

arnabnaha wrote on Sunday, November 27, 2011:

I get the following error whenever i try to send a message or i press any button on the message screen like the “send message”  and “cancel”

ERROR: query failed: SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, IF(pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname, IF(pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname, patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username) JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ? order by limit 0, 25

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘limit 0, 25’ at line 7

I tried sending a message from the patient portal (offsite) to the openemr. it was received nicely. I opened it and it showed it content. I wrote my message in the box and when i clicked “send message”, the error showed. But to my utter surprise, the message was delivered to the patient in the portal. Please help!!

2

arnabnaha wrote on Monday, November 28, 2011:

Any help on this???

3

juggernautsei wrote on Monday, November 28, 2011:

What version are you using? Also, have you setup the SMTP information in Notifications under Globals under administration?

Administration > Globals> Notifications

Sherwin
openmedpractice.com

4

arnabnaha wrote on Monday, November 28, 2011:

I am on 4.1 devlopment version. SMTP is working fine…and have also added it …I have a Mercury mail server setup too…

5

yehster wrote on Monday, November 28, 2011:

The source of the error is from main\messages\messages.php
The $sortby and $sortorder variables are empty strings instead of proper values in your situation.
around line 392 code is as follows

        $sql = "SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, 
          IF(pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
          IF(pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
          patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
          FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username) 
          JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != 'Done' 
          AND pnotes.deleted != '1' AND pnotes.assigned_to LIKE ?".
          " order by ".add_escape_custom($sortby)." ".add_escape_custom($sortorder).
          " limit ".add_escape_custom($begin).", ".add_escape_custom($listnumber);

My guess is that the http request is not being properly formed and is passing in empty strings for sortby and sortorder.
-Kevin Yeh
kevin.y@integralemr.com

6

arnabnaha wrote on Monday, November 28, 2011:

So what should i do now…any suggestions??

7

mdsupport wrote on Monday, November 28, 2011:

As a workaround, try modifying the SQL identified by Kevin as :

" order by IFNULL(".add_escape_custom($sortby).",pnotes.id) ".add_escape_custom($sortorder).

It should work if add_escape_custom returns NULL.  If it returns ‘’, you will need to use IF(expr1,expr2,expr3) construct.

Correct solution will be to initialize $sortby & $sortorder to some default values earlier in the logic. 

8

mdsupport wrote on Monday, November 28, 2011:

The code block got distorted in the post. 

." ".

should be replaced by

.",pnotes.id) ".
9

yehster wrote on Monday, November 28, 2011:

https://github.com/yehster/openemr/commit/78ac32074803acd710d21251ed8f06bec115f77a
https://github.com/yehster/openemr/tree/messageBug
Bug fix to handle empty strings in request parameters for sortBy and sortOrder.

10

arnabnaha wrote on Tuesday, November 29, 2011:

Thanks all…Thank you Kevin for providing the code…it now works like a charm without those error messages…

11

bradymiller wrote on Tuesday, November 29, 2011:

Hi,
Committed yehster’s fix to sourceforge and will include it in the next 4.1.0 patch.
thanks,
-brady