Value-lists with SQL query - syntax on my end…

Archived from the Xataface Users forum.

mike.jones — Fri Feb 15, 2013 12:13 pm

Application URL:
www . swmobridgecontest.org/cms/contest

Description of problem:
I am trying link a table containing the teachers and schools into a another table which contains the student/contestant data.

The student table is in 2013contestants

The teacher table is 2013teachers

The teacher and school records could be tied to multiple student records, but the student record can only have one teacher and school.

I _think_ everything is correct, but I’m still learning as I go with both xataface and MySQL.

I keep getting the following error:

Code: Select all: Fatal error: Failed parsing SQL query on select: SELECT TeacherID, TeacherName FROM 2013teachers ORDER BY TeacherName . The Error was Parse error: Unexpected clause on line 1 SELECT TeacherID, TeacherName FROM 2013teachers ORDER BY TeacherName ^ found: "2" in /home/swmobridge/swmobridgecontest.org/cms/contest/dataface/lib/SQL/Parser.php on line 1765

Fields.ini file in the 2013contestants table folder

Code: Select all: `[Grade]
widget:type = select
vocabulary = Grade
[TeacherID]
widget:type = select
vocabulary = Teacher`

valuelists.ini file in the 2013contestants table folder

Code: Select all: `[Grade]
FIRST= 1st
SECOND = 2nd
THIRD = 3rd
FOURTH = 4th
FIFTH = 5th
SIXTH = 6th
SEVENTH = 7th
EIGHTH = 8th
NINTH = 9th
TENTH = 10th
ELEVENTH = 11th
TWELTH = 12th
OTHER = Other
[Teacher]
sql = “SELECT TeacherID, TeacherName FROM 2013teachers ORDER BY TeacherName”`

Table structure cut-n-paste from phpmyadmin

Code: Select all

`2013teachers

Field Type Null Default Comments
TeacherID int(11) No
TeacherName varchar(32) Yes NULL
School varchar(32) Yes NULL
Indexes:

Keyname Type Unique Packed Field Cardinality Collation Null Comment
PRIMARY BTREE Yes No TeacherID 14 A`

Code: Select all

`2013contestants

Field Type Null Default Comments
id int(11) No
date_time datetime Yes NULL
FirstName varchar(32) No Contestants First Name
LastName varchar(32) No Contestants Last Name
Grade varchar(127) Yes NULL
TeacherID int(11) Yes NULL
BridgeNumber int(5) Yes NULL
Disqualified varchar(11) Yes
Comment varchar(25) Yes NULL
Indexes:

Keyname Type Unique Packed Field Cardinality Collation Null Comment
PRIMARY BTREE Yes No id 5 A`

auphi — Fri Feb 15, 2013 2:30 pm

Hmm… I don’t see anything wrong either. Maybe someone else will catch the error though…

In the meantime try simplifying your sql code and then add things back in gradually to see where it breaks, i.e. try:

__sql__ = “SELECT TeacherID FROM 2013teachers”

if that works, then

__sql__ = “SELECT TeacherID, TeacherName FROM 2013teachers”

then

__sql__ = “SELECT TeacherID, TeacherName FROM 2013teachers ORDER BY TeacherName”

and maybe that will help you at least identify where the problem is.

mike.jones — Fri Feb 15, 2013 3:51 pm

Sounds good - I’ll give that a whirl and see where I end up.

mike.jones — Mon Feb 18, 2013 7:01 am

No joy - tried various combinations, but alas, nothing seemed to work. Will dig into the PHP source next, I guess. Way over my head, but that’s how your learn, right?

FWIW, the SQL query worked w/o any errors in phpMYadmin - so there seems to be something incorrect with my fields.ini and valuelists.ini files…

http://www.swmobridgecontest.org/cms/contest/

After some more massaging, the teacher names will show up in the TeacherID field in the 2013contestants table - but whenever I try to add a new records in the 2013contestants table, I get the PHP/SQL error described above.

shannah — Tue Feb 19, 2013 1:41 pm

The SQL parser looks like it’s choking on the table name “2013teachers”. This is likely because it begins with a number so the parser thinks it is just a number. You can help out the parser by using backticks “' around the table name so that it knows you want it to be an identifier. E.g. SELECT TeacherID FROM2013teachers`

Steve

mike.jones — Tue Feb 19, 2013 6:36 pm

Wow, that was it!

I owe you a coffee or something.