补0813：sql增删改查

<?php
interface iDb{

    static function insert($db,$table,$data);
    static function select($db,$table,$where,$fields,$order,$skip,$limit);
    static function delete($db,$table,$where=[]);
    static function update($db,$table,$data,$where=[]);

}
abstract class aDb implements iDb {
    protected static $_instance;

    private function __construct()
    {

    }
    static function con_($host='localhost',$user='root',$pwd='root',$db='heima')
    {
        $dsn = 'mysql:host='.$host.';dbname='.$db;
        if(!self::$_instance){

            try {
                $dbh = new PDO($dsn, $user,$pwd, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
            } catch (PDOException $e) {
                echo 'Connection failed: ' . $e->getMessage();
                exit;
            }
            return  $dbh;
        }
    }


    private function __clone()
    {
        // TODO: Implement __clone() method.
    }

}
class Db extends aDb{
    public function ls($data){
        echo "<pre>";
        print_r($data);
    }
    static function insert($db,$table,$data){
        $key_str='';
        $v_str='';
        foreach($data as $key=>$v){
            $key_str.=$key.",";
            $v_str.= gettype($v) === 'string'?"'".$v."',":$v.',';
        }
        $key_str=trim($key_str,',');
        $v_str=trim($v_str,',');
        //判断数据是否为空
        $sql="insert into $table ($key_str) values ($v_str)";
        //echo $sql;
        //INSERT INTO 'heima'.'sp_goods' ('goods_id', 'goods_name', 'goods_price', 'goods_number', 'goods_weight', 'cat_id', 'goods_introduce', 'goods_big_logo', 'goods_small_logo', 'is_del', 'add_time', 'upd_time', 'delete_time', 'cat_one_id', 'cat_two_id', 'cat_three_id', 'hot_mumber', 'is_promote', 'goods_state') VALUES ('1', '南极人女士三角内裤 中腰可爱无痕女士内裤 均码 k102P1027', '49.00', '100', '100', '1191', '<div class=\"lazyimg\"><div moduleid=\"R9000475_3\" modulename=\"商品详情\"><p><img data-src=\"//image.suning.cn/uimg/sop/commodity/138920341921275653753268_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/138920341921275653753268_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/320294018209000122936780_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/320294018209000122936780_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/704928021836697029300000_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/704928021836697029300000_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/674563845781283224909500_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/674563845781283224909500_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/151134588018743970526002_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/151134588018743970526002_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/831318046793946113206000_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/831318046793946113206000_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/916281915185884963655750_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/916281915185884963655750_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/472343017167947876723490_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/472343017167947876723490_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/942767809372154340834000_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/942767809372154340834000_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/163812559664514099011170_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/163812559664514099011170_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/697203950107440612220250_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/697203950107440612220250_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/697247714477543390785500_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/697247714477543390785500_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/161411030914914658667827_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/161411030914914658667827_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/669094843119273038295020_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/669094843119273038295020_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/362990339109906307146490_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/362990339109906307146490_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/200766730097564804937320_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/200766730097564804937320_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/357686216186091569615500_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/357686216186091569615500_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/206539641915557176577986_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/206539641915557176577986_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/106568805613403252031066_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/106568805613403252031066_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/562575204157804367889930_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/562575204157804367889930_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/201136725963941859463860_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/201136725963941859463860_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/174343734111585091167516_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/174343734111585091167516_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/174437664961525990196410_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/174437664961525990196410_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"><img data-src=\"//image.suning.cn/uimg/sop/commodity/852602712102381755560600_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/852602712102381755560600_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"></p></div><div moduleid=\"R9000475_4\" modulename=\"商品尺码表\"><p><img data-src=\"//image.suning.cn/uimg/sop/commodity/191351338116998619431514_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/191351338116998619431514_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"></p></div><div moduleid=\"R9000475_5\" modulename=\"模特效果\"><p><img data-src=\"//image.suning.cn/uimg/sop/commodity/378452992490143925172200_x.jpg?from=mobile&amp;format=80q.webp\" alt=\"\" src=\"//image.suning.cn/uimg/sop/commodity/378452992490143925172200_x.jpg?from=mobile&format=80q.webp\" width=\"100%\" height=\"auto\"></p></div></div>', '', 'http://image1.suning.cn/uimg/b2c/newcatentries/0070081143-000000000145723256_2_200x200.jpg', '0', '1514255862', '1514255862', NULL, '1157', '1174', '1191', '0', '0', '2');
//        echo "<pre>";
//        print_r($db->exec($sql));
        if($db->exec($sql)){
            return $db->lastInsertId();
        }

    }
    static function select($db,$table,$where,$fields='*',$order='',$skip=0,$limit=1000){
        if(is_array($where)){
            foreach ($where as $key => $val) {
                if (is_numeric($val)) {
                    $condition = $key.'='.$val;
                }else{
                    $condition = $key.'=\"'.$val.'\"';
                }
            }
        }else{
            $condition = $where;
        }
        if (!empty($order)) {
            $order = " order by ".$order;
        }
        $sql = "select $fields from $table where $condition $order limit $skip,$limit";
       // echo $sql;

        return $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);

    }
    static function delete($db,$table,$where=[]){
        //不完善   还有or条件没判别 数组组合  字段合并  但大概是这个意思
        $condition='';
        if(is_array($where)){
            foreach ($where as $key => $val) {
                if(is_array($val)){
                    list($a,$b) = $val;
                    if($a == 'like'){
                        $condition.=$key .' like '  .'\'%'.$b.'%\' and ';
                    }else{
                        $condition.= $key.$a.$b.' and ';
                    }
                }else{
                    if (is_numeric($val)) {
                        $condition .= $key.'='.$val.' and';
                    }else{
                        $condition .= $key.'=\"'.$val.'\"  and';
                    }
                }

            }
            $condition=rtrim($condition,' and');

            $sql = "delete from $table where $condition";
            echo $sql;
            echo $db->exec($sql);
        }

    }
    static function update($db,$table,$data,$where=[]){
        //还有  > < != 没判别  还有or
        $str='';
        foreach($data as $key=>$v){
            $str.="$key='$v',";
        }
        $str=rtrim($str,',');
        if(is_array($where)){
            foreach ($where as $key => $val) {
                if(is_array($val)){
                    $condition = $key.' in ('.implode(',', $val) .')';
                } else {
                    $condition = $key. '=' .$val;
                }
            }
        } else {
            $condition = $where;
        }


        $sql="update $table set $str where $condition";
        echo $sql;
        echo $db->exec($sql);

    }
   

}
$obj = Db::con_();
$table = 'sp_goods_cats';
//$data = DB::select($obj,'sp_goods',['goods_id'=>23]);

//$data = ['parent_id'=>0,'cat_name'=>'ces','is_show'=>1,'cat_sort'=>0,'data_flag'=>1,'create_time'=>time()];
//
//$res = Db::delete($obj,$table,['cat_name'=>['like','ce'],'is_show'=>['>',0]]);
//$res = Db::insert($obj,$table,$data);
//echo "<pre>";
//print_r($res);

Db::update($obj,$table,['cat_sort'=>2],['cat_sort'=>[0,1]]);