CODEWITHSUNDEEP
phpmysqlcodeigniterforeign-keys
Mushangi Derrick
Mushangi Derrick

Reputation: 134

Grabbing user id of current logged user and add it as foreign key value in a different table returns null - MySQL - CodeIgniter

I'm trying to insert data based on current logged in user. I'm grabbing the user email address from the session data, then I query users table for the user_id that matches the unique email address but upon inserting it throws Error Number: 1452.

Cannot add or update a child row: a foreign key constraint fails (ecamara.institution_contact_info, CONSTRAINT institution_contact_info_ibfk_2 FOREIGN KEY (institution_users_id) REFERENCES institution_users (institution_users_id))

I'm left thinking that when I retrieve the email address from the session data, it does not return any values.

Here is what I've tried;

login.php controller

public function login_validate()
{
    $this->form_validation->set_rules('email_address', 'Institution Email', 'trim|required|valid_email|callback_validate_credentials');
    $this->form_validation->set_rules('password', 'Password', 'trim|required');
    //$query = $this->form_validation->run();

    if($this->form_validation->run()) //if user credentials validate
    {
        $data = array(
            'email_address' => $this->input->post('email_address'),
            'is_logged_in' => true
            );

        $this->session->set_userdata($data); 
        redirect('login/members_area');
    }
    else
    {
        $this->index();
    }       
}

This is the callback function 

public function validate_credentials()
{
    $this->load->model('institution_users_model'); 

    if($this->institution_users_model->validate())
    {
        return true;
    }
    else
    {
        $this->form_validation->set_message('validate_credentials', 'Incorrect Email/Password');
        return false;
    }
}

vetting.php controller

public function create_contact()
{
    $this->load->model('institution_users_model');
    $create_institution_contact = $this->institution_users_model->create_institution_contact();
    if($create_institution_contact)
    {
        redirect('vetting/success_message');
    }
    else{
        redirect('vetting/institution_contact_info');
    }
}

institution_users_model.php

public function validate()
{
    $this->db->where('email_address', $this->input->post('email_address'));
    $this->db->where('password', md5($this->input->post('password')));
    $query = $this->db->get('institution_users');

    if($query->num_rows() == 1)
    {
        return true;
    }
    else
    {
        return false;
    }   
}

public function create_member()
{
    $user_data = array(
        'institution_name' => $this->input->post('institution_name'),
        'email_address' => $this->input->post('email_address'),
        'password' => md5($this->input->post('password'))
    );
    $insert = $this->db->insert('institution_users', $user_data);

    if($insert)
    {
        return true;
    }
    else
    {
        return false;
    }

}

public function create_institution_contact()
{
    $email_address = $this->session->userdata('email_address');
    $this->db->select('institution_users.institution_users_id');
    $this->db->where('institution_users.email_address', $email_address);
    $institution_users = $this->db->get('institution_users');
    $result = $institution_users->result();
    $institution_users_id = $result[0]->institution_users_id;

    $contact_data = array(
        'institution_users_id' => '$institution_users_id',
        'institution_registration_no' => $this->input->post('institution_registration_no'),
        'institution_address' => $this->input->post('institution_address'),
        'institution_telephone_no' => $this->input->post('institution_telephone_no'),
        'website' => $this->input->post('website')
    );

    $insert_contact = $this->db->insert('institution_contact_info', $contact_data);
    if($insert_contact)
    {
        return true;
    }
    else
    {
        return false;
    }
}

Upvotes: 1

Views: 1259

Answers (2)

Mushangi Derrick
Mushangi Derrick

Reputation: 134

I had to check whether the session data $email_address had a value, then perform the query and finally iterate over the elements of the array result $institution_users being returned to access the value for the variable $institution_users_id.

Here is the modified function create_institution_contact();

public function create_institution_contact()
{       
    $email_address = $this->session->userdata('email_address');
    if($email_address){
        $this->db->where('institution_users.email_address', $email_address);
        $institution_users = $this->db->get(institution_users); 
        foreach($institution_users->result() as $row)
        {
            $institution_users_id = $row->institution_users_id;
        }
    }               

    $contact_data = array(
        'institution_users_id' => $institution_users_id,
        'institution_registration_no' => $this->input->post('institution_registration_no'),
        'institution_address' => $this->input->post('institution_address'),
        'institution_telephone_no' => $this->input->post('institution_telephone_no'),
        'website' => $this->input->post('website')
    );

    $insert_contact = $this->db->insert('institution_contact_info', $contact_data);
    if($insert_contact)
    {
        return true;
    }
    else
    {
        return false;
    }
}

Upvotes: 0

Wood Guardian
Wood Guardian

Reputation: 599

It's got little to do with the app code layer. The database foreign key error is your clue to check the DB schema and see what dependencies are needed prior to adding an institution.

I'm guessing you'll need to define that "institution" record prior to inserting a user

Figure it out by running

SHOW CREATE TABLE institution_users\G

using mysql client. Check the ecamara table too

Upvotes: 0

Related Questions