PHP中批量更新数据表中多条记录

class test {

    /**
     * 创建像这样的查询: "IN("a","b")";
     *
     * @author   wengxianhu
     * @created to 2013-05-27
     * @param    mix      $item_list      列表数组或字符串,如果为字符串时,字符串只接受数字串
     * @param    string   $field_name     字段名称
     * @return   void
     */
    public function db_create_in($item_list, $field_name = "")
    {
        if (empty($item_list))
        {
            return " ".$field_name . " IN ("") ";
        }
        else
        {
            if (!is_array($item_list))
            {
                $item_list = explode(",", $item_list);
                foreach ($item_list as $k=>$v)
                {
                    $item_list[$k] = intval($v);
                }
            }

            $item_list = array_unique($item_list);
            $item_list_tmp = "";
            foreach ($item_list AS $item)
            {
                if ($item !== "")
                {
                    $item_list_tmp .= $item_list_tmp ? ","$item"" : ""$item"";
                }
            }
            if (empty($item_list_tmp))
            {
                return " ".$field_name . " IN ("") ";
            }
            else
            {
                return " ".$field_name . " IN (" . $item_list_tmp . ") ";
            }
        }
    }

    /**
     * 拼接更新字段
     *
     * @author wengxianhu
     * @created to 2013-05-27
     * @param unknown $data 批量更新的数组
     * @param string $index_key 主键值的字段名
     * @return string
     */
    public function _concatFields($data, $index_key)
    {
        if (empty($data)) {
            return "";
        }
        $array_tmp = array();
        $index_key_array = array();
        if (!is_array(current($data))) {
            $data = array($data);
        }
        $tmp1 = "";
        foreach (current($data) as $_v => $_k) {
            $tmp1 = $_v . "_temp";
            ${$tmp1} = "";
            if ($_v != $index_key) {
                ${$tmp1} .= " {$_v} = CASE {$index_key} ";
            }
        }
        reset($data);
        $tmp2 = "";
        foreach ($data as $_k => $_v) {
            foreach ($_v as $_f => $_fv) {
                $tmp2 = $_f . "_temp";
                ${$tmp2} .= "WHEN "{$_v[$index_key]}" THEN "" . addslashes(stripslashes($_fv)) . "" ";
                array_push($index_key_array, $_v[$index_key]);
            }
        }
        reset($data);
        $tmp3 = "";
        foreach (current($data) as $_v => $_k) {
            $tmp3 = $_v . "_temp";
            ${$tmp3} .= " END ";
            if ($_v != $index_key) {
                $array_tmp[$tmp3] = ${$tmp3};
            }
        }
        $array_tmp[$index_key] = $this->db_create_in($index_key_array, $index_key);
        return $array_tmp;
    }

    /**
     * 拼接更新字段
     *
     * @author wengxianhu
     * @created to 2013-05-27
     * @param unknown $data 批量更新的数组
     * @param string $index_key 主键值的字段名
     * @return string
     */
    public function _concatFields_old($data, $index_key)
    {
        if(empty($data))
        {
            return "";
        }
        $array_tmp = array();
        $index_key_array = array();
        if(!is_array(current($data)))
        {
            $data = array($data);
        }
        foreach (current($data) as $_v => $_k)
        {
            if($_v != $index_key)
            {
                ${$_v."_temp"} .= " {$_v} = CASE {$index_key} ";
            }
        }
        reset($data);
        foreach ($data as $_k => $_v)
        {
            foreach ($_v as $_f => $_fv)
            {
                ${$_f."_temp"} .= "WHEN "{$_v[$index_key]}" THEN "".addslashes(stripslashes($_fv))."" ";
                array_push($index_key_array, $_v[$index_key]);
            }
        }
        reset($data);
        foreach (current($data) as $_v => $_k)
        {
            if($_v != $index_key)
            {
                ${$_v."_temp"} .= " END ";
                $array_tmp[$_v."_temp"] = ${$_v."_temp"};
            }
        }
        $array_tmp[$index_key] = $this->db_create_in($index_key_array, $index_key);
        return $array_tmp;
    }

    /**
     * 获取更新的数据SQL
     *
     * @author wengxianhu
     * @created to 2013-05-27
     * @param unknown $data 批量更新的数组
     * @param string $index_key 主键值的字段名
     * @return multitype:
     */
    public function _getUpdateInfo($data, $index_key)
    {
        reset($data);
        $fields = array();
        $conditions = array();
        $fields_info = $this->_concatFields($data, $index_key);
        $conditions = $fields_info[$index_key];
        unset($fields_info[$index_key]);
        $fields = implode(",", $fields_info);
        return compact("fields", "conditions");
    }

    /**
     * 批量更新数据
     *
     * @author wengxianhu
     * @created to 2013-05-27
     * @param unknown $set 批量更新的数组
     * @param string $table 数据表名
     * @param string $index_key 主键值的字段名
     * @return void
     */
    public function updateAll($set, $table, $index_key)
    {
        if(empty($set))
        {
            return "";
        }
        $update_info = $this->_getUpdateInfo($set, $index_key);
        $sql = "UPDATE {$table} SET {$update_info["fields"]} WHERE {$update_info["conditions"]}";
        $this->query($sql,"execute"); //这里根据当前使用的系统修改
    }
}

$goods = array(
    array("goods_id"=>2000,"cate_id"=>100,"name"=>"godos1"),
    array("goods_id"=>2001,"cate_id"=>101,"name"=>"godos2"),
    array("goods_id"=>2002,"cate_id"=>102,"name"=>"godos3"),
);
// $goods = array("goods_id"=>2000,"cate_id"=>100,"name"=>"godos1");
$a = new test();
$a->updateAll($goods, "ecm_goods", "goods_id");

女装批发