PHP之sql拼接语句

sql拼接语句

select拼接语句

function select($table,$cond=array()){
    $sql = "select * from `{$table}` where 1";
    if (!empty($cond)){
        foreach ($cond as $k=>$v){
            if (is_array($v)){
                switch ($v[0]){
                    case 'eq':
                        $op='=';
                        break;
                    case 'lt':
                        $op='<';
                        break;
                    case 'gt':
                        $op='>';
                        break;
                    case 'lte':
                    case 'elt':
                        $op='<=';
                        break;
                    case 'gte':
                    case 'egt':
                        $op='>=';
                        break;
                    case 'neq':
                        $op='<>';
                        break;
                }
                $sql.= " and `{$k}`{$op}'{$v[1]}'";
            }else {
                $sql .= " and `{$k}`='{$v}'";
            }
        }
    }
    return $sql;
}
$table = 'goods';
$cond = array(
    'name'=>'钢笔',
    'price'=>'255.00元',
    'aa'=>array('lt',12),
);
echo select($table,$cond);

这只是简单的select拼接

insert拼接

$table = 'goods';
$data = array();
$data['name']='笔记本';
$data['price']='2555.00元';
$data['deal']='2万+';
$keys = array_keys($data);
$keys = array_map(function ($k){
    return "`{$k}`";
},$keys);
$keys = implode(',',$keys);
$values = implode(',',array_map(function ($v){
    return "'{$v}'";
},array_values($data)));
echo "insert into `{$table}` ({$keys}) values ({$values})";

update拼接

$table = 'goods';
$data = array();
$data['name']='笔记本';
$data['price']='2555.00元';
$data['deal']='2万+';
$data['id']='11';
//获取主键
function getPrimaryKey($table){
    $link = mysqli_connect('127.0.0.1','root','root','data','3308');
    mysqli_set_charset($link,'utf8');
    $rs = mysqli_query($link,"desc `{$table}`");
    while ($rows = mysqli_fetch_assoc($rs)){
        if ($rows['Key']=='PRI')
            return $rows['Field'];
    }
}
$pk = getPrimaryKey($table);
$keys = array_keys($data);
$index = array_search($pk,$keys);
unset($keys[$index]);
//$keys=array_keys($data);
$keys = implode(',',array_map(function ($k) use($data){
    return "`{$k}`='{$data[$k]}'";

},$keys));
echo "update `{$table}` set {$keys} where `{$pk}`='{$data[$pk]}'";

array_keys():获取数组的健值
array_values():获取数组的每一个值
array_map():回调函数，返回自己设定的值
implode()：将数组用指定符号分割