0

I have an SQL query that used to work without problem in an earlier version of MySQL, but I've now switched to using MySQL 8 and the query just won't work in that new version. I'm using the query from a PHP script. The query is as follows:

$query = "SELECT * FROM memo WHERE (memo_hidden = 0 OR memo_hidden IS null) AND memo_date='".$required_date."' ORDER BY programme_order, rank";

If I try using it, I just get an error message from MySQL that makes no sense at all. That error message is as follows:

"Invalid memo query: 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 '' at line 1"

If I change the query as follows, it works fine:

$query = "SELECT * FROM memo WHERE (memo_hidden = 0 OR memo_hidden IS null) AND memo_date='".$required_date."' ORDER BY programme_order";

However, I obviously want the query to order by both fields (programme_order & rank). I can obviously order the return database rows myself using PHP, but I'd prefer not to do that.

If anyone out there understands how to resolve this, please let me know. Massive thanks in advance for any help.

craig-c
  • 161
  • 2
  • 5
  • 2
    [Rank is a reserved word as of 8.0.2](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R). If you want to use it as a column name, you're going to need to surround it by backticks every time you reference it – aynber Nov 21 '22 at 18:16
  • please try **ORDER BY memo.programme_order, memo.rank – rsmdh Nov 21 '22 at 19:13
  • thank you aynber! As I'm sure you already know, changing the end of the query so that it has backticks around `rank` resolved the problem. – craig-c Nov 21 '22 at 19:16

0 Answers0