php - 更新任意数量的列和 AND 子句的查询

Question

因此，我编写了这段代码来进行通用更新查询，以处理任意数量的列和“AND”子句。

代码不会给出任何错误并且执行正常，但是，在所有列中它更新了错误的值。

我的代码使用最后一个数组键的值更新所有列(在我的示例中，当我运行脚本时，addedby 的值位于所有列中)。

我一直在试图找出为什么会发生这种情况，但无法得出结论。我在这里做错了什么？

我的代码:

    function update($arr,$warr,$table)
{
    global $conn;
    //first get the count of $arr and $warr
    $arrcount = count($arr);
    $warracount = count($warr);
    $arc=0; // $arr counter
    $warc =0; // $warc counter
    $updatestring = "UPDATE $table SET ";
    $wherestring = "WHERE ";
    //make the string now
    foreach($arr as $key=>$value)
    {
        $arc++;
        $st = "$key=:$key";
        if($arc==$arrcount)
        {
            //this is the last one, a comma is not required
            $updatestring = $updatestring . $st;
        }
        else
        {
            $updatestring = $updatestring . $st . ",";
        }
    }

    foreach($warr as $wkey=>$wvalue)
    {
        $warc++;
        $st1 = "$wkey=:$wkey";
        if($warc==$warracount)
        {
            //this is the last, not need for AND
            $wherestring = $wherestring . $st1;
        }
        else
        {
            $wherestring = $wherestring . $st1 . " AND ";
        }
    }
    //now write the update query
    try
    {
        $s = $conn->prepare("$updatestring $wherestring");
        foreach($arr as $key=>$value)
        {
            $s->bindParam(":$key", $value);
        }
        foreach($warr as $wkey=>$wvalue)
        {
            $s->bindParam(":$wkey", $wvalue);
        }
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
    var_dump($s->execute());
}//function
$arr = array("firstname" => "Asas",
                     "lastname" => "Asas",
                     "location" => "Malmo",
                     "phone" => "Asas",
                     "email" => "Asas",
                     "linkedinprofile" => "Asasasas",
                     "employer" => "Asas",
                     "competencies" => "Asas",
                     "address" => "Asaasass",
                     "postcode" => "Asas",
                     "city" => "Asas",
                     "registrationdate" => "Asasaas",
                     "time" => "Asas",
                     "addedby" => "Asaasass");
        $warr = array("indexid" => 14);
var_dump(update($arr,$warr, "contacts"));

Answer 1

很多代码都可以简化，尤其是逗号/和处理。为什么不呢

$arr_params = array();
foreach(array_keys($arr) as $key) {
    $arr_params[] = "$key=:$key";
}
$fields = implode(',', $arr_params);

与 warr 类似，只需使用 ' 和 ' 进行内爆，然后

$sql = "UPDATE $table SET $fields WHERE $where";

现在您不需要重复代码，不需要额外的 if，并检查是否位于循环中的最后一个元素。