phpcodeigniterdatabase-migration

How to transfer fields to a database where one field is foreign?


I can't add fields to the profile, user_id seems to work, the values ​​are correct, but it doesn't add to the field, but if I change it manually in mysql, it changes the value without errors, what should I do?

It turns out that my question is that when a user registers, in addition to the user, a profile is created that is linked by user_id, but I can’t do it no matter how much I try

I have a database, here are its migrations(profile):

public function up()
{
    $this->forge->addField([
        'id' => [
            'type' => 'INT',
            'constraint' => 10,
            'unsigned' => true,
            'auto_increment' => true,

        ],
        'firstname' => [
            'type' => 'VARCHAR',
            'constraint' => '128',
            'null' => true,
        ],
        'lastname' => [
            'type' => 'VARCHAR',
            'constraint' => '128',
            'null' => true,
        ],
        'email' => [
            'type' => 'VARCHAR',
            'constraint' => '128',
            'null' => true,

        ],
        'phone' => [
            'type' => 'VARCHAR',
            'constraint' => '128',
            'null' => true,

        ],
        'description' => [
            'type' => 'TEXT',
            'constraint' => '128',
            'null' => true,

        ],
        'date_birth' => [
            'type' => 'DATE',
            'null' => true,
        ],
        'avatar' => [
            'type' => 'TEXT',
            'null' => true,
        ],
        'user_id' => [
            'type' => 'int',
            'constraint' => 11,
            'unsigned' => true,
            'null' => false,
        ],
    ]);

    $this->forge->addForeignKey('user_id', 'user', 'id', '', 'CASCADE');
    $this->forge->addPrimaryKey('id')->addUniqueKey('user_id');;
    $this->forge->createTable('profile');
}

public function down()
{
    $this->forge->dropTable('profile');
}

user migration:

public function up()
{
    $this->forge->addField([
        'id' => [
            'type'  => 'INT',
            'constraint' => 10,
            'unsigned' => true,
            'auto_increment' => true,
        ],
        'name' => [
            'type'  => 'VARCHAR',
            'constraint' => '128',
            'null' => false,
        ],
        'password_hash' => [
            'type' => 'VARCHAR',
            'constraint' => '255',
            'null' => false,
        ]
        ]);

        $this->forge->addPrimaryKey('id')->addUniqueKey('name');
        $this->forge->createTable('user', true);
}

public function down()
{
    $this->forge->dropTable('user');
}

controller registration (the task when creating a user is to create a separate profile):

public function create_account()
{
    $user = $this->request->getPost();

    $avatar = $user['avatar'];  


    if ($user) {
        if ($this->model->insert($user)) {
            $user_id = $this->request->getPost('name');
            $login = $this->model->where('name', $user_id)->first();
            $session = session();
            $session->set('user_id', $login->id);
            $session->set('user_name', $login->name);
            if (session()->has('user_id') && $this->profile->insert(['firstname' => 'ivan', 'avatar' => $avatar, 'user_id' => 18])) {
                return redirect()->to('/home')->with('success', ['Вы успешно авторизовались']);
            }
        } else {
            return redirect()->back()->with('errors', $this->model->errors())->with('warning', 'Invalid data')->withInput();
        }
    }
}
    

Solution

  • The error of

    Cannot add or update a child row: a foreign key constraint fails (`mypart`.`profile`, CONSTRAINT `profile_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE)
    

    Tells you that the foreign key on user_id that references user(id) in your profile table is being violated. Let's understand the issue. In general, this error can occur when your operation would end up having a profile whose user_id is not null and has a value that does not match any user.

    This happens because of your insert here:

            if ($this->profile_model->insert(['avatar' => $avatar]) && session()->has('user_id')) {
                return redirect()->to('/home')->with('success', ['Вы успешно авторизовались']);
            }
    

    where you attempt to insert a profile for which you do not set user_id and therefore you violate this constraint. Change it to

            if (session()->has('user_id') && $this->profile_model->insert(['avatar' => $avatar, 'user_id' => $session->get('user_id')])) {
                return redirect()->to('/home')->with('success', ['Вы успешно авторизовались']);
            }
    

    where you set the user_id. Note that I changed the order of your operands, to first check for user_id in the session and only if it is found to insert the profile for it.