CodeIgniter UPDATE查询不使用列值添加-Java 学习之路

在CodeIgniter Update with query builder工作正常，但在我的情况下，我必须以下列方式运行多个更新查询 .

$query="";
foreach($result as $row)    
{
  //generate query by some procedure will look like
  //UPDATE `accounts` SET `lastbal` = lastbal + 500.00 WHERE `id` = 1;
  $balance=$row['balance];
  $accountid=$row['acid];
  //append string
  $query.= "UPDATE `accounts` SET `lastbal` = lastbal + $balance WHERE `id` = $accountid";

}

所以$ query就像在循环之外

$query=UPDATE `accounts` SET `lastbal` = lastbal + 500.00 WHERE `id` = 1;
UPDATE `accounts` SET `lastbal` = lastbal + 200.00 WHERE `id` = 2;
UPDATE `accounts` SET `lastbal` = lastbal + 60.00 WHERE `id` = 3;

Excution

$this->db->query($query);

我得到错误，如缺少参数等等......等等

QUERY在 SQL console 上正常运行 . 是否可以在批处理模式下执行此查询 .

我不想以下面的方式在循环内运行查询

$this->db->set('lastbal', "lastbal+$balance", FALSE);
$this->db->where('id', $acid);
$this->db->update('accounts');

我希望它在批处理模式下，其中column_old_value输入应该可用 .

2 回答

使用活动记录$this->db->update_batch()

$data = array();    

foreach($result as $row)    
{
    $data[] = array(
    'id' => $row['acid'] ,
    'lastbal'=> 'accounts'.'lastbal' +  $row['balance']
    )
}

$this->db->update_batch('accounts', $data, 'id');

回复于 2024-05-06T04:07:03+08:00

如果您不想在循环内运行查询，则必须使用

活动记录$this->db->update_batch()

但不幸的是，在 update_batch 中，今天没有参数选项来禁用转义值，所以

Either

在查询之前使用此行：

// set this to false so that _protect_identifiers skips escaping:

// Codeigniter 3.x.x
$this->db->protect_idenifiers(FALSE);

// Codeigniter 2.x.x
$this->db->_protect_identifiers=FALSE;

这将停止向构建的查询添加反引号 .

并提供如下输入

$data = array();    

foreach($result as $row)    
{
    $data[] = array(
          'id' => $row['acid'] ,

          /* Give input as string */
          'lastbal'=>  'lastbal +' . $row['balance'] 
    )
}

$this->db->update_batch('accounts', $data, 'id'); 

// important to set this back to TRUE or ALL of your queries from now on will be non-escaped:
// Codeigniter 2.x.x
$this->db->_protect_identifiers=TRUE;

// Codeigniter 3.x.x
$this->db->protect_idenifiers(TRUE);

Please note above will not escape your query

Try this by modifying core, it will skip escaping whenever given value is array, so that rest of your query will be escaped with protect_identifiers

https://github.com/bcit-ci/CodeIgniter/blob/develop/system/database/DB_query_builder.php#L2098

Of function

public function set_update_batch($key, $index = '', $escape = NULL)
{
..
..
..
}

From

'value'=>($escape === FALSE ? $v2 : $this->escape($v2))

'value'=>(is_array($v2) ? $v2[0] : ($escape === FALSE ? $v2 : $this->escape($v2)))

并提供如下输入

$data = array();    

foreach($result as $row)    
{
    $data[] = array(
          'id' => $row['acid'] ,

          /* Give input as array */
          'lastbal'=> array( 'lastbal +' . $row['balance'] )
    )
}

$this->db->update_batch('accounts', $data, 'id');

On old CI 2.2.x

Modify DB_active_rec.php

From

if ($escape === FALSE)
            {
                $clean[$this->_protect_identifiers($k2)] = $v2;
            }
            else
            {
                $clean[$this->_protect_identifiers($k2)] = $this->escape($v2);
            }

$clean[$this->_protect_identifiers($k2)] = (is_array($v2)? $v2[0] : ( ($escape === FALSE) ? $v2 : $this->escape($v2) ));

回复于 2024-05-06T04:07:03+08:00

CodeIgniter UPDATE查询不使用列值添加

2 回答

相关问题