代码之家 › 专栏 › 技术社区 › SBB

PHP/CodeIgniter-通过数据数组基于多个WHERE条件删除记录

codeigniter mysql php

0

SBB · 技术社区 · 8 年前

我正在为我的项目使用PHP/CodeIgniter,我正在试图找出处理此查询的最佳方法。

我有一组数据要用作 WHERE 用于删除记录的子句。

Array
(
    [0] => Array
        (
            [CustomerID] => 8
            [Denomination] => 40
        )

    [1] => Array
        (
            [CustomerID] => 9
            [Denomination] => 425
        )

)

我正在尝试这样做:

public function remove_customers_from_order($formData){
        $this->db
        ->where($formData)
        ->delete("customer_rewards");
    }

有没有比每次循环并删除更有效的方法? CustomerID 和 Denomination 两者都匹配my WHERE子句的列名。

预期结果:

DELETE FROM customer_rewards WHERE CustomerID = 8 AND Denomination = 40; DELETE FROM customer_rewards WHERE CustomerID = 9 AND Denomination = 425;

错误:

这样运行时,我会遇到以下错误: Array to string conversion database/DB_query_builder.php

3 回复 | 直到 8 年前

1

3

ramin ashrafimanesh 8 年前

I think you can use:
foreach($formData as $w){
    $this->db->or_where("(`CustomerID` = '".$w['CustomerID']."' AND `Denomination`='".$w['Denomination'].')",NULL,false);
}
$this->db->delete();

2

0

Strawberry 8 年前

此查询更高效:

DELETE 
  FROM customer_rewards 
 WHERE (CustomerID = 8 AND Denomination = 40)
    OR (CustomerID = 8 AND Denomination = 425);

你能想出如何相应地调整你的循环吗?

3

0

Atural 8 年前

尽管已经有了公认的答案,但在CI中,这样做的正确方法是:

$arrData = [
    [
        'CustomerID' => 8,
        'Denomination' => 40
    ],
    [
        'CustomerID' => 9,
        'Denomination' => 425
    ]
];

foreach($arrData AS $arrItem)
{
    $this->db->or_group_start();
        $this->db->where('CustomerID', $arrItem['CustomerID']);
        $this->db->where('Denomination', $arrItem['Denomination']);
    $this->db->group_end();
}
$this->db->delete('customer_rewards');

在这种情况下,可以防止SQL注入