trying to use batch_update()

I am trying to update a field in all of the record showing with this:

$this->db->where('email', $_SESSION['userid']);
$this->db->where('campaign', $_SESSION['campaign']);
$data[] = array(
    'in_sample' => 0
);
$this->db->update_batch('employees', $data);

The where()'s are used to make sure that I only update this user's data since this is a multi-user system. When I run the update_batch() line I get this error. In sample is an int btw. 

A Database Error Occurred
You must specify an index to match on for batch updates.

Filename: models/MyModel.php
Line Number: 143



I have set in_sample to be indexed in the database.

proof that an old dog can learn new tricks

That doesn't look like a batch_update type query.. batch update is when you want to update multiple rows at once (and set different values for different rows).

If you are just trying to update multiple rows (and set the same values for each) you can just use a normal update query with the where conditions.

You should index email and campaign fields in the employees table.
Also what kind of database?

OK. I switched over to this:
        $this->db->where('email', $_SESSION['userid']);
        $this->db->where('campaign', $_SESSION['campaign']);
        $data[] = array(
            'in_sample' => 0
        );
        $this->db->update('employees', $data);

        $this->db->where('in_population', 0);  //ie not excluded
        $sql = "SELECT * FROM employees ORDER BY RAND() LIMIT " . $count;
        $data[] = array(
            'in_sample' => 1
        );
        $query = $this->db->query($sql);
        $this->db->update('employees', $data);

What I am trying to do is first set all OF THE in_sample=0. Then I am trying to pick a random sample of 30 of them and set the chosen rows to in_sample=1. $count=30. 

When I run this I get the following error:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 = Array WHERE `email` = '[email protected]' AND `campaign` = 'Apple'' at line 1

UPDATE `employees` SET 0 = Array WHERE `email` = '[email protected]' AND `campaign` = 'Apple'

Filename: models/MyModel.php

Line Number: 148

Am I calling update incorrectly?

proof that an old dog can learn new tricks

MySQL

proof that an old dog can learn new tricks

Solved one issue.  I defined the $data array as $data[] when it should have been $data.

proof that an old dog can learn new tricks

I am still having one more issue.
  $sql = "SELECT * FROM employees ORDER BY RAND() LIMIT " . $count;
        $data = array(
            'in_sample' => 1
        );
        $query = $this->db->query($sql);

        $this->db->set($data);
        $this->db->update('employees');

The query seems to work OK. When I take a look at the result(), I can see that 30 rows are chosen. But when update line runs, it is updating all the rows, not just the 30 that have been filtered. I can see that ALL the rows have in_sample set to one.  Anyone know what I am doing wrong in SQL? Additionally, does anyone know what the seed is for RAND()?

Perhaps I need to loop through the results and update them manually, one at a time?

proof that an old dog can learn new tricks

You're making 2 separate queries, the SELECT isn't being used for the UPDATE query.

I haven't used CI3 in a while, but perhaps you can try something like this.

PHP Code:

$this->db
    ->set($data)
    ->order_by('RAND()')
    ->limit($count)
    ->update('employees'); 


I switched it over to:
            $data = array(
                'in_population' => 1
            );
            $this->db->where('email', $_SESSION['userid']);
            $this->db->where('campaign', $_SESSION['campaign']);
            $this->db->or_where('employee_title', $exclude_string);
            $this->db->or_where('job_code', $exclude_string);
            $this->db->or_where('department', $exclude_string);
            $this->db->or_where('role', $exclude_string);
            $this->db->update('employees',$data );

I am hoping this means that if the email matches and the campaign matches, then if my exclude string appear in employee_title, job_code, department, or role, then update in_population to 1. (this means they are NOT in the population, btw).  Is there someway to see how many updates were done? This is currently not updating any. Is there something wrong with my logic?

I saw a post where "where_in" is being used to check a few results in a single field. I am doing the opposite. I have one string that I want to see if it appears in any of 4 fields. I expected that if one of those fields matched my exclude_string, "in_population" would get set to 1. But this doesn't seems to update any of the rows. I am sure that some of those rows should be updated. I expected that the first two where's are being ANDed with the 4 or_wheres? Is this a correct assumption?

proof that an old dog can learn new tricks

In reading about group query I found this. But this is still not working any ideas?
````
            $this->db->select('*')->from('employees')
                ->group_start()
                ->where('email', $_SESSION['userid'])
                ->where('campaign', $_SESSION['campaign'])
                    ->or_group_start()
                    ->where('employee_title',  $exclude_string)
                    ->where('job_code',  $exclude_string)
                    ->where('department',  $exclude_string)
                    ->where('role',  $exclude_string)
                    ->group_end()
                ->group_end();
            $this->db->update('employees',$data );
````

proof that an old dog can learn new tricks