pdo操作数据库

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

<?php
// 封装一个DB类.用于实现对数据库的增删改查
class Db{
    // 属性私有化
    private $host;    // 数据库主机地址
    private $port;    // 数据库端口
    private $dbname;  // 数据库名
    private $charset; // 字符集
    private $dsn;     // dsn
    private $pdo;     // 数据库pdo对象
    private $table;   // 数据库pdo对象
    private static $obj = null;// 静态变量防止初始化
    // 构造方法私有化,防止类外new
    private function __construct($config=[]){
//      $this->dsn ='mysql:host=localhost;port=3306;dbname=shop;charset=utf8';
        $this->initParam($config);// 初始化参数
        $this->initPdo($config);  // 连接pdo
        $this->table = $config['table'];
 
    }
    // 初始化参数
    private function initParam($config=[]){
        $this->host = $config['host']??"localhost";
        $this->port = $config['port']??"3306";
        $this->dbname = $config['dbname']??"bad";
        $this->charset = $config['charset']??"utf8";
        $this->dsn = "mysql:host={$this->host};port={$this->port};dbname={$this->dbname};charset={$this->charset}";
    }
    // 初始化pdo连接
    private function initPdo($config=[]){
        // 连接数据库
        $this->user = $config['user']??"root";
        $this->pass = $config['pass']??"root";
        try{
            $this->pdo = new PDO($this->dsn,$this->user,$this->pass);
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // 设置模式
        }catch (PDOException $e){
            die($e->getMessage());
        }
 
    }
    // 创建对象
    public static function getInstance($config=[]){
        if(self::$obj == null){
            self::$obj = new self($config);
        }
        return self::$obj;
    }
    private function __clone()
    {
        // TODO: Implement __clone() method.
    }
 
    // 1.添加
    public function add($data){
        $keys  = array_keys($data);
        $field = "";
        $vals = "";
        foreach ($keys as $v){
            $field .=$v.",";
            $vals .= "?,";
        }
        $field = rtrim($field,",");
        $vals = rtrim($vals,",");
        $sql = "insert into {$this->table} (".$field.") values($vals)";
        // 准备预处理
        $stmt = $this->pdo->prepare($sql);
        // 绑定参数
        $n = 1;
        foreach ($data as $v){
            $stmt->bindValue($n,$v);
            $n++;
        }
        // 执行
        return $stmt->execute();
    }
 
    //删除
    public function del($w)
    {
        $sql = "delete from {$this->table} where ";
        $str = "";
        foreach ($w as $k=>$v){
            $str .="$k = ? and ";
        }
        $str = rtrim($str," and ");
        $sql = $sql . $str;
        $stmt = $this->pdo->prepare($sql);
        // 绑定参数
        $n = 1;
        foreach ($w as $v){
            $stmt->bindValue($n,"$v");
            $n++;
        }
        // 执行
        return $stmt->execute();
    }
 
    //修改
    public function update($w1,$w2)
    {
        $str1 = '';
        foreach ($w1 as $k1 => $v1){
            $str1 .="$k1 = ? , ";
        }
        $str1 = rtrim($str1,' , ');
        $str2 = '';
        foreach ($w2 as $k2 => $v2){
            $str2 = "$k2 = ? , ";
        }
        $str2 = rtrim($str2,' , ');
        $sql = "update {$this->table} set {$str1} where {$str2}";
        // 准备预处理
        $stmt = $this->pdo->prepare($sql);
        // 绑定参数
        $n = 1;
        foreach ($w1 as $v1){
            $stmt->bindValue($n,$v1);
            $n++;
        }
        foreach ($w2 as $v2){
            $stmt->bindValue($n,$v2);
            $n++;
        }
        //执行
        $stmt->execute();
        return $stmt->rowCount();
    }
 
    // 根据id来查询
    public function getOneById($id){
        $sql = "select * from {$this->table} where id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(1,$id);
        $stmt->execute();
        return $stmt->fetch();
    }
    // 根据条件来查询
    public function getOne($where){
        $sql = "select * from {$this->table} where ";
        $str = "";
        foreach ($where as $k=>$v){
            $str .="$k = ? and ";
        }
        $str = rtrim($str," and ");
        $sql = $sql . $str;
        // 准备预处理
        $stmt = $this->pdo->prepare($sql);
        // 绑定参数
        $n = 1;
        foreach ($where as $v){
            $stmt->bindValue($n,$v);
            $n++;
        }
        // 执行
        $stmt->execute();
        return $stmt->fetch();
    }
 
    // 搜索
    public function search($where){
        $sql = "select * from {$this->table} where ";
        $str = "";
        foreach ($where as $k=>$v){
            $str .="$k like ? and ";
        }
        $str = rtrim($str," and ");
        $sql = $sql . $str;
        $stmt = $this->pdo->prepare($sql);
        // 绑定参数
        $n = 1;
        foreach ($where as $v){
            $stmt->bindValue($n,"%$v%");
            $n++;
        }
        // 执行
        $stmt->execute();
        return $stmt->fetchAll();
    }
 
    //查询多条
    public function getAll($w){
        $sql = "select * from {$this->table} where ";
        $str = "";
        foreach ($w as $k=>$v){
            $str .="$k = ? and ";
        }
        $str = rtrim($str," and ");
        $sql = $sql . $str;
        $stmt = $this->pdo->prepare($sql);
        // 绑定参数
        $n = 1;
        foreach ($w as $v){
            $stmt->bindValue($n,"$v");
            $n++;
        }
        // 执行
        $stmt->execute();
        return $stmt->fetchAll();
    }
}
//$db = Db::getInstance(["table"=>"book"]);
//$w1 = ['title'=>'333','content'=>'444'];
//$w2 = ['aid' => '5'];
//$row = $db->update($w1,$w2);
//var_dump($row);