Daniel Daniel - 1 month ago 9
MySQL Question

How can I resolve this SQLSTATE error in the context of cakephp 3?

I have looked through similar posts but they do not seem to apply.

I created a login and registration page on CakePHP 3 and when I tested the registration of a new user.

Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

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:

INSERT INTO users (name, email, password, created, modified) VALUES (:c0, :c1, :c2, :c3, :c4)
If you want to customize this error message, create src/Template/Error/pdo_error.ctp


I have read that it may be due to not having added auto-increment, but when I created id in phpmyAdmin I did check off A.I. for auto increment.

I am also not sure of whether or not it may have to do with my recent additions to my code in UsersController.php:

<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Event\Event;

/**
* Users Controller
*
* @property \App\Model\Table\UsersTable $Users
*/
class UsersController extends AppController
{

/**
* Index method
*
* @return \Cake\Network\Response|null
*/
public function index()
{
$users = $this->paginate($this->Users);

$this->set(compact('users'));
$this->set('_serialize', ['users']);
}

/**
* View method
*
* @param string|null $id User id.
* @return \Cake\Network\Response|null
* @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
*/
public function view($id = null)
{
$user = $this->Users->get($id, [
'contain' => ['Posts']
]);

$this->set('user', $user);
$this->set('_serialize', ['user']);
}

/**
* Add method
*
* @return \Cake\Network\Response|void Redirects on successful add, renders view otherwise.
*/
public function add()
{
$user = $this->Users->newEntity();
if ($this->request->is('post')) {
$user = $this->Users->patchEntity($user, $this->request->data);
if ($this->Users->save($user)) {
$this->Flash->success(__('The user has been saved.'));

return $this->redirect(['action' => 'index']);
} else {
$this->Flash->error(__('The user could not be saved. Please, try again.'));
}
}
$this->set(compact('user'));
$this->set('_serialize', ['user']);
}

/**
* Edit method
*
* @param string|null $id User id.
* @return \Cake\Network\Response|void Redirects on successful edit, renders view otherwise.
* @throws \Cake\Network\Exception\NotFoundException When record not found.
*/
public function edit($id = null)
{
$user = $this->Users->get($id, [
'contain' => []
]);
if ($this->request->is(['patch', 'post', 'put'])) {
$user = $this->Users->patchEntity($user, $this->request->data);
if ($this->Users->save($user)) {
$this->Flash->success(__('The user has been saved.'));

return $this->redirect(['action' => 'index']);
} else {
$this->Flash->error(__('The user could not be saved. Please, try again.'));
}
}
$this->set(compact('user'));
$this->set('_serialize', ['user']);
}

/**
* Delete method
*
* @param string|null $id User id.
* @return \Cake\Network\Response|null Redirects to index.
* @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
*/
public function delete($id = null)
{
$this->request->allowMethod(['post', 'delete']);
$user = $this->Users->get($id);
if ($this->Users->delete($user)) {
$this->Flash->success(__('The user has been deleted.'));
} else {
$this->Flash->error(__('The user could not be deleted. Please, try again.'));
}

return $this->redirect(['action' => 'index']);
}

// Login Controller
public function login(){
if($this->request->is('post')){
$user = $this->Auth->identify();
if($user){
$this->Auth->setUser($user);
return $this->redirect(['controller' => 'posts']);
}
// Bad Login
$this->Flash->error('Incorrect Login');
}
}

//Logout
public function logout(){
$this->Flash->success('You are logged out');
return $this->redirect($this->Auth->logout());
}

public function register(){
$user = $this->Users->newEntity();
if($this->request->is('post')){
$user = $this->Users->patchEntity($user, $this->request->data);
if($this->Users->save($user)){
$this->Flash->success('You are registered and can login');
return $this->redirect(['action' => 'login']);
} else {
$this->Flash->error('You are not registered');
}
}
$this->set(compact('user'));
$this->set('_serialize', ['user']);
}

public function beforeFilter(Event $event){
$this->Auth->allow(['register']);
}
}

Answer

This has nothing to do with the framework, it is a simple SQL violation, you need to set the primary key to autoincrement. You can how to set your primary key as autoincrement here

If there is still some error that you face, please share.

Comments