Dealing with MySQL keywords as column names

June 23, 2020, 08:43:43 AM

I have "Key" as the column name (and primary key) in several of my tables. If I try to generate a simple List View, I get:

Code: [Select]

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'Key,

Even if I deselect the field (as I don't need it shown or searched by), it is automatically included.
This can be avoided by fully qualifying the field, as in table.Key, or escaping it, as in `Key`. PHPRad should do that by default.
Meanwhile, a workaround I found was to use Join Tables, which seems to result in proper MySQL statements.

A similar error occurs when joining several tables that have their primary key named the same (e.g., "ID") in a List View and leaving Inline Edit enabled:

Code: [Select]

Notice: Undefined index: ID in \list.php on line 201

The column name needs to be fully qualified in order to avoid confusing table1.ID with table2.ID.