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

[mike.jones]

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]

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]

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

[mike.jones]

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]

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 FROM `2013teachers`

Steve

[mike.jones]

Wow, that was it!

I owe you a coffee or something.