php下sql处理函数集合

sql.fn.php

<?php
/**
 * 判断数据保存或插入
 *
 * @author boxcore
 * @date   2016-07-18
 * @param  object     $db    数据库对象
 * @param  string     $table 表名称
 * @param  array      $data  单条数据
 * @param  array      $unque 查询唯一映射
 * @param  array      $skp   跳出特征符，如array('is_loc'=>2), 当获取到目标的数组中
 *                           含有$v['is_loc']= 2 的数据时会跳出更新操作
 */
function save_data_by_check_skp( $db, $table='', $data=array(), $unque=array(), $skp=array(), $is_return=false, $is_insert_time='insert_time'){
    // 检查是否更新操作

    $unque_str = $unque_map = array();
    foreach($unque as $v){
        $unque_str[] = "{$v} => {$data[$v]}";
        $unque_map[$v] = $data[$v];
    }
    $unque_str = join(', ',$unque_str);


    $sql = select_sql($table, $unque_map );
    $data_exist_row = null;
    $data_exist_row = $db->getRow($sql);


    if(!empty($data_exist_row)){
        $skp_t=false;
        if(!empty($skp)){
            foreach($skp as $k=>$v){
                throw_log("test:$k -- $v -- $data_exist_row[$k]");
                if($data_exist_row[$k] == $v) $skp_t=1;
            }

            $print_str = "$table 表 $unque_str 不需要执行更新！";
        }

        if(!$skp_t){
            $sql = update_sql( $table, $data, $unque_map );
            // echo $sql."\n";
            $rs = $db->query($sql);
            $print_str = "$table 表 $unque_str 更新成功！";
        }

    }else{
        // 是否填入默认写入时间
        if($is_insert_time){
            $data[$is_insert_time]  = date('Y-m-d H:i:s');
        }
        
        $sql = insert_sql( $table, $data );
        $rs = $db->query($sql);
        $print_str = "$table 表 $unque_str 添加成功！";
    }

    if($is_return) return $print_str;
    throw_log($print_str);
}

/**
 * 自定义抛出日志格式
 *
 * @author boxcore
 * @date   2015-01-31
 * @param  string     $str
 * @return string
 */
function throw_log($str, $is_return=false){
    $s = '['.date('Y-m-d H:i:s').']';
    $s .= basename($_SERVER['SCRIPT_NAME']);
    $s .= ': ' . trim($str)."\n";
    if($is_return){
        return $s;  
    }
    echo $s;
}


/**
 * 快捷查询
 * @param $table
 * @param $field 字段
 * @param $wheres
 */
function select_sql( $table, $wheres = array(), $field='*') {
    $select_where = array();
    
    if( ! empty( $wheres ) ) {
        foreach( $wheres as $f => $v ) {
            array_push( $select_where, sprintf( '`%s` = "%s"', $f, $v ) );
        }
        $select_where = 'WHERE ' . implode( ' AND ', $select_where );
    } else {
        $select_where = '';
    }
    $select = "SELECT {$field} FROM `{$table}` {$select_where}";
    return $select;
}


/**
 * 快捷批量插入数据
 * @param string $table     表名
 * @param array  $date  二维数组
 * @author boxcore
 * @return string
 */
function insert_batch_sql( $table, $data, $is_addslashes=1, $check_cnt=false) {
    $field_arr = array();
    $value_sql_arr = array();
    $first = true;
    foreach( $data as $d ) {
        $value_arr = array();
        $cur_cnt = count($d);
        if($check_cnt && ($check_cnt!=$cur_cnt)){
            throw_log("存在不合法数据, 已经跳过：");
            print_r($d);
            continue;
        }
        foreach( $d as $field=>$value ) {
            if( $first )  {
                $field_arr[] = $field;
            }
            if($is_addslashes){
                $value = addslashes($value);
            }
            $value_arr[] = sprintf( '"%s"',  $value  );         
        }       
        $value_sql_arr[] = '(' . implode( ',', $value_arr ) . ')';  
        $first = false;
    }
    
    $fields_sql = '`' . implode( '`, `', $field_arr ) . '`';
    $values_sql = implode( ',', $value_sql_arr );
    return "INSERT INTO `{$table}` ( {$fields_sql} ) VALUES {$values_sql}";
}

/**
 * 获取快捷插入数据sql
 * @param $table
 * @param $data
 */
function insert_sql( $table, $data,$is_addslashes=1 ) {
    $insert_fileds = array();
    $insert_data   = array();
    foreach( $data as $field => $value ) {
        if($is_addslashes){
            $value = addslashes($value);
        }
        array_push( $insert_fileds, "`{$field}`" );
        array_push( $insert_data, sprintf( '"%s"', $value ) );
    }
    $insert_fileds = implode( ', ', $insert_fileds );
    $insert_data   = implode( ', ', $insert_data );
    return "INSERT INTO `{$table}` ({$insert_fileds}) values ({$insert_data})";
}

/**
 * 快捷更新表
 * @param $table
 * @param $data
 * @param $wheres
 */
function update_sql( $table, $data, $wheres = array()) {
    $update_data  = array();
    $update_where = array();
    foreach( $data as $field => $value ) {
        array_push( $update_data, sprintf( '`%s` = "%s"', $field, $value ) );
    }
    $update_data  = implode( ', ', $update_data );
    
    if( ! empty( $wheres ) ) {
        foreach( $wheres as $field => $value ) {
            array_push( $update_where, sprintf( '`%s` = "%s"', $field, $value ) );
        }
        $update_where = 'WHERE ' . implode( ' AND ', $update_where );
    } else {
        $update_where = '';
    }
    $update = "UPDATE `{$table}` SET {$update_data} {$update_where}";
    return $update;
}