Problem with query using single quotes in chart widget

Just guessing. D
Does it need the ending semi-colon? ‘;’

Or… try WHERE YEAR(Period) = 2020;

UPDATE: YEAR() will only work if Period looks like a date.

• This reply was modified 2 years, 11 months ago by charlesgodwin.
• This reply was modified 2 years, 11 months ago by charlesgodwin.

Hi @iekdepagter,

The copy query function seems to work correct with quotes. It looks like your last quote is a backtick. Can you check that please?

Thanks,
Peter

Hi Peter,

The query (with single quotes around values, backticks around table and field names):
SELECT Period,TotcrudeIN FROM jodi_NLrefineries
WHERE Period>’2019-12′ AND Period<‘2021-01’
The (correct) result listing:
Period TotcrudeIN
2020-01 5628
2020-02 4995
2020-03 5159
2020-04 4979
2020-05 4152
2020-06 3540
2020-07 3992
2020-08 4444
2020-09 4449
2020-10 4748
2020-11 4823
2020-12 5065

The error message when the query is copied into the Chart Widget (I put spaces after ‘near’ to prevent ‘translation’ of & # 0 3 9 ; to ‘):

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 ‘ ; & # 0 3 9 ; 2 0 2 1 – 0 1 & # 0 3 9 ;
“,”,”,”,) resultset limit 0’ at line 2

Note: & # 0 3 9 ; is a representation of a single quote
I don’t understand the last line of the error message.

The following query:
SELECT Period,TotcrudeIN FROM jodi_NLrefineries
WHERE SUBSTR(Period,1,4)=’2020′
gives the same listing, no syntax error in the Chart Widget
and a nice chart is produced.

The same query without quotes around 2020 also works fine.
Why should ‘2020’ or 2020 work correctly and ‘2020-10’ not?

Anyway, I want to select a range of monthly values, not select by year.

Another question: is there a way to show a full range of values (e.g. 0..6000) on the Y-axis on a column chart in stead of only a part of the range (e.g. 3000…6000, minimum to maximum Y-values)?

• This reply was modified 2 years, 11 months ago by iekdepagter.
• This reply was modified 2 years, 11 months ago by iekdepagter.

Hi Iek,

What is the data type of the Period column? Is this a date column? Or varchar?

How do you copy the query into the chart query field? I used different approches, but they all work for me. What OS and browser are you using?

Would a group by solve your last question?

Thanks,
Peter

Hi Peter,

The problem seems to be that the string ”,”,”,”,) was somehow added to the end of the MySQL-query. That produced an error message as soon as the Dashboards&Widgets icon was clicked.

This all happened repeatedly within a complex WordPress-installatie with about 40 plugins, which probably was corrupted in some way.

When I made a clean WordPress installation with only the WPDA plugin everything worked as expected. WPDA Premium is an excellent plugin with everything I need!

Thanks, Peter, for your support,
Iek

Thank you for reporting back Iek! I added your chart feature request to my to do list and will try to add it this or next month.

Best regards,
Peter