Codeigniter update table column with different values

Question

I have the following records in my table:

id            status         

129           'Ready'           
129           'Ready'          
129           'Confirm'          
129           'Confirm'        
129           'Processing'        
130           'Confirm'

What I am trying to change is change the status according to id based on user input.

I am capturing user input in my controller as below:

$id= $this->input->post("id");
$confirm = $this->input->post("confirm");
$processing = $this->input->post("processing");
$ready = $this->input->post("ready");
$delivered = $this->input->post("delivered");

an example of user input values is as follows:

   $id= 129
   $confirm = 1
   $processing = 2
   $ready = 2
   $delivered = 0

This basically means that all rows with id 129 should have status in the order, confirm should appear once, processing twice, ready ready and no delivered.

How can I update the status so that it would be like below using the above input data:

id            status 
129           'Ready'
129           'Ready' 
129           'Confirm'
129           'Processing' 
129           'Processing' 
130           'Confirm'

In my model I tried as below but it's not working.

public function update_status($serialized,$confirm,$processing,$ready,$delivered) {
$data = array(
'status' => 'Confirm',  
);
            $this->db->where('is_serialized', $serialized);
            $this->db->limit($confirm);
            $this->db->update('items', $data);`$data = array(
'status' => 'Processing',   
);
            $this->db->where('is_serialized', $serialized);
            $this->db->limit($processing);
            $this->db->update('items', $data);
$data = array(
'status' => 'Ready',    
);
            $this->db->where('is_serialized', $serialized);
            $this->db->limit($ready);
            $this->db->update('items', $data);
$data = array(
'status' => 'Delivered',    
);
            $this->db->where('is_serialized', $serialized);
            $this->db->limit($delivered);
            return $this->db->update('items', $data); }

Any help would be highly appreciated.

`

i'm sorry but this makes no sense. assuming the table only has 2 columns, and that id isn't unique, you can't select a specific column to change hence you are trying to do some wacky limits to achieve this. i'm assuming you are doing an ordering system where items can be dispatched at different paces, you should really revise your schema. this is unwieldy. the folks over at dba.stackexchange.com might be able to help — Alex
– Alex, Commented Sep 13, 2018 at 0:21
sorry if it is not very clear, the table has other columns but I didn't list them because the change I am seeking involves there two only. What I am trying to get is enable a user to change the status of a row or rows with a particular id. For eg, there are two rows of id 129 having status as ready, if the user want to change one or both these rows to status processing, how to do that? — Hamza Zahir
– Hamza Zahir, Commented Sep 13, 2018 at 0:56
for ALL $this->db->where('status', 'Ready'); $this->db->update('table', array('status' => 'Processing'); however I wouldn't suggest using strings and instead use number codes 1, confirm; 2, ready; .etc. and define those codes according to their string values in a separate table. I still don't think this is the most efficient/right way to do this because if user wants to change one row, you don't have a unique column (?) so your left doing less than efficient methods. — Alex
– Alex, Commented Sep 13, 2018 at 1:02
the number actually stand for how many rows to change, eg. if confirm is 2 it need to change 2 rows having ready value to confirm value. — Hamza Zahir
– Hamza Zahir, Commented Sep 13, 2018 at 1:05
"if the user want to change one or both" hence you need to know which row with id 129 and status ready to change. doesn't this matter? or do you just want to arbitrarily choose a row with id 129 and status ready and change its value. — Alex
– Alex, Commented Sep 13, 2018 at 1:09

Alex · Accepted Answer · 2018-09-13 01:31:37Z

Try this out:

function update_status($id, $serialized, $confirm = 0, $processing = 0, $ready = 0, $delivered = 0) {
    if (count($confirm) > 0) {
        $this->db->where('id', $id);
        $this->db->where('is_serialized', $serialized);
        $this->db->limit($confirm);
        $this->db->update('items', array('status' => 'Confirm'));
    }
    if (count($processing) > 0) {
        $this->db->where('status', 'Confirm');
        $this->db->where('id', $id);
        $this->db->where('is_serialized', $serialized);
        $this->db->limit($processing);
        $this->db->update('items', array('status' => 'Processing'));
    }
    if (count($ready) > 0) {
        $this->db->where('status', 'Processing');
        $this->db->where('id', $id);
        $this->db->where('is_serialized', $serialized);
        $this->db->limit($ready);
        $this->db->update('items', array('status' => 'Ready'));
    }
    if (count($delivered) > 0) {
        $this->db->where('status', 'Ready');
        $this->db->where('id', $id);
        $this->db->where('is_serialized', $serialized);
        $this->db->limit($delivered);
        $this->db->update('items', array('status' => 'Delivered'));
    }
}

mickmackusa · Accepted Answer · 2025-12-09 16:41:05Z

I either don't understand the business requirement or this task is ill-considered.

The process seems to want to update a predefined number of records with certain statuses without knowing what each record's original status was or if there are even the correct number of records in existence to hold the desired number of status values. The submission payload seems to be completely disassociate from the actual database records. [shrugs shoulders]

Here is a D.R.Y. script to receive the submitted changes, wipe the original status from the expected number of qualifying rows, then repopulate the wiped values. The model method returns the number of records actually changed - for the sake of comparing against the submission total, if desired.

Controller:

$id = $this->input->post('id');
$statusCounts = [
    'Confirm' => $this->input->post('confirm') ?: 0,
    'Processing' => $this->input->post('processing') ?: 0,
    'Ready' => $this->input->post('ready') ?: 0,
    'Delivered' => $this->input->post('delivered') ?: 0,
];

$affRows = $this->Item_model->updateStatus(
    $id,
    array_filter($statusCounts, fn($c) => $c > 0)
);

The parameters of CodeIgniter's update() method are table name, SET clause, WHERE clause, then LIMIT.

Model:

public function updateStatus(int $id, array $statusCounts): int
{
    if (!$statusCounts) {
        return 0;
    }

    // clear value on specified number of records
    $this->db->update(
        'items',
        ['status' => null],
        ['id' => $id],
        array_sum($statusCounts);
    );

    // populate value on specified number of records
    $affectedRows = 0;
    foreach ($statusCounts as $status => $count) {
        $this->db->update(
            'items',
            ['status' => $status],
            ['id' => $id, 'status' => null],
            $count
        );
        $affectedRows += $this->db->affected_rows();
    }
    return $affectedRows;
}

If it matters, the above risks not updating all of the records related to the id. To ensure that all records related to the id are affected, perhaps all of the records should be DELETEd, then INSERTed with the desired values. This would not be advisable if there were other columns in the table because other data would be lost.

Collectives™ on Stack Overflow

Codeigniter update table column with different values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related