Update using WHERE

I searched for this topic and didn't get any hits.

I am trying to update multiple rows with the query helper.

There is a column called activity_id which is NOT the primary key.

I am trying to make all the rows that have a specific activity_id to be null so they are unassigned in my program.

Here is a function in my model

PHP Code:

public function set_activity_id_null($activity_id = FALSE)
    {
      
        $data = array(
            'activity_id' => null
        );
        if ($activity_id !== FALSE) {
            $this->where('activity_id', $activity_id);
            $this->update($data);
            return TRUE;
        }
        return false;
    } 


What I am expecting to happen:

UPDATE `billing_charges_2`
SET activity_id = null
WHERE activity_id = 8518

But codeigniter is telling me "no data to update"

I know I am missing something.

Try running it as a SELECT instead of UPDATE and see what results you get.

Like this?

PHP Code:

public function set_activity_id_null($activity_id = FALSE)
    {
        $data = array(
            'activity_id' => null
        );
        if ($activity_id !== FALSE) {
            $this->where('activity_id', $activity_id);
            $this->select($data);
            return TRUE;
        }
        return false;
    } 


this explodes codeigniter even worse.

Thanks for trying though.

---

As a temporary solution I made it a simple query

PHP Code:

$this->db->query('UPDATE '.$this->table.' SET activity_id = null WHERE activity_id = '.$activity_id); 


Seems to work.

https://codeigniter.com/user_guide/model...aving-data

https://codeigniter.com/user_guide/model...aving-data

Thanks, but again nothing on there explains my situation.

Closest thing is this:

PHP Code:

$userModel
    ->whereIn('id', [1,2,3])
    ->set(['active' => 1])
    ->update(); 


Where you can set multiple rows, but you have to pass all the ids in.

In my situation I would have to query the db for all the rows where activity_id = X
Then convert that to an array of the ids and then do the above query.

Thanks though.

From your expectation above;

Code:

UPDATE `billing_charges_2`
SET activity_id = null
WHERE activity_id = 8518

Have you tried it this way?

PHP Code:

public function set_activity_id_null($activity_id = FALSE)
    {
        if ($activity_id !== FALSE) { 
 $this
 ->where('activity_id', $activity_id)
 ->set('activity_id', NULL)
 ->update();
 return TRUE;
        }
 return FALSE;
    } 


I was so sure that was going to work. Totally makes sense... but... same error:

"There is no data to update"

Wild.

I appreciate your efforts!

Thank you.

Did you set $allowedFields in your model?
https://codeigniter.com/user_guide/model...your-model
$allowedFields

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Yes. All fields are in $allowedFields.

Thanks for helping.

I really think Ci4 update() only wants to update with a primary key. Anything outside of that, it explodes.

Did you set $allowedFields in your model?
https://codeigniter.com/user_guide/model...your-model
$allowedFields

Yes. All fields are in $allowedFields.

Thanks for helping.

I really think Ci4 update() only wants to update with a primary key. Anything outside of that, it explodes.

Did you set $allowedFields in your model?
https://codeigniter.com/user_guide/model...your-model
$allowedFields

There's an easy way to prove/disprove your theory....

PHP Code:

echo $this->where('activity_id', $activity_id)->getCompiledSelect();