class test {
/**
* 创建像这样的查询: "IN('a','b')";
*
* @author wengxianhu
* @created to -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 -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 -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 -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 -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');
