Saving data into multiple tables from one form in CakePHP 2.5.1

Question

I am new in CakePHP, developing a cloud application using CakePHP 2.5.1 and MySQL. i have following tables:

users

  user_id ,
  people_id(FK- people.people_id),
  username,
  password

people

  people_id, 
  manager_id(FK- people.people_id), 
  firsr_name,
  last_name,

roles

  role_id ,
  people_id (FK- people.people_id),
  role

addresses

  address_id,
  people_id (FK- people.people_id),
  street,
  house no,
  post code,
  city

Foreign key people.manager_id refers to primary_key people.people_id. The field roles.role can hold either 'manager' or 'customer'.

Now, in my registration form, there will be following input fields:

-username
-password
-first name
-last name
-street
-house no
-post code
-city
-role

I have used a User model (User.php), a registration form (add.ctp) and a controller (UsersController.php) to input username and password; and implemented login, logout.

The View add.ctp:

<div class="users form">
<?php echo $this->Form->create('User'); ?>
    <fieldset>
        <legend><?php echo __('Registrierung'); ?></legend>

        <?php echo $this->Form->input('username');

              echo $this->Form->input('password');
              echo $this->Form->input('password_confirm', array('label' => 'Confirm Password', 'maxLength' => 255, 'title' => 'Confirm password', 'type'=>'password'));


        );  
    ?>
    </fieldset>
<?php echo $this->Form->end(__('Register')); ?>
</div>

is working fine. Now I need to include above input fields in the registration form. This means creating models for each table and defining the associations like belogsTo, hasMany, etc. in my User model, I have defined the following association:

class User extends AppModel {

    public $belongsTo = array(
            'Person' => array(
                'className' => 'Person',
                'foreignKey' => 'people_id'

            )
    );
}

However, it throws the following error:

Database Error
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Person.id' in 'on clause'

Could anyone tell me how to implement these associations? I could not find any example which matches with my requirements.

Indrasis Datta · Accepted Answer · 2016-01-19 06:14:46Z

2

You can explicitly define the primary key in each of the models, but that's make it a whole lot complicated.

I'd recommend you to stick to the conventions. Please change all the primary key fields to "id". That'd probably resolve a bulk of your problems.

Peace! xD

answered Jan 19, 2016 at 6:14

Indrasis Datta

8,6182 gold badges18 silver badges32 bronze badges

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

Comments

Inigo Flores · Accepted Answer · 2016-01-19 18:53:59Z

1

The MySQL error is being caused by the way you have named your primary keys. In CakePHP, primary keys should be named id.

If you wish to use a different name (not recommendable), you have to tell CakePHP what name you will be using. This is done declaring the $primaryKey property. Example:

class Person extends AppModel {

    public $primaryKey = 'people_id';

}

However, it's preferable that you rename your primary keys and adhere to CakePHP conventions as much as possible.

Also, by the way CakePHP treats plurals, your people_id fields should be renamed to person_id.

EDIT: Adding relationships

Your relationships could be as follows.

Your Person class:

class Person extends AppModel{

    public $belongsTo = array(
        'Manager' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
    );

    public $hasMany = array(
        'User',
        'Role',
        'Address',
        'Subordinate' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
    );
}

Your other classes:

class User extends AppModel{

    public $belongsTo = array(
        'Person'
    );
}

class Role extends AppModel{

    public $belongsTo = array(
        'Person'
    );
}

class Address extends AppModel{

    public $belongsTo = array(
        'Person'
    );
}

Remember to change all foreign keys from people_id to person_id.

Modification in model Role

I think you may want to move field roles.person_id to people.role_id. IMHO it makes more sense.

Your relationships would change to:

class Person extends AppModel{

    public $belongsTo = array(
        'Manager' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
        'Role'
    );

    public $hasMany = array(
        'User',
        'Address',
        'Subordinate' => array(
            'className' => 'Person',
            'foreignKey' => 'manager_id',
        ),
    );
}

and

class Role extends AppModel{

    public $hasMany = array(
        'Person'
    );
}

See

Cookbook 2.x: Model and Database Conventions

edited Jan 19, 2016 at 18:53

answered Jan 18, 2016 at 17:28

Inigo Flores

4,4591 gold badge17 silver badges37 bronze badges

12 Comments

Tom Over a year ago

thank you for your reply. i have changed the column name to id. the database error has gone now. but can you tell me how to implement the associations, specially people table ? since it has a foreign key which points to the primary key of the same table.

Tom Over a year ago

of course i will mark. actually, my question has two parts, one for the errors and another one is for implementation of the associations. can you help me for the second part ?

Inigo Flores Over a year ago

I've added the relationships. Let me know if you have any questions.

Tom Over a year ago

thank you so much again ! regarding the roles and people tables, i should inform you my requirements. while registering on the application, the user can be registered as a manager or customer. if manager, he/she can have multiple customers. so, in this scenario, do you think i should move the field roles.person_id to people.role_id ?

Inigo Flores Over a year ago

If you don't move it, you would end up repeating the value in roles.role every time you added a Person. You might as well get rid of the roles table and add a role (VARCHAR(8)) field to table people, which would hold either 'manager'or 'customer'.

|

Collectives™ on Stack Overflow

Saving data into multiple tables from one form in CakePHP 2.5.1

2 Answers 2

Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related