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');

女装批发