Unknown column (when it is there!).

matbennett

This has got me pulling my last remaining hair out today...

Has anyone got any idea why this:

SELECT * 
FROM FJAFFdepartments
ORDER BY FJAFFdepartments.parent_id

would result in the error Unknown column 'FJAFFdepartments.categories_id' in 'field list'

Where as, this:

SELECT * 
FROM FJAFFdepartments
ORDER BY 'FJAFFdepartments.parent_id'

(note the ' )

.. works without error.

I could go and add the quotes throughout my pages, but I'd rather understand what the problem is.

Sxooter

My guess is that the second query will result in no more ordering than leaving it off.

YappyDog

What database are you using?

Without the quotes, some databases are case-sensitive. So, a column named Parent_ID in the table definition, would not be the same as one defined as parent_id.

Sxooter

Yeah, but single quotes (i.e. ticks) go around literals. This makes the query the equivalent of:

select * from table order by 'frobozz';

i.e. every row will have the text frobozz as an order by column, i.e. no actual ordering will take place.

Now,

select * from table order by "table.column"

is another issue (or with the non-standard ` quoting MySQL insists on using🙂

select * from table order by table.column

matbennett

I am using mySQL.

My query wasn't really "why does the second example work?", but more "Why am I getting an error on the first one?"

Simply put, I just cannot reference some of the fields, and have no idea why. For example, the following results in an error, even though that field exists.

SELECT FJAFFdepartments.categories_id

Here's the info from phpmyadmin on that table just to demonstrate that I am not spelling the name wrong or anything:

FJAFFdepartments
Field Type Attributes Null Default Extra
categories_id int(11) Yes 0

categories_name varchar(32) Yes NULL

categories_image varchar(64) Yes NULL

parent_id int(11) Yes 0

categories_sort int(3) Yes NULL

Indexes : Keyname Type Cardinality Field
categories_id UNIQUE 65 categories_id

I did wonder whether this was related to some reserved term, but I have been renaming and testing and still had the problem.

Sxooter

I don't know mysql well enough to say, but maybe you're having a case folding problem here.

recreate your table in a test database, with everything in lower case, and see if it works there.

Have you tried quoting the table and field?

SELECT FJAFFdepartments.categories_id

matbennett

No idea what caused this.

Ended up creating all new table with new names & porting the data over. After a liberal use of search and replace everything is fine now.

Thanks for the suggestions though.

Sxooter

You may have had some kind of non-printing character stuck in your table name or something weird. huh, well glad you got it working.