0

I'm creating raw MySQL queries to insert into my DB using the Laravel 'DB' facade. I've gotten this same type of query to work with another database table, but it's throwing errors on this specific one. 

 1   PDOException::("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 'group,originating_attorney,practice_area,responsible_attorney,statute_of_limitat' at line 1")
  /home/vagrant/Code/proj/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

2   PDO::prepare("INSERT INTO firm_matters(id,etag,display_number,custom_number,description,status,location,client_reference,billable,maildrop_address,billing_method,open_date,close_date,pending_date,client,contingency_fee,custom_rate,group,originating_attorney,practice_area,responsible_attorney,statute_of_limitations,user,account_balances,custom_field_vals,custom_field_set_associations,created_at,updated_at) VALUES (...)

I've checked the reserved keywords for MySQL and I don't think I'm violating any rules. Could someone more versed in MySQL check to see if my query is correct.

Improve this question
1
  • 2
    please consider formatting your questions in such a way that the code can be read without scrolling hundreds of characters back and forth. Commented Aug 9, 2018 at 21:35

3 Answers 3

Reset to default
3

I've checked the reserved keywords for MySQL

No you didn't

 syntax to use near 'group,originating
                     ^^^^^ here

GROUP is reserved word https://dev.mysql.com/doc/refman/5.7/en/keywords.html

simply don't create "raw MySQL queries" and you'll be fine next time. you are smart enough to use framework yet you use "raw MySQL queries". just don't do it.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I'm upset at myself for missing that keyword after poring over the documentation. I agree I hate the idea of using raw SQL queries but the framework doesn't support updating or creating mass entries. Only one or the other. It's too taxing on the system to run a query per entry when I'm dealing with over 30k items.
@OscarAGarcia It's too taxing on the system to run a query per entry when I'm dealing with over 30k items. framework doesn't support updating or creating mass entries you are wrong! unless you use remote mysql connection just put your loop with inserts into transaction laravel.com/docs/5.6/database#database-transactions
2

check the manual that corresponds to your MySQL server version for the right syntax to use near 'group, ...

Here's a tip about MySQL syntax errors: When the SQL parser encounters a word in your query that confuses it, the error message shows you exactly where it got confused. In this case, the word group is the one that the parser wasn't expecting in that place in the query.

Others have answered that group is a reserved word in MySQL. You can use reserved words as identifiers (table names, column names, etc.) if you put them in back-ticks to delimit them and make it clear they are identifiers, not SQL keywords.

INSERT INTO firm_matters(id, etag, display_number, custom_number, description, 
  status, location, client_reference, billable, maildrop_address, billing_method,
  open_date, close_date, pending_date, client, contingency_fee, custom_rate,
  `group`,
  originating_attorney, practice_area,responsible_attorney, statute_of_limitations, 
  user, account_balances, custom_field_vals, custom_field_set_associations,
  created_at, updated_at) VALUES (...)

The recommendation from @Peter to use a framework is because many frameworks automatically delimit ALL identifiers in back-ticks, just in case they are reserved words.

But you can resolve this error yourself without adopting a complex framework. Either delimit your identifiers, at least those that match MySQL reserved words, or else don't use reserved words as identifiers in the first place.

There's nothing wrong with writing SQL directly.

Improve this answer

3 Comments

There's nothing wrong with writing SQL directly until you forget about another reserved word :) Oscar is using framework already so there is no point for_not_ using framework. We use frameworks exactly for this stuff. If you choose not to use framework even with milion on rows you'd get minimal performance boost.
I also want to add that's fine to use raw sql in edge cases when you really really really want to speed up things (laravel can do that anyway :)). In any other case we should use framework
With all due respect, that's a matter of opinion.
-1

depends on your MySQL version, at least group is reserved keyword

https://dev.mysql.com/doc/refman/5.7/en/keywords.html#keywords-5-7-detailed-G

when you write a raw query, change all the 

column to `column`
Improve this answer

2 Comments

OP is using laravel stop encouraging people to write SQL while using full blown framework. you are right about reserved keyword I am sure you didnt missed my answer
so how can you write this query in Laravel if MySQL 'ONLY_FULL_GROUP_BY' is enabled, selectRaw('MIN(id) id, ANY_VALUE(thread_type) thread_type, ANY_VALUE(title) title,...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.