Scaffolding bug when table named 'case'

[eluser]maesk[/eluser]
Hi all

I'm not sure if this is a bug, but I think so. I have a table in my DB called 'case'. When I load scaffolding for this table and want to create a new record I get the following error message:

An Error Was Encountered
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case LIMIT 1' at line 1
SELECT * FROM case LIMIT 1

This only happens when the table is called case. If I rename the table (e.g. to "case2" or any other name) then it works fine.

Edit: I just noticed that another MySQL error shows up when trying to insert a record with a field called "where". Probably these are just reserved words in MySQL then.
Sorry for posting as a possible CI bug.

[eluser]Glen Swinfield[/eluser]
You can use case - but the query would need to be SELECT * FROM `case` LIMIT 1. But it is best to avoid reserved words I find. And I don't think there is a way to make the scaffold escape the table name.

Same for `where`. Try to avoid these names because your finished SQL statements are going to be very tricky.

e.g. SELECT where from case where where in (1,2,3,4) !!!

[eluser]maesk[/eluser]
[quote author="Codepat" date="1185449563"]You can use case - but the query would need to be SELECT * FROM `case` LIMIT 1. But it is best to avoid reserved words I find. And I don't think there is a way to make the scaffold escape the table name.[/quote]

Yes, I didn't even find the place within CI where the SQL query is being constructed. What I don't understand: why is it ok to use most table names without quotes and others, such as 'case', only with quotes? Is it because 'case' is a reserved word in MySQL (cf. http://dev.mysql.com/doc/refman/5.1/en/r...words.html)?
In this case <-- :-) I think MySQL should indicate this in its error message instead of simply calling it a "syntax error".

[quote author="Codepat" date="1185449563"]Same for `where`. Try to avoid these names because your finished SQL statements are going to be very tricky.

e.g. SELECT where from case where where in (1,2,3,4) !!![/quote]

:-)
You're right, "where" is a very unfortunate name for a table field. I simply changed it to "location" now.

[eluser]Glen Swinfield[/eluser]

I think MySQL should indicate this in its error message instead of simply calling it a “syntax error”.

That would be nice wouldn't it :-). Unfortunatley it's all about context, mysql is essentially dumb in this respect, and to be fair when it comes across a keyword it can't then go and check where it is in the string to gauge the context then return syntax or other error. This would be quite a lot of overhead.

why is it ok to use most table names without quotes and others, such as ‘case’, only with quotes

Technically you should escape all field and table names with `example`, but if the name is not ambiguous MySQL just assumes it is a field or table name, unless it is escaped with 'example', in which case it assumes it is data.

[eluser]maesk[/eluser]
Thanks for your reply. This makes sense. Maybe a final question:

What are these strange quotation marks called that MySQL expects for table names (`example`) and how do you enter them on your keyboard? I could never figure that out and always copy paste them from somewhere. The single quotation marks don't work, only these strange ones.

[eluser]Glen Swinfield[/eluser]
It's called a 'grave accent' according to wikipedia. You will find it (probably) on the top left of your keyboard - perhaps at the beginning of the row of numbers.

I'm using a laptop so the button configuration may differ from yours.

[eluser]maesk[/eluser]
Thank you very much, Codepat! Now I understand. I know the grave accent from French (e.g. "voilà") and now I could find it on my keyboard too (it's on the top right of my German keyboard, next to backspace).

Cheers!

[eluser]Drarok[/eluser]
I'd like to add another voice to this (admittedly old) thread.
I've just come across the same sort of problem with a field named "key".

Should CI not automatically add grave accents to fields and tables?