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

A place for users and developers of the Xataface to discuss and receive support.

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

Postby 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      
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby 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.
auphi
 
Posts: 20
Joined: Sun Oct 21, 2012 7:39 pm

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

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

Sounds good - I'll give that a whirl and see where I end up. :D
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

Postby 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? :D

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.
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm

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

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

Steve
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

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

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

Wow, that was it! :D

I owe you a coffee or something. :mrgreen:
mike.jones
 
Posts: 12
Joined: Mon Feb 11, 2013 9:17 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 2 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved