how to fill field foreign key from field auto_increment

Question

I have 3 table for first table user, $data i have $id and it's auto_increment, and it like auto fill on mysql but the problem is the second table and the third $id is foreign key from first table, and when i insert the data, $id from the second table and the third table is empty, how i get $id for fill $id from the second table and the third?, here the code :

if($this->form_validation->run() == TRUE){
            echo 'Harap Tunggu...';
            //data akun
            $email = $this->input->post('email');
            $pass = md5($this->input->post('password'));

            //data pribadi
            $nama = $this->input->post('nama_lengkap');
            $alamat = $this->input->post('alamat');
            $tgl_lhr = $this->input->post('date');
            $no_telp = $this->input->post('no_telp');

            //data pendidikan
            $tingkat_pendidikan = $this->input->post('tingkat_pendidikan');
            $nama_institusi = $this->input->post('namainstitusi');
            $jurusan = $this->input->post('jurusan');
            $no_induk = $this->input->post('nomor_induk');
            $nilai = $this->input->post('nilai');
            $no_telpon_institusi = $this->input->post('no_institusi');

            $salt = $this->salt();

            $pass = $this->makePassword($this->input->post('password'), $salt);


            $data = array(
                'id' => $id,
                'email' => $email,
                'password' => $pass,
                'salt' => $salt
            );

            $data2 = array(
                'nama_lengkap' => $nama,
                'tanggal_lahir' => $tgl_lhr,
                'alamat' => $alamat,
                'no_telpon' => $no_telp,
                'id' => $id
                );

            $data3 = array(
                'tingkat_pendidikan' => $tingkat_pendidikan,
                'nama_institusi' => $nama_institusi,
                'jurusan' => $jurusan,
                'no_induk' => $no_induk,
                'nilai' => $nilai,
                'no_telpon_institusi' => $no_telpon_institusi,
                'id' => $id
                );


            $this->db->insert('user',$data);
            $id = $this->db->insert_id();
            $data2['id'] = $data3['id'] = $id;
            $this->db->insert('data_pribadi',$data2);
            $this->db->insert('pendidikan',$data3);

            $this->session->set_flashdata("success","your account has been created");
            $this->session->set_flashdata('id', $id);
            redirect('upload_foto/index','refresh');

        }

id and foreign key should be 2 column. Cz `foreign key` can have same value 1 or more — Abdulla Nilam, Mar 20 '17 at 18:53
You can get last insert id after first insertion, that you can use for another table, to get last id please have a look into https://www.w3schools.com/php/php_mysql_insert_lastid.asp — Omi, Mar 20 '17 at 18:54

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

In order to fetch the last id inserted you can use the below codes. call $this->db->lastInsertId();check this link

edited May 23 '17 at 12:32

Community

1
1

answered Mar 20 '17 at 18:58

Nabheet Madan

1
3

i got this error "Message: Call to undefined method CI_DB_mysqli_driver::lastInsertId()" – Firm Mar 20 '17 at 19:16
You are using mysqli so below code will help – Nabheet Madan Mar 20 '17 at 19:49

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

I don't know what $this means but PDO and mysqli implement lastInsertId function
You should call to the first insertion and then store the last inserted id into variable.
Assign $data2['id'] and $data3['id'] with the return value from lastInsertId method.

Code:

 <?php
       if($this->form_validation->run() == TRUE){
            echo 'Harap Tunggu...';
            //data akun
            $email = $this->input->post('email');
            $pass = md5($this->input->post('password'));

            //data pribadi
            $nama = $this->input->post('nama_lengkap');
            $alamat = $this->input->post('alamat');
            $tgl_lhr = $this->input->post('date');
            $no_telp = $this->input->post('no_telp');

            //data pendidikan
            $tingkat_pendidikan = $this->input->post('tingkat_pendidikan');
            $nama_institusi = $this->input->post('namainstitusi');
            $jurusan = $this->input->post('jurusan');
            $no_induk = $this->input->post('nomor_induk');
            $nilai = $this->input->post('nilai');
            $no_telpon_institusi = $this->input->post('no_institusi');

            $salt = $this->salt();

            $pass = $this->makePassword($this->input->post('password'), $salt);


            $data = array(
                'id' => $id,
                'email' => $email,
                'password' => $pass,
                'salt' => $salt
            );

            $data2 = array(
                'nama_lengkap' => $nama,
                'tanggal_lahir' => $tgl_lhr,
                'alamat' => $alamat,
                'no_telpon' => $no_telp,
                'id' => $id // Undefined
                );

            $data3 = array(
                'tingkat_pendidikan' => $tingkat_pendidikan,
                'nama_institusi' => $nama_institusi,
                'jurusan' => $jurusan,
                'no_induk' => $no_induk,
                'nilai' => $nilai,
                'no_telpon_institusi' => $no_telpon_institusi,
                'id' => $id // Undefined
                );


            $this->db->insert('user',$data);
            $id = $this->db->insert_id();
            $data2['id'] = $data3['id'] = $id;
            $this->db->insert('data_pribadi',$data2);
            $this->db->insert('pendidikan',$data3);

            $this->session->set_flashdata("success","your account has been created");
            $this->session->set_flashdata('id', $id);
            redirect('upload_foto/index','refresh');

        }

1. i create this from codeigniter 2. can you give an example? 3. how to return value from lastInsertId? — Firm, Mar 20 '17 at 19:09
ok, in codeigniter you can use $this->db->insert_id(); I update my answer. — 4EACH, Mar 20 '17 at 19:17
it's work thanks, but i have 1 question, how i flash data form $id ? i try like this $this->session->set_flashdata('id', $id); but i got error — Firm, Mar 20 '17 at 19:27
Its a different question, provide more info / code as you did at this question and i will help you. — 4EACH, Mar 20 '17 at 19:30
oke i edited my code from question and i put more. just can you see it? — Firm, Mar 20 '17 at 19:40
I edit my answer, i comment the undefined $id, you have it twice in your code, in data2 and data3 — 4EACH, Mar 20 '17 at 19:55

how to fill field foreign key from field auto_increment

2 Answers2

Code: