1010-个人博客之【数据库访问类封装】
Db.php文件
<?php
// 数据库访问类
class Db{
function __construct(){
$this->pdo = new PDO('mysql:host=127.0.0.1;dbname=myblog','root','root');
}
//指定查询表名方法
public function table($table){//$table接收外部传来的表名
$this->table = $table;
$this->field = '*';
$this->where = [];
$this->order = '';
$this->limit = 0;
$this->insert =[];
return $this;
}
//查询方法
//1.指定查询字段方法
public function field($field){//$table接收外部传来的表名
$this->field = $field;
return $this;
}
//2.指定查询条件方法
public function where($where){//$table接收外部传来的表名
$this->where = $where;
return $this;
}
//3.指定查询条件方法
public function order($order){//$table接收外部传来的表名
$this->order = $order;
return $this;
}
//4.指定查询数量方法
public function limit($limit){//$table接收外部传来的表名
$this->limit = $limit;
return $this;
}
//5.查询一条记录
public function item(){
//$sql = "SELECT {$this->field} FROM `{$this->table}` WHERE `id`=12 and `cid`=2 LIMIT 1";
$sql = $this->_build_sql('select');
$sql .= " LIMIT 1";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$item = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $item ? $item[0] : false;
}
//6.查询不固定多条记录
public function lists(){
$sql = $this->_build_sql('select');exit($sql);
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
//“增加（添加）、删除、改变（更新）”数据 方法
//1.增加数据方法
public function insert($data){
//$sql = "INSERT INTO {$this->table}(`cid`,`title`)VALUES(1,'insert测试')";
$sql = $this->_build_sql('insert',$data);
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
return $this->pdo->lastInsertId();
}
//构造sql语句
private function _build_sql($type,$data = NULL){
if($type == 'select'){
$sql = "SELECT {$this->field} FROM `{$this->table}`";
if($this->where){
$sql .= $this->_build_where_sql();
}
$this->order && $sql .= " order by {$this->order}";
$this->limit && $sql .= " LIMIT {$this->limit}";
}
if($type == 'insert'){
$sql = $this->_build_insert_sql($data);
}
return $sql;
}
//构造where_sql
private function _build_where_sql(){
$sql = '';
$where = '';
foreach ($this->where as $key => $value) {
if(is_string($value)){
$where .= " and `{$key}`='{$value}'";
}else{
$where .= " and `{$key}`={$value}";
}
}
$where = ltrim($where," and");//去掉多余的“空格+and”
$sql .= " WHERE ".$where;//添加上缺的“空格+and+空格”
}
//构造insert_sql
private function _build_insert_sql($data){
$sql = "INSERT INTO {$this->table}";
$fields = $values = [];
foreach ($data as $key => $value) {
$fields[] = '`'.$key.'`';
$values[] = is_string($value) ? "'".$value."'":$value ;
}
$sql .= '('.implode(',',$fields).')VALUES('.implode(',',$values).')';
}
}