CODEWITHSUNDEEP
mysqlcakephp-3.0
Bayezid Alam
Bayezid Alam

Reputation: 380

CakePHP 3.0: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

I faced saving data to database using Form in CakePHP 3.0.

//add.ctp
<div>
    <?= $this->Form->create($deposit) ?>
    <fieldset>
        <legend><?= __('Add') ?></legend>
        <?php
            echo $this->Form->input('date');
            echo $this->Form->input('profile_id', ['options' => $profiles]);
            echo $this->Form->input('amnt');
            echo $this->Form->input('desc');
            echo $this->Form->input('user_id', ['options' => $users]);
        ?>
    </fieldset>
    <?= $this->Form->button(__('Submit')) ?>
    <?= $this->Form->end() ?>
</div>

Here is my add function

public function add()
{
    $deposit = $this->Deposits->newEntity();
    if ($this->request->is('post')) {
        $deposit = $this->Deposits->patchEntity($deposit, $this->request->data);
        if ($this->Deposits->save($deposit)) {
            $this->Flash->success(__('The member deposit has been saved.'));
            return $this->redirect(['action' => 'index']);
        } else {
            $this->Flash->error(__('The member deposit could not be saved. Please, try again.'));
        }
    }

    $profiles = $this->Deposits->Profiles->find('list', ['limit' => 200]);
    $users = $this->Deposits->Users->find('list', ['limit' => 200]);
    $this->set(compact('deposit', 'profiles', 'users'));
}

When i submitted the the Form i found below Database syntex Error

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 'desc, user_id, created, modified) VALUES ('2015-06-06', 7, '3211', 'some text', 1,' at line 1

And SQL Query was showing:

INSERT INTO member_deposits (date, profile_id, amnt, desc, user_id, created, modified) VALUES (:c0, :c1, :c2, :c3, :c4, :c5, :c6)

I spent plenty of time resolving the issue by Googling and from Similar Post, no luck, but after spending a day i found to get resolve simply configuring quoteIdentifiers to true.

quoteIdentifiers is by default set to false at config/app.php under Datasources of your cake project.

Upvotes: 1

Views: 2963

Answers (1)

crafter
crafter

Reputation: 6296

One of your columns is using a column name that is reserved by MySQL.

dev.mysql.com/doc/refman/5.0/en/reserved-words.html

As you can see, DESC is reserved.

If you can find a way to change the query to use escaped column name specifies, mysql will tolerate this. for example

INSERT INTO `member_deposits` (
    `date`, `profile_id`, `amnt`,
    `desc`, `user_id`, `created`, `modified`) 
VALUES (:c0, :c1, :c2, :c3, :c4, :c5, :c6)

Alternatively, change the column name to something that does not violate the mysql reserved word rule.

Upvotes: 5

Related Questions