how to get last id after insert db in php-CodePudding

here I save data to the database with multiple rows, I want to retrieve each last id per row, how do I do that?

ex : print_r result when input data.

Array
(
    [0] => Array
        (
            [name] => metode 11
            [description] => DESC 1
        )

    [1] => Array
        (
            [name] => metode 22
            [description] => DESC 2
        )

    [2] => Array
        (
            [name] => metode 33
            [description] => DESC 1
        )
)

in database

id | name      | description
1    metode 11   desc 1
2    metode 22   desc 2
3    metode 33   desc 3

after finished input I want to take each id and this my code

$data = array(); 
$numrow = 1;
foreach ($sheet as $row) {
    if ($numrow > 1) {
        array_push($data, array(
                'name'              => $row['A'],
                'description'       => $row['B'],
        ));
    }
    $numrow  ;
}

$this->db->insert_batch('my_tabel', $data);
$myid =$this->db->insert_id();
//after insert i retrieve id using last_id() function, however, only last id is fetched, i want to fetch each id

$data_1 = array();
$numrow2 = 1;

foreach ($sheet as $row) {
    if ($numrow2 > 1) {
        array_push($data_1, array(
            'id_last'  => $myid,
            'id'     => $row['E'],
            'barang' => $row['F'],
        ));
    }
    $numrow2  ;
}

i want like this :

Array
        (
            [0] => Array
                (
                    [last_id] => 1
                    [id] => 33
                    [barang] => ccc
                )
        
            [1] => Array
                (
                    [last_id] => 2
                    [id] => 44
                    [barang] => dd
                )
        
            [2] => Array
                (
                    [last_id] => 3
                    [id] => 55
                    [barang] => eee
                )
        )

thanks in advance for those who have answered I hope what I said was clear enough

CodePudding user response：

Why not insert it row by row instead of a batch? Build a dictionary from each insert to pair it back later on.

$data = array(); 
$numrow = 1;
foreach ($sheet as $row) {
    if ($numrow > 1) {
        array_push($data, array(
                'name'              => $row['A'],
                'description'       => $row['B'],
        ));
    }
    $numrow  ;
}
$array_of_insert_id = array();

foreach ($data as $index => $data_to_insert) {
    $this->db->insert('my_tabel', $data_to_insert);
    $array_of_insert_id[$index] = $this->db->insert_id();
}

$data_1 = array();
$numrow2 = 1;
$counter = 0;


foreach ($sheet as $row) {
    if ($numrow2 > 1) {
        array_push($data_1, array(
            'id_last'  => $array_of_insert_id[$counter],
            'id'     => $row['E'],
            'barang' => $row['F'],
        ));
    }
    $numrow2  ;
    $counter  ;
}