<?php
class DB{
public $host = '127.0.0。1';
public $username = 'root';
public $password = 'root';
public $dbname = 'test';
private $pdo;
static $_instance = null;
public static function getInstance(){
if(null == self::$_instance){
$className = get_called_class();
self::$_instance = new self();
}
return self::$_instance;
}
public function __construct()
{
try{
$dsn = "mysql:dbname={$this->dbname};host={$this->host}";
$this->pdo = new Pdo($dsn, $this->username, $this->password);
// 设置sql语句查询如果出现问题 就会抛出异常
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 设置sql语句查询如果出现问题 就会抛出异常
} catch(PDOException $e){
die("连接失败: ".$e->getMessage());
}
}
/**
* 查询所有的记录
* @param $table 表名
* @param array $wheres 查询的字段
* @param string $rows 查询的字段
* @param string $group 分组条件
* @param string $order 排序条件
* @param string $limit 查询条数限制
* @return mixed 查询的结果
*/
public function query($table,$queryType="all",$wheres=[],$rows='*',$group='',$order='',$limit=''){
try {
//创建预处理sql语句
$where = $wheres ? " WHERE " : '';
$exec = [];
foreach ($wheres as $k=>$v){
$where .= "`{$k}` = :$k AND ";
$exec[":{$k}"] = $v;
}
$where = $where ? rtrim($where,"AND ") : "";
$sql = "SELECT {$rows} FROM `{$table}` {$where}";
if($group!=''){
$sql .= ' GROUP BY '.$group;
}
if($order!=''){
$sql .= ' ORDER BY '.$order;
}
if($limit!=''){
$sql .= ' LIMIT '.$limit;
}
//执行预处理sql语句,创建一个PDOStatement
$pdoStmt = $this->pdo->prepare($sql);
//判断$pdoStmt对象是否创建成功
if ($pdoStmt != true) {
return $this->pdo->errorInfo();//创建失败返回错误语句
}
//执行预处理语句:execute()
$res = $pdoStmt->execute($wheres);
$rows = [];
if ($res == true && ($pdoStmt->rowCount() > 0)) {
while ($row = $pdoStmt->fetch(PDO::FETCH_ASSOC)) {
if($queryType != 'all'){
return $row;
}
$rows[] = $row;
}
}
return $rows;
} catch (PDOException $e) {
return 'Connect ERROR'.$e->getMessage();
}
}
/**
* 插入数据
* @param $table 表名
* @param $data 要插入的数据,eg:array("字段名"=>值)
* @return string 返回刚插入的ID
*/
public function insert($table,$data){
try{
$filed = '';
$value = '';
$exec = [];
//根据要插入的数据,组装预处理的sql语句
foreach ($data as $k => $v){
$filed.= "`{$k}`,";
$value.= "?,";
$exec[] = $v;
}
$filed = rtrim($filed,",");
$value = rtrim($value,",");
$sql = "insert into `{$table}` ({$filed}) values({$value})";
//执行预处理sql语句,创建一个PDOStatement
$stmt = $this->pdo->prepare($sql);
//判断$pdoStmt对象是否创建成功
if ($stmt != true) {
return $this->pdo->errorInfo();//创建失败返回错误语句
}
$stmt->execute($exec);//执行预处理语句:execute()
return $this->pdo->lastInsertId();
}catch (PDOException $e) {
return 'Connect ERROR'.$e->getMessage();
}
}
/**
* 更新数据库的数据
* @param $table 表名
* @param $data 要更新的数据
* @param array $wheres 更新数据的条件
* @return array|int|string 返回更新的条数
*/
public function update($table,$data,$wheres=[]){
try{
//创建预处理sql语句
$set = "";
$where = $wheres ? " WHERE " : '';
$exec=[];
foreach ($data as $k => $v){
$set .= "`{$k}` =:{$k},";
$exec[":{$k}"] = $v;
}
$set = rtrim($set,",");
foreach ($wheres as $key => $value){
$where .= "`{$key}` = :$key AND ";
$exec[":{$key}"] = $value;
}
$where = $where ? rtrim($where,"AND ") : "";
$sql="UPDATE `{$table}` SET {$set} {$where}";
//执行预处理sql语句,创建一个PDOStatement
$stmt = $this->pdo->prepare($sql);
//判断$pdoStmt对象是否创建成功
if ($stmt != true) {
return $this->pdo->errorInfo();//创建失败返回错误语句
}
if($stmt->execute($exec)){
//成功会返回受影响的记录数
return $stmt->rowCount();
}else{
return $stmt->errorInfo();
}
} catch (PDOException $e){
return 'Connect ERROR'.$e->getMessage();
}
}
/**
* 删除数据
* @param $table 表名
* @param array $wheres 删除的条件
* @return array|int|string 返回受影响的记录数
*/
public function del($table,$wheres=[]){
try{
//创建预处理sql语句
$where = $wheres ? " WHERE " : '';
$exec = [];
foreach ($wheres as $k=>$v){
$where .= "`{$k}` = :$k AND ";
$exec[":{$k}"] = $v;
}
$where = $where ? rtrim($where,"AND ") : "";
$sql = "DELETE `{$table}` {$where}";
//执行预处理sql语句,创建一个PDOStatement
$stmt = $this->pdo->prepare($sql);
//判断$pdoStmt对象是否创建成功
if ($stmt != true) {
return $this->pdo->errorInfo();//创建失败返回错误语句
}
if($stmt->execute($exec)){
//成功会返回受影响的记录数
return $stmt->rowCount();
}else{
return $stmt->errorInfo();
}
} catch (PDOException $e){
return 'Connect ERROR'.$e->getMessage();
}
}
/**
* 执行原装的SQL语句查询
* @param $sql sql语句
* @param $exec 预处理的条件
* @return array|string
*/
public function execSql($sql,$exec){
try {
//执行预处理sql语句,创建一个PDOStatement
$pdoStmt = $this->pdo->prepare($sql);
//判断$pdoStmt对象是否创建成功
if ($pdoStmt != true) {
return $this->pdo->errorInfo();//创建失败返回错误语句
}
//执行预处理语句:execute()
$res = $pdoStmt->execute($exec);
$rows = [];
if ($res == true && ($pdoStmt->rowCount() > 0)) {
while ($row = $pdoStmt->fetch(PDO::FETCH_ASSOC)) {
$rows[] = $row;
}
}
return $rows;
} catch (PDOException $e){
return "ERROR:".$e->getMessage();
}
}
}