How to divide incoming rows of data to be executed by CodeIgniter's update_batch() and insert_batch()?

Question

My objective is to use a combination of CodeIgniter's insert_batch() and update_batch() to add incoming data to my macro_plan table.

In my script below I am attempting to query the database for existing rows based on sr_no values then appropriately call the batch querying methods.

function insert_batch($dataSet)
{
    $query = $this->db->query("select sr_no from macro_plan");
    $data = $query->result_array();
    $sr_nos=array();

    foreach($data as $key => $value):
        $sr_nos[$key]=$value['sr_no'];
    endforeach;

    $query1= $this->db->query("select * from macro_plan WHERE sr_no IN ('".$sr_nos."')");
    $update_query = $query1->result();
    if ($update_query->num_rows() > 0) {

        $this->db->update_batch($dataSet,$this->macro_plan);//update if ids exist
    } else {
        $this->db->insert_batch($dataSet,$this->macro_plan);//insert if does not exist
    }
}

However, I am getting the "array to string conversion" error.

$dataset will resemble this:

Array (
    [0] => Array (
        [quantity_update] => 88
        [sr_no] => 2020-11-1
        [batch] => Batch 2
        [quantity_date_update] => 05-May-20
        [inq_id] => 49
    )
    [1] => Array (
        [quantity_update] => 99
        [sr_no] => 2020-11-2
        [batch] => Batch 1
        [quantity_date_update] => 11-May-20
        [inq_id] => 49
    )
)

My table structure looks like this:

Answer 1

1. Query your table for pre-existing rows that contain the sr_no values that exist in your $dataSet.
2. Then apply keys to the result set rows from the sr_no values -- this allows the swift lookup on new data against the old data (to see if the respective new rows should be inserted, executed as an update, or completely ignored because the data is the same.

Untested Suggestion:

function insertUpdateMacroPlan($dataSet)
{
    $keyedExistingRows = array_column(
        $this->db
            ->where_in('sr_no', array_column($dataSet, 'sr_no'))
            ->get('macro_plan')
            ->result_array(),
        null,
        'sr_no'
    );

    foreach ($dataSet as $data) {
        if (isset($keyedExistingRows[$data['sr_no']])) {
            // sr_no exists in the db, add known id to new data array
            $identified = ['id' => $keyedExistingRows[$data['sr_no']]['id']] + $data;

            if ($identified != $keyedExistingRows[$data['sr_no']]) {
                $updateBatch[] = $identified;
            }
            // if the arrays contain the same data, the new data will be discarded
        } else {
            $insertBatch[] = $data;
        }
    }

    if (!empty($insertBatch)) {
        $this->db->insert_batch('macro_plan', $insertBatch);
    }
    if (!empty($updateBatch)) {
        $this->db->update_batch('macro_plan', $updateBatch, 'id');
    }
}

p.s. if your business logic requires that sr_no values are unique, I recommend that you reflect that in your table configuration by setting the sr_no column as a Unique Key.