Unable to Query the User table from another tables valuelist

[doyledennis]

I have added authentication to my Xataface site as instructed in the tutorials. Authentication, permissions and filters all work great but when I attempted to add a foreign key to the User table Xataface can not parse the selct statement properly from a valuelist. If I do not try to use a select widget and value list everything is fine.

Select Statement to populate the dropdown (in the valuelists.ini):
__sql__="SELECT ID, Name FROM User ORDER BY Name"

Note: If I replace the "User" table name with a different table name, the select statement works just fine and the dropdown is populated.

Is "User" some sort of key word within the Xataface SQL statement parsing functionality?

Current Code:

fields.ini
[User_ID]
widget:label = "User"
widget:description = "Select the User."
widget:type=select
vocabulary=SomeUsers
order=13

valuelists.ini
[SomeUsers]
__sql__="SELECT ID, Name FROM User ORDER BY Name"

Xataface Error
Fatal error: Failed parsing SQL query on select: SELECT ID, Name FROM User ORDER BY ID . The Error was Parse error: Unexpected clause on line 1 SELECT ID, Name FROM User ORDER BY ID ^ found: "User" in /Applications/XAMPP/xamppfiles/htdocs/xataface/lib/SQL/Parser.php on line 1755

[shannah]

It's possible that one of your column names or table name is a reserved MySQL word. Try wrapping them in backticks to clear up the confusion.

e.g.

Code: Select all

__sql__="SELECT `ID`, `Name` FROM `User` ORDER BY `Name`"


[doyledennis]

Steve, Thank You for the quick response!

You were correct wrapping User the way you suggested did the trick.

Interesting thing is that the query was successful using mySQL work bench. Either way, thank you for the response and great job on this application!

[shannah]

Xataface has its own SQL parser that it uses to gather information about the queries. It isn't perfect, but it gets the job done most of the time.. and it's always improving.