6.1 SQL query error - advance directives on MariaDB

Situation
This is when in patient dashboard and attempting to the click the Advance Directives edit button on the right of the patient window, the popup contains the below SQL error.
@sjpadgett is this one you can help with? :slight_smile:

Query Error

ERROR: query failed: SELECT documents.date, documents.id FROM documents INNER JOIN categories_to_documents ON categories_to_documents.document_id=documents.id WHERE categories_to_documents.category_id=? AND documents.foreign_id=? AND documents.deleted = 0ORDER BY documents.date DESC

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘BY documents.date DESC’ at line 1

/var/www/interface/patient_file/summary/advancedirectives.php at 123:sqlStatement

OpenEMR Version
6.1
MariaDB Version: 10.5.15

Table layout:

hi @madmax , add a space at the end of openemr/advancedirectives.php at master · openemr/openemr · GitHub

 "AND documents.foreign_id=? AND documents.deleted = 0 " .
1 Like

No change, same error. I dont see any space being added in the error outputted either, despite clearing the browser cache etc.

Is it possible this is being cached elsewhere? I’m running on a local test server but the query does not appear to be getting updated. Produces the same error each time.

don’t think it’s cached

thanks for the bug report btw, have added the fix

SELECT documents.date, documents.id FROM documents INNER JOIN categories_to_documents ON categories_to_documents.document_id=documents.id WHERE categories_to_documents.category_id=0 AND documents.foreign_id=0 AND documents.deleted = 0 ORDER BY documents.date DESC

That works in sqlyog, i changed the ? to 0 and I got back no results.
MariaDB is throwing this error back at SQLyog when unchanged:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘? AND documents.foreign_id=? AND documents.deleted = 0 ORDER BY documents.dat…’ at line 1

can you paste a screenshot of where the advanced directives is?

You mean this?

oh, there’s a global, thanks

that date looks odd

First post also has the column layout from SQLyog point of view at least, if that helps.

it’s the syntax like you said. fix space then show the error.

With the space added the error output remains the same for me? Just cleared browser to be double sure.

Changing the 0 to a 1 or any number, still comes back as 0 too, is that being set elsewhere?


the space is not taking. are you sure it’s added in code and the right place? thats the issue and is clearly shown in error.

Not sure if something had a lock on the file, had to edit locally! But the space has taken now.

@stephenwaite fix for the space worked and has fixed the issue. Thanks again!

Date format shown does still look weird. I guess thats a minor thing, least its working now :slight_smile:

3 Likes