I have a problem that I'm quite looking for a solution to. I want to generate a parent ID for its children. This list is coming from the CSV file.

CSV File

Database Data entries from an imported CSV file

The CSV file has no parent ID column. I wanted the user to minimize updating the CSV file instead the system will just generate it automatically during the importing process.

CONTROLLER:

public function update_atf_parent_id()
{
    $financeDb = db_connect('finance');
    $FinanceATFModel = new \App\Models\FinanceATFModel($financeDb);
    $builder = $financeDb->table('atf');
    $builder->where('type', 'Payout');
    $query = $builder->get()->getResultObject();
    foreach ($query as $row) {
        $new_data = array('parent_id' => $row->id);
        $builder->where('date', $row->date);
        $builder->update($new_data);
        
    }
}


public function atf_add()
{
    /** HELPERS */
    $financeDb = db_connect('finance');

    /** HELPERS */
    helper('form');

    /** MODELS */
    $FinanceATFModel = new \App\Models\FinanceATFModel($financeDb);
    $FinanceATFFileModel = new \App\Models\FinanceATFFileModel($financeDb);
    $UserModel = new \App\Models\UserModel();
    $DataOptionModel = new \App\Models\DataOptionModel();
    $FinanceDataOptionModel = new \App\Models\FinanceDataOptionModel($financeDb);

    /** VARIABLES */
    $subpage = $this->request->uri->getSegment(2);
    $action = $this->request->uri->getSegment(3);
    $uri_action = (strpos($this->request->uri->getSegment(2), 'add') !== false) ? 'add' : 'view';
    $uri_action = (strpos($this->request->uri->getSegment(2), 'update') !== false) ? 'update' : $uri_action;
    $current_page = ($uri_action == 'add') ? 'Add Finance Payouts' : 'View Finance Payouts';
    $current_page = ($uri_action == 'update') ? 'Update Finance Payouts' : $current_page;
    $airbnb_account = $this->request->getVar('airbnb_account');
    $get_airbnb_account = $FinanceDataOptionModel->get_airbnb_account($airbnb_account);

    $count_atf = $FinanceATFModel->where('airbnb_account', $get_airbnb_account)->countAllResults();
    $atf_position_max =
        $count_atf > 0
        ? $FinanceATFModel->where('airbnb_account', $get_airbnb_account)->selectMax('position')->get()->getRow()->position
        : 0;

    $data = array(
        'title' => 'Add ATF - Finance',
        'id' => '',
        'subpage' => $subpage,
        'uri_action' => $uri_action,
        'page' => $this->request->uri->getSegment(1),
        'current_page' => $current_page,
        'disabled' => $uri_action === 'view' ? 'disabled' : '',
        'action' => $action,
        'getAirbnbAccounts' => $FinanceDataOptionModel->getAirbnbAccounts(),
    );

    // Validation
    $input = $this->validate([
        'atf_file' => 'uploaded[atf_file]|max_size[atf_file,4098]|ext_in[atf_file,csv]'
    ]);
    if (!$input) { // Not valid
        echo view('templates/header', $data);
        echo view('templates/sidebar');
        echo view('templates/navbar');
        echo view('finance/atf/atf_add');
        echo view('templates/footer');
    } else {
        // Valid
        if ($file = $this->request->getFile('atf_file')) {

            $dir = 'public/uploads/atf/';
            $dir_exist = true; // flag for checking the directory exist or not
            if (!is_dir($dir)) {
                mkdir($dir, 0777, true);
                $dir_exist = false; // dir not exist
            }

            if ($file->isValid() && !$file->hasMoved()) {
                // Get random file name
                $newName = $file->getRandomName();
                // Store file in public/csvfile/ folder
                $file->move($dir, $newName);
                $file = fopen($dir . $newName, "r");

                // insert imported csv file to 'atf_file' table
                $fileData = array(
                    'file_name' => $newName,
                    'performed_by' => session()->get('id'),
                    'date_created' => date('Y-m-d h:i:s'),
                );
                $builder = $financeDb->table('atf_files');
                $builder->insert($fileData);
                $insertID = $financeDb->insertID();

                $i = 0;
                $numberOfFields = 15; // Total number of fields
                $importData_arr = array();
                // Initialize $importData_arr Array
                while (($filedata = fgetcsv($file, 1000)) !== FALSE) {
                    // DATE
                    $ex_date = explode('/', $filedata[1]);
                    $d = isset($ex_date[0]) ? $ex_date[0] : '';
                    $m = isset($ex_date[1]) ? $ex_date[1] : '';
                    $y = isset($ex_date[2]) ? $ex_date[2] : '';
                    $date = $y . '-' . $m . '-' . $d;
                    // $date = $filedata[1];
                    // START DATE
                    $ex_date = explode('/', $filedata[4]);
                    $d = isset($ex_date[0]) ? $ex_date[0] : '';
                    $m = isset($ex_date[1]) ? $ex_date[1] : '';
                    $y = isset($ex_date[2]) ? $ex_date[2] : '';
                    $start_date = $y . '-' . $m . '-' . $d;
                    // $start_date = $filedata[4];
                    // OTHER VARIABLES
                    $position = $filedata[0];
                    $type = $filedata[2];
                    $confirmation_code = $filedata[3];
                    $nights = $filedata[5];
                    $guest_name = $filedata[6];
                    $listing = $filedata[7];
                    $details = $filedata[8];
                    $reference = $filedata[9];
                    $currency = $filedata[10];
                    $amount = $filedata[11];
                    $paid_out = str_replace(',', '', $filedata[12]);
                    $host_fee = $filedata[13];
                    $cleaning_fee = $filedata[14];
                    $created = date('Y-m-d H:i:s');
                    $updated = date('Y-m-d H:i:s');

                    $count_rows = count(file($dir . $newName, FILE_SKIP_EMPTY_LINES)) + $atf_position_max;


                    $parent_id = array_search('Payout', $importData_arr, 'position');


                    // Skip first row & check number of fields
                    $num = count($filedata);
                    if ($i > 0 && $num == $numberOfFields) {
                        // $position =  $position + $count_rows - $i; // for position, starting from biggest to lowest number
                        $position =  $count_rows - $i; // for position, starting from biggest to lowest number
                        // $_position =  $count_rows; // for position, starting from biggest to lowest number
                        // $_position =  $count_rows - $i; // for position, starting from biggest to lowest number
                        // $position = $filedata[0]; // for position, starting from biggest to lowest number
                        // Key names are the insert table field names - amount, description, ending_balance, status, link, notes, trusts_type, date_created
                        // $position = $position; // for position, starting from biggest to lowest number

                        // $importData_arr[$i]['parent_id'] = $parent_id;
                        $importData_arr[$i]['position'] = $position;
                        $importData_arr[$i]['date'] = $date;
                        $importData_arr[$i]['type'] = $type;
                        $importData_arr[$i]['confirmation_code'] = $confirmation_code;
                        $importData_arr[$i]['start_date'] = $start_date;
                        $importData_arr[$i]['nights'] = $nights;
                        $importData_arr[$i]['guest_name'] = $guest_name;
                        $importData_arr[$i]['listing'] = $listing;
                        $importData_arr[$i]['details'] = $details;
                        $importData_arr[$i]['reference'] = $reference;
                        $importData_arr[$i]['currency'] = $currency;
                        $importData_arr[$i]['amount'] = $amount;
                        $importData_arr[$i]['paid_out'] = $paid_out;
                        $importData_arr[$i]['host_fee'] = $host_fee;
                        $importData_arr[$i]['cleaning_fee'] = $cleaning_fee;
                        $importData_arr[$i]['airbnb_account'] = $airbnb_account;
                        $importData_arr[$i]['created'] = $created;
                        $importData_arr[$i]['updated'] = $updated;
                    }
                    $i++;
                }
                fclose($file);

                // Process Insert / Update
                $count = 0;
                $importData_arr = array_reverse($importData_arr);

                // ======================================================================================

                $builder = $financeDb->table('atf');

                foreach ($importData_arr as $atfdata) {
                    if ($FinanceATFModel->insert($atfdata)) {
                        $count++;
                    }
                }

                session()->setFlashdata('success', '<div class="alert alert-success w-100 shadow-lg border border-success"><i class="fa fa-check-circle"></i> Successfully imported.</div>');


                $this->update_atf_position($get_airbnb_account);


                /** LOGS */
                helper('function_helpers');
                logs('add', 'add finance atf::' . $airbnb_account, $financeDb->insertID());
            } else {
                // MESSAGE
                session()->setFlashdata('success', '<div class="alert alert-danger w-100 shadow-lg border border-success"><i class="fa fa-check-circle"></i> File not imported.</div>');
            }
        } else {
            // Set Session
            // session()->setFlashdata('success', '<div class="alert alert-danger w-100 shadow-lg border border-success"><i class="fa fa-check-circle"></i> File not imported.</div>');
        }
        return redirect()->to(base_url("finance/atf/atf_entries?account=$airbnb_account"));


        print_r('<pre>');
        print_r($importData_arr);
        print_r('</pre>');
    }
}

In my current setup I imported the CSV file then the parent ID generation will be done after the file has been imported update_atf_parent_id() this controller is the one will generate the parent ID. I am referencing the date column as the group to be updated. But the downside is that once the other groups have the same date those groups will be considered as one group. That's why the parent ID will be the same. If you guys have some solution for this that would be a big help. Thanks in advance everyone!

P.S. I am using codeigniter framework version 4.