How to check for which version of MySQL is valid my query?

Question

I'm trying to do some queries which in my local XAMPP is working fine, but, when I try on my server, the query shows empty.

I use phpinfo() to check MySQL version, on my local is 7.4 and on my server is 5.7. The thing is my SQL query is working on server, is not showing any errors, but the response is empty, this is not happening on local.

Query:

SELECT *, JSON_EXTRACT(`settings`, '$.exp') AS `exp`, JSON_EXTRACT(`settings`, '$.status') AS `status` FROM `factory_invoice` WHERE `invoice` REGEXP '^[0-9]{4}[A-Z]{3}$' AND( JSON_CONTAINS(`settings`, '1', '$.type') OR JSON_CONTAINS(`settings`, '2', '$.type') ) AND( JSON_EXTRACT(`settings`, '$.status') = '0' OR JSON_EXTRACT(`settings`, '$.status') = '1' ) AND( JSON_EXTRACT(`settings`, '$.exp') >= '1609153609' AND JSON_EXTRACT(`settings`, '$.exp') <= '1620770400' ) ORDER BY `invoice` ASC

Any idea if is something from the server side? Or maybe from my SQL query?

I thing the main problem is here.........JSON_EXTRACT(settings, '$.exp') >= '1609153609' AND JSON_EXTRACT(settings, '$.exp') <= '1620770400' ) — Asif Uz Zaman
– Asif Uz Zaman, Commented May 18, 2021 at 15:26
You want say... That sql work on xampp server... But not working your online server.... — Asif Uz Zaman
– Asif Uz Zaman, Commented May 18, 2021 at 15:30
@AsifUzZaman yes, that's what I mean, is working fine on XAMPP server but not on my online server. But not exactly, because on my online server, when I try sql in phpMyAdmin, is not showing any error, just return empty. — El Tito Barte
– El Tito Barte, Commented May 18, 2021 at 15:34
Ok... Give me 30 mint... I waill give my server address then you can test there.. — Asif Uz Zaman
– Asif Uz Zaman, Commented May 18, 2021 at 15:43
Thanks @AsifUzZaman, I already solved, I will post now solution. — El Tito Barte
– El Tito Barte, Commented May 18, 2021 at 16:03

El Tito Barte · Accepted Answer · 2021-05-18 16:05:20Z

I already solved. The problem was in JSON_EXTRACT(settings, '$.exp') >= '1244.... Thanks to @AsifUzZaman

I just clean a bit the code and I use BETWEEN method.

SELECT * FROM `factory_invoice` WHERE `invoice` REGEXP '^[0-9]{4}[A-Z]{3}$' AND JSON_EXTRACT(`settings`, '$.type') IN(1, 2) AND JSON_EXTRACT(`settings`, '$.status') IN(0, 1) AND JSON_EXTRACT(`settings`, '$.exp') BETWEEN '1609110000' AND '1620770400' ORDER BY `invoice` ASC

Collectives™ on Stack Overflow

How to check for which version of MySQL is valid my query?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related