As previously announced via banners and our newsletters, support is no longer available through this platform.

For easier navigation, you can still click on "Submit a Ticket" here, choose the appropriate category, and you'll be redirected to the correct support channel for your plugin.

You can still access your previous tickets and browse public tickets, but please note that responding to tickets is no longer possible.

Paid customers: Please log in to your store account for support.

Pre-purchase questions: Use the support widget in the bottom-right corner of our websites:
https://wpamelia.com
https://wpdatatables.com
https://wpreportbuilder.com

Okay
  Public Ticket #1295752
MYSQL Query Interval
Closed

Comments

  • MattHadfield113 started the conversation
    September 7, 2017 at 9:45am

    Hello,

    I have a query that i am trying to run (See Below), however the query comes back with an error -

    Error

    unknown [expr_type] = reserved in "WHERE" [4]


    The query i am executing is -

    SELECT DATE(`created`) AS created_date, `Extension`, SUM(CASE WHEN `Call_type` = "Incoming" AND `Extension` != 2504 THEN 1 ELSE 0 END) AS CallsAnswered, SUM(CASE WHEN `Call_type` = "Incoming" AND `Extension` = 2504 AND `Dialed_digits` LIKE 'R%' THEN 1 ELSE 0 END) AS CallsRecieved FROM mp_wp_call_report_pstn WHERE `created` BETWEEN NOW() - INTERVAL 31 DAY AND NOW() GROUP BY DATE(`created`) ORDER BY `created` ASC LIMIT 0, 32

    Removing the "INTERVAL 31 DAY" resolves the issue and allows the query to proceed. However when processing this manually on MYSQL, the query works without issue and provides the relevant data.

    Is there a way you have to specify an interval or is this not supported in wpDataTables ?

    Regards,

    Matt

  •  1,896
    Miloš replied
    September 7, 2017 at 11:31am

    Hi Matt,
    Thank you for your purchase.

    Our recommendation when creating mysql tables is not to use functions like SUM, GROUP BY, ORDER BY etc.
    If you have some complicated query my suggestion is to make a VIEW in phpmyadmin or any other database management system and see if that query returns the desired result.
    If the result is good , use that query as an input when creating the tables with Add from data source option.

    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/

    wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables