set_update_batch how to plus 1

PHP Code:

$data = array(
        array(
            'id' => 5,
            'name' => 'test',
            'visit' => '`visit` + 1'
        ),
        array(
            'id' => 7,
            'name' => 'wang',
            'visit' => '`visit` + 1'
        )
    );
$this->Test_model->set_update_batch($data, 'visit', false);
$this->Test_model->update_batch('table', $data, 'id'); 


but is don't work。

when use

PHP Code:

$update = array(
    'id' => 7,
    'name' => 'wang',
);
$this->Test_model->set('visit' , 'visit + 1', false)->update($data); 


it's OK ,can you help me how to use set_update_batch and update_batch

@souicp,

I really don't see the need to use $this->Test_model->set_update_batch();

The issue may be how you declare 'visit' => '`visit` + 1' in your array.

Have you tried this (use of double and single quotes): 'visit' => "'visit' + 1"

@souicp,

I really don't see the need to use $this->Test_model->set_update_batch();

The issue may be how you declare 'visit' => '`visit` + 1' in your array.

Have you tried this (use of double and single quotes): 'visit' => "'visit' + 1"

don't work.
can you help me check it's bugs

@souicp,

Can you show the code? What error message did you receive? Also, have you confirmed that the actual query works (if you were to type it out directly)?

@souicp,

Can you show the code? What error message did you receive? Also, have you confirmed that the actual query works (if you were to type it out directly)?

PHP Code:

        $data = array(
            array(
                'id'         => 5,
                'domainName' => 'facebook.com',
                'view'       => '`view` + 1'
            ),
            array(
                'id'        => 7,
                'domainName' => 'google.com',
                'view'      => '`view` + 1'
            )
        );
        $this->db->set_update_batch($data, 'view', false);
        $this->db->update_batch('icp', $data, 'id'); 
        echo $this->db->last_query();die; 


sql:

Code:

UPDATE `sl_icp`
SET domainName =
CASE
        
        WHEN id = 5 THEN
        facebook.com
        WHEN id = 7 THEN
        google.com ELSE domainName
    END,
    VIEW =
CASE
        
        WHEN id = 5 THEN
        `view` + 1
        WHEN id = 7 THEN
        `view` + 1 ELSE VIEW
    END,
    `domainName` =
CASE
        
        WHEN `id` = 5 THEN
        'facebook.com'
        WHEN `id` = 7 THEN
        'google.com' ELSE `domainName`
    END,
    `view` =
CASE
        
        WHEN `id` = 5 THEN
        '`view` + 1'
        WHEN `id` = 7 THEN
        '`view` + 1' ELSE `view`
    END
WHERE
    `id` IN ( 5, 7, 5, 7 )

but not view plus 1

@souicp,

When you test the sql directly in MySQL does it work? What is 'view'? Is it a field?

@souicp,

When you test the sql directly in MySQL does it work?  What is 'view'?  Is it a field?

yes,
ddl:

Code:

CREATE TABLE `sl_icp` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `domainName` varchar(255) DEFAULT NULL COMMENT 'domain',
 `view` int(11) unsigned NOT NULL DEFAULT '1' COMMENT 'visit',
 PRIMARY KEY (`id`),
 UNIQUE KEY `domain` (`domainName`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='test';

@souicp,

Ok, I see what the challenge is. Normally it would be just view + 1 but because we are doing a batch update and everything is escaped then that is causing the issue. Have you tried ...

'view' => 'view + 1' (This might be seen as a string)
or
'view' => 'view'+ 1 (This might work)
or
'view' => view + 1 (This might throw an error)

It should be one of the answers above.

@souicp,

Ok, I see what the challenge is.  Normally it would be just view + 1 but because we are doing a batch update and everything is escaped then that is causing the issue. Have you tried ...

'view' => 'view + 1'   (This might be seen as a string)
or
'view' => 'view'+ 1    (This might work)
or
'view' => view + 1     (This might throw an error)

It should be one of the answers above.

it's bug
i fix it 

PHP Code:

    public function update_batch($table, $set = NULL, $index = NULL, $key = NULL, $escape = NULL, $batch_size = 100)
    {
        // Combine any cached components with the current statements
        $this->_merge_cache();

        if ($index === NULL)
        {
            return ($this->db_debug) ? $this->display_error('db_must_use_index') : FALSE;
        }

        if ($set === NULL)
        {
            if (empty($this->qb_set_ub))
            {
                return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
            }
        }
        else
        {
            if (empty($set))
            {
                return ($this->db_debug) ? $this->display_error('update_batch() called with no data') : FALSE;
            }

            $this->set_update_batch($set, $index, $key, $escape);
        }

        if (strlen($table) === 0)
        {
            if ( ! isset($this->qb_from[0]))
            {
                return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
            }

            $table = $this->qb_from[0];
        }

        // Batch this baby
        $affected_rows = 0;
        for ($i = 0, $total = count($this->qb_set_ub); $i < $total; $i += $batch_size)
        {
            if ($this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set_ub, $i, $batch_size), $index)))
            {
                $affected_rows += $this->affected_rows();
            }

            $this->qb_where = array();
        }

        $this->_reset_write();
        return $affected_rows;
    } 


PHP Code:

    public function set_update_batch($key, $index = '', $keys = null,  $escape = NULL)
    {
        $key = $this->_object_to_array_batch($key);

        if ( ! is_array($key))
        {
            // @todo error
        }

        is_bool($escape) OR $escape = $this->_protect_identifiers;

        foreach ($key as $k => $v)
        {
            $index_set = FALSE;
            $clean = array();
            foreach ($v as $k2 => $v2)
            {
                if ($k2 === $index)
                {
                    $index_set = TRUE;
                }

                $clean[$k2] = array(
                    'field'  => $this->protect_identifiers($k2, FALSE, ($keys == $k2 ? $escape : NULL)),
                    'value'  => (($escape === FALSE && $keys == $k2) ? $v2 : $this->escape($v2))
                );
            }

            if ($index_set === FALSE)
            {
                return $this->display_error('db_batch_missing_index');
            }

            $this->qb_set_ub[] = $clean;
        }

        return $this;
    } 


PHP Code:

strlen(array_search($k2, $keys)) > 0 


Fix you code

PHP Code:

    $clean[$k2] = array(
                    'field'  => $this->protect_identifiers($k2, FALSE, ( strlen(array_search($k2, $keys)) > 0 ? $escape : NULL)),
                    'value'  => (
                        ($escape === FALSE && strlen(array_search($k2, $keys)) > 0 ) ? $v2 : $this->escape($v2)
                        )
                ); 


Example use 

PHP Code:

     
$productData = [
 'stock' => '`stock` + 1',
 'discount' => '`discount` + 1',
 'idproduct' => '101'
];


$escape_data = ["stock", "discount"];
$this->db->update_batch("product", $productData, 'idproduct', $escape_data, false);