Edit table in CakePHP 2: Update/Insert error

Question

Problem: When editing an entry SQL is making an INSERT instead of an UPDATE.

I have two SQL tables : users and users_detail. users(id,role,name,password) & users_detail(id,adress,city,user_id)

Foreign key users_detail.user_id is linked to users.id.

I have a form in my cakephp app to edit users_detail, if a user wants to edit his adress for example. Here is my controller :

        public function admin_edit_dashboard($id = null){
    if (!$this->User->exists($id)) {
        throw new NotFoundException('Invalid user details');
    }
    if ($this->request->is('post') || $this->request->is('put')) {

        if ($this->User->UserDetail->save($this->request->data, true, ['id', 'address', 'city'])) {
            $this->Session->setFlash('The user has been saved');
            return $this->redirect(array('action' => 'dashboard'));
        } else {
            $this->Session->setFlash('The user could not be saved. Please, try again.');
        }
    } else {
        //$this->request->data = $this->User->read(null, $id);
        $user = $this->User->UserDetail->find('first', array(
            'conditions' => array(
                'UserDetail.user_id' => $id
            )
        ));
        $this->request->data = $user;
    }
    $this->set(compact('user'));
}

And my form :

<?php echo $this->Form->create('UserDetail');?>
        <?php echo $this->Form->input('address', array('class' => 'form-control')); ?>
        <br />
        <?php echo $this->Form->input('city', array('class' => 'form-control')); ?>
        <br />
        <?php echo $this->Form->button('Submit', array('class' => 'btn btn-primary')); ?>
        <?php echo $this->Form->end(); ?>

But when I'm validate the form to edit details I have an error :

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

Because the SQL query isn't an UPDATE but an INSERT. I don't know why.

SQL Query: INSERT INTO cake.user_details (adress_ex, city_ex) VALUES ('roses street', 'London')

Thank you!

EDIT : It works, thanks for your help! Good code added.

Answer 1

You appear to have several problems.

$this->request->address is in correct. Form data is passed as $this->request->data so you should be using $this->request->data['UserDetail']['address'] etc.

If you want to make sure you only save specific fields you can pass these as the third parameter of save() and then you don't need to use set() beforehand:-

$this->UserDetail->save($this->request->data, true, ['id', 'address', 'city']);

The SQL error implies that your primary key on the user_details table is not set to auto-increment (which it should). Which is why an INSERT fails.

You are also forgetting to pass the primary key for the record you're trying to update so Cake assumes you want to create a new record. Make sure you include this so that Cake knows to use UPDATE. For example include the following in your View form:-

echo $this->Form->hidden('id');

Answer 2

Your User model should have association to UserDetail like:

public $hasOne = array('UserDetail');

and your in UserDetail:

public $belongsTo = array('User');

Then you don't have to use loadModel, just do:

$this->User->UserDetail->find('first', array(...));

And you should edit user details from UserDetailsController action.

In your view add lines to know what you are editing:

echo $this->Form->input('id');

And then just do save with passed $this->request->data. That should do it. And like I said, check table id in database, it has to be autoincrement.

Answer 3

You can't update just your table by telling in controller

$this->UserDetail->save($this->request->data)

Instead you should try to do something like

//find that specific row in database and then update columns and save it

$this->UserDetail->set(array('address'=>$request->address,'city'=>$request->city));                
$this->UserDetail->save();

Your model wants to save it like a new user_details under id = 0 which already exists because you didn't specify that you want to update it and which columns you want to update.

Hope it helps