-2

I created my database tables and bake as usual with CakePHP 3 but when I point my browser to some link it give me this errors:

Error: SQLSTATE[42000]: Syntax error or access violation: 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 'AS Users__contact person, Users.Phoneno AS Users__Phoneno, Users.email AS `U' at line 1

If you are using SQL keywords as table column names, you can enable identifier quoting for your database connection in config/app.php.

SQL Query:

SELECT Users.id AS `Users__id`, 
        Users.name AS `Users__name`, 
        Users.address AS `Users__address`, 
        Users.contact person AS `Users__contact person`, 
        Users.Phoneno AS `Users__Phoneno`, 
        Users.email AS `Users__email` 
FROM users Users 
LIMIT 20 OFFSET 0

screenshot of the error page for users table

Community
  • 1
  • 1
mopo
  • 1
  • 2
  • 6
  • 2
    Do you really have a space in a column name i.e. `Users.contact person` or should that be an underscore? If its really a space then use `Users.\`contact person\`` i.e. back ticks around the syntactical error – RiggsFolly Feb 15 '18 at 23:02
  • @RiggsFolly nice job formatting that inline code. I can never get it right :) – Phil Feb 15 '18 at 23:04
  • @Phil yea its a beast. You have to use an escape char i.e. "\" on the backticks which you put inside a set of backticks. It can take me ages to get it right sometimes! – RiggsFolly Feb 15 '18 at 23:05
  • 2
    I also have to ask why you would want to create alias names for these column names that have 2 underscores in them? Do you like to make life difficult for yourself? – RiggsFolly Feb 15 '18 at 23:08
  • The __ is a Cake thing where it is the model__column. This is the way to write native queries and have them return data in the same format as the model. – ryantxr Feb 15 '18 at 23:59
  • the problem is the space in the column name. You can simply avoid using spaces or - if you have to - use `quoteidentifiers => true` in the database settings. – arilia Feb 16 '18 at 11:23
  • Possible duplicate of [How to safely use reserved SQL names?](https://stackoverflow.com/questions/27854333/how-to-safely-use-reserved-sql-names) – arilia Feb 16 '18 at 11:23
  • thanks guys and @RiggsFolly nice to have observerd that i have been looking for the reason all day but thank to you the error was from the space in the column contact person instead of contactperson – mopo Feb 17 '18 at 11:13

1 Answers1

0

Thanks guys and @RiggsFolly nice to have observerd that in the SQL

The error was from the space in the column contact person instead of contactperson in the SQL

mopo
  • 1
  • 2
  • 6