<?php
return [
'DB_HOST' => 'localhost',
'DB_NAME' => 'blog',
'DB_USER' => 'root',
'DB_PWD' => '',
];
<?php
include 'config.php';
/**
* Created by PhpStorm.
* User: Administrator
* Date: 2018/5/23
* Time: 15:41
*/
class pdo_sql
{
protected $link;//连接数据库
protected $options;//存放mysql方法的数组
protected $sql;//sql语句
protected $tableName;//数据库表名
/**
* pdo_sql constructor.
*/
public function __construct()
{
$this->initOptions();//清空数组中方法的值
$this->link = $this->connect();//连接数据库方法
}
/**
* 数据库连接
* @return PDO
*/
protected function connect()
{
try{
$link = new PDO('mysql:host='.DB_HOST.'; dbname='.DB_NAME.'; charset=utf8', DB_USER, DB_PWD);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//异常错误模式
return $link;
} catch (Exception $e) {
die('数据库连接失败' . $e->getMessage());
}
}
/**
* 关闭数据库
*/
public function __destruct()
{
$this->link = null;
}
/**
* 外部调用受保护的成员变量时调用
* @param string $name
* @return bool
*/
public function __get($name)
{
if ('sql' == $name) {
return $this->sql;
}
return false;
}
/**
* 清空方法内的值
*/
protected function initOptions()
{
$array = ['table', 'field', 'where', 'order', 'group', 'having', 'limit', 'select'];
foreach ($array AS $key=>$value) {
$this->options[$value] = '';
if ('table' == $value) {
$this->options[$value] = $this->tableName;
}
}
}
/**
* field 方法
* @param string $field
* @return $this
*/
public function field($field='')
{
//如果不为空,传入的是字符串时和传入是数组时得到的结果,为空时,值为*
if (!empty($field)) {
if (is_string($field)) {
$this->options['field'] = $field;
} else if (is_array($field)) {
$this->options['field'] = join(',', $field);
}
} else {
$this->options['field'] = '*';
}
return $this;
}
/**
* where 方法
* @param $where
* @return $this
*/
public function where($where)
{
//传入的值不为空,且为字符串或数组时的得到的结果,
if (!empty($where)) {
if (is_string($where)) {
$this->options['where'] = 'WHERE ' . $where;
} else if (is_array($where)) {
$where = $this->parseValue($where);
$data = [];
foreach ($where AS $key=>$value) {
if ('' == $data) {
$data .= $key . '=' . $value . '';
} else {
$data .= 'AND ' . $key . '=' . $value . '';
}
}
$this->options['where'] = 'WHERE ' . $data;
}
}
return $this;
}
/**
* table 方法
* @param string $table
* @return $this
*/
public function table($table)
{
//不为空是,则为传入的参数
if (!empty($table)) {
$this->options['table'] = $table;
}
return $this;
}
public function order($order)
{
if (!empty($order)) {
$this->options['order'] = 'ORDER BY ' . $order;
}
return $this;
}
/**
* group 方法
* @param $group
* @return $this
*/
public function group($group)
{
if (!empty($group)) {
$this->options['group'] = 'GROUP BY ' . $group;
}
return $this;
}
/**
* having 方法
* @param $having
* @return $this
*/
public function having($having)
{
//传入的值为空和不为空时,得到的不同结果
if (!empty($having)) {
$this->options['having'] = 'HAVING ' . $having;
} else {
$this->options['having'] = $having;
}
return $this;
}
/**
* limit 方法
* @param $limit
* @return $this
*/
public function limit($limit)
{
if (!empty($limit)) {
if (is_string($limit)) {
$this->options['limit'] = 'LIMIT ' . $limit;
} else if (is_array($limit)) {
$this->options['limit'] = 'LIMIT ' . join(',', $limit);
}
}
return $this;
}
/**
* @return array
*/
public function select()
{
//filed为空时,为*
if (empty($this->options['field'])) {
$this->options['field'] = '*';
}
$sql = 'SELECT %FIELD% FROM %TABLE% %WHERE% %HAVING% %ORDER% %GROUP% %LIMIT%';
$sql = str_replace(['%FIELD%', '%TABLE%', '%WHERE%', '%HAVING%', '%ORDER%', '%GROUP%', '%LIMIT%'], [$this->options['field'], $this->options['table'], $this->options['where'], $this->options['having'], $this->options['order'], $this->options['group'], $this->options['limit']], $sql);
$this->sql = $sql;
return $this->query($sql);
}
/**
* insert 方法
* @param array $data
* @return bool|string
*/
public function insert($data)
{
$data = $this->parseValeu($data);//$data为字符串时的处理方法
$keys = array_keys($data);//得到$data数组中的所有键
$values = array_values($data);//得到$data数组中所有的值
$sql = 'INSERT INTO %TABLE%(%FIELD%) values(%VALUES%)';
$sql = str_replace(['%TABLE%', '%FIELD%', '%VALUES%'], [$this->options['table'], join(',', $keys), join(',', $values)], $sql);
$this->sql = $sql;
return $this->exec($sql);
}
/**
* update 方法
* @param array $data
* @return bool|string
*/
public function update($data)
{
$data = $this->parseValeu($data);//$data是字符串时的处理方法
$value = $this->parseUpdate($data);//为$data中的值固定格式
$sql = 'UPDATE %TABLE% SET %VALUE% %WHERE%';
$sql = str_replace(['%TABLE%', '%VALUE%', '%WHERE%'], [$this->options['table'], $value, $this->options['where']], $sql);
$this->sql = $sql;
return $this->exec($sql);
}
/**
* 为$data固定格式
* @param $data
* @return string
*/
protected function parseUpdate($data)
{
//为$data固定定义的格式
foreach ($data AS $key=>$value) {
$newData[] = $key . '=' . $value;
}
return join(',', $newData);
}
/**
* delete 方法
* @return bool|string
*/
public function delete()
{
$sql = 'DELETE FROM %TABLE% %WHERE%';
$sql = str_replace(['%TABLE%', '%WHERE%'], [$this->options['table'], $this->options['where']], $sql);
$this->sql = $sql;
return $this->exec($sql);
}
/**
* query 方法
* @param string $sql
* @return array
*/
protected function query($sql)
{
$this->initOptions();//进入方法时清空数组中的值
$res = $this->link->query($sql);
//如果处理后的结果不为false时,返回一个关联数组的结果集
if (false !== $res) {
$row = $res->fetchAll(PDO::FETCH_ASSOC);
return $row;
} else {
die('查询失败');
}
}
/**
* exec 方法
* @param string $sql
* @param bool $isInsert
* @return bool|string
*/
protected function exec($sql, $isInsert=true)
{
$this->initOptions();//进入方法时清空数组中的值
$res = $this->link->query($sql);
//处理后的结果不为false,且处理插入方法,为true,返回插入的自增id
if (false !== $res) {
if ($isInsert) {
return $this->link->lastInsertId();
} else {
$row = $res->fetchAll(PDO::FETCH_ASSOC);
return $row;
}
}
return true;
}
/**
* 处理$data中的值为字符串时的方法
* @param array $data
* @return mixed
*/
protected function parseValeu($data)
{
//如果数组中的值是字符串,则自动加上引号,然后保存到新的数组中
foreach ($data AS $key=>$value) {
if (is_string($value)) {
$value = '"' . $value . '"';
}
$newData[$key] = $value;
}
return $newData;
}
}<?php
include 'config.php';
/**
* Created by PhpStorm.
* User: Administrator
* Date: 2018/5/23
* Time: 15:41
*/
class pdo_sql
{
protected $link;//连接数据库
protected $options;//存放mysql方法的数组
protected $sql;//sql语句
protected $tableName;//数据库表名
/**
* pdo_sql constructor.
*/
public function __construct()
{
$this->initOptions();//清空数组中方法的值
$this->link = $this->connect();//连接数据库方法
}
/**
* 数据库连接
* @return PDO
*/
protected function connect()
{
try{
$link = new PDO('mysql:host='.DB_HOST.'; dbname='.DB_NAME.'; charset=utf8', DB_USER, DB_PWD);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//异常错误模式
return $link;
} catch (Exception $e) {
die('数据库连接失败' . $e->getMessage());
}
}
/**
* 关闭数据库
*/
public function __destruct()
{
$this->link = null;
}
/**
* 外部调用受保护的成员变量时调用
* @param string $name
* @return bool
*/
public function __get($name)
{
if ('sql' == $name) {
return $this->sql;
}
return false;
}
/**
* 清空方法内的值
*/
protected function initOptions()
{
$array = ['table', 'field', 'where', 'order', 'group', 'having', 'limit', 'select'];
foreach ($array AS $key=>$value) {
$this->options[$value] = '';
if ('table' == $value) {
$this->options[$value] = $this->tableName;
}
}
}
/**
* field 方法
* @param string $field
* @return $this
*/
public function field($field='')
{
//如果不为空,传入的是字符串时和传入是数组时得到的结果,为空时,值为*
if (!empty($field)) {
if (is_string($field)) {
$this->options['field'] = $field;
} else if (is_array($field)) {
$this->options['field'] = join(',', $field);
}
} else {
$this->options['field'] = '*';
}
return $this;
}
/**
* where 方法
* @param $where
* @return $this
*/
public function where($where)
{
//传入的值不为空,且为字符串或数组时的得到的结果,
if (!empty($where)) {
if (is_string($where)) {
$this->options['where'] = 'WHERE ' . $where;
} else if (is_array($where)) {
$where = $this->parseValue($where);
$data = [];
foreach ($where AS $key=>$value) {
if ('' == $data) {
$data .= $key . '=' . $value . '';
} else {
$data .= 'AND ' . $key . '=' . $value . '';
}
}
$this->options['where'] = 'WHERE ' . $data;
}
}
return $this;
}
/**
* table 方法
* @param string $table
* @return $this
*/
public function table($table)
{
//不为空是,则为传入的参数
if (!empty($table)) {
$this->options['table'] = $table;
}
return $this;
}
public function order($order)
{
if (!empty($order)) {
$this->options['order'] = 'ORDER BY ' . $order;
}
return $this;
}
/**
* group 方法
* @param $group
* @return $this
*/
public function group($group)
{
if (!empty($group)) {
$this->options['group'] = 'GROUP BY ' . $group;
}
return $this;
}
/**
* having 方法
* @param $having
* @return $this
*/
public function having($having)
{
//传入的值为空和不为空时,得到的不同结果
if (!empty($having)) {
$this->options['having'] = 'HAVING ' . $having;
} else {
$this->options['having'] = $having;
}
return $this;
}
/**
* limit 方法
* @param $limit
* @return $this
*/
public function limit($limit)
{
if (!empty($limit)) {
if (is_string($limit)) {
$this->options['limit'] = 'LIMIT ' . $limit;
} else if (is_array($limit)) {
$this->options['limit'] = 'LIMIT ' . join(',', $limit);
}
}
return $this;
}
/**
* @return array
*/
public function select()
{
//filed为空时,为*
if (empty($this->options['field'])) {
$this->options['field'] = '*';
}
$sql = 'SELECT %FIELD% FROM %TABLE% %WHERE% %HAVING% %ORDER% %GROUP% %LIMIT%';
$sql = str_replace(['%FIELD%', '%TABLE%', '%WHERE%', '%HAVING%', '%ORDER%', '%GROUP%', '%LIMIT%'], [$this->options['field'], $this->options['table'], $this->options['where'], $this->options['having'], $this->options['order'], $this->options['group'], $this->options['limit']], $sql);
$this->sql = $sql;
return $this->query($sql);
}
/**
* insert 方法
* @param array $data
* @return bool|string
*/
public function insert($data)
{
$data = $this->parseValeu($data);//$data为字符串时的处理方法
$keys = array_keys($data);//得到$data数组中的所有键
$values = array_values($data);//得到$data数组中所有的值
$sql = 'INSERT INTO %TABLE%(%FIELD%) values(%VALUES%)';
$sql = str_replace(['%TABLE%', '%FIELD%', '%VALUES%'], [$this->options['table'], join(',', $keys), join(',', $values)], $sql);
$this->sql = $sql;
return $this->exec($sql);
}
/**
* update 方法
* @param array $data
* @return bool|string
*/
public function update($data)
{
$data = $this->parseValeu($data);//$data是字符串时的处理方法
$value = $this->parseUpdate($data);//为$data中的值固定格式
$sql = 'UPDATE %TABLE% SET %VALUE% %WHERE%';
$sql = str_replace(['%TABLE%', '%VALUE%', '%WHERE%'], [$this->options['table'], $value, $this->options['where']], $sql);
$this->sql = $sql;
return $this->exec($sql);
}
/**
* 为$data固定格式
* @param $data
* @return string
*/
protected function parseUpdate($data)
{
//为$data固定定义的格式
foreach ($data AS $key=>$value) {
$newData[] = $key . '=' . $value;
}
return join(',', $newData);
}
/**
* delete 方法
* @return bool|string
*/
public function delete()
{
$sql = 'DELETE FROM %TABLE% %WHERE%';
$sql = str_replace(['%TABLE%', '%WHERE%'], [$this->options['table'], $this->options['where']], $sql);
$this->sql = $sql;
return $this->exec($sql);
}
/**
* query 方法
* @param string $sql
* @return array
*/
protected function query($sql)
{
$this->initOptions();//进入方法时清空数组中的值
$res = $this->link->query($sql);
//如果处理后的结果不为false时,返回一个关联数组的结果集
if (false !== $res) {
$row = $res->fetchAll(PDO::FETCH_ASSOC);
return $row;
} else {
die('查询失败');
}
}
/**
* exec 方法
* @param string $sql
* @param bool $isInsert
* @return bool|string
*/
protected function exec($sql, $isInsert=true)
{
$this->initOptions();//进入方法时清空数组中的值
$res = $this->link->query($sql);
//处理后的结果不为false,且处理插入方法,为true,返回插入的自增id
if (false !== $res) {
if ($isInsert) {
return $this->link->lastInsertId();
} else {
$row = $res->fetchAll(PDO::FETCH_ASSOC);
return $row;
}
}
return true;
}
/**
* 处理$data中的值为字符串时的方法
* @param array $data
* @return mixed
*/
protected function parseValeu($data)
{
//如果数组中的值是字符串,则自动加上引号,然后保存到新的数组中
foreach ($data AS $key=>$value) {
if (is_string($value)) {
$value = '"' . $value . '"';
}
$newData[$key] = $value;
}
return $newData;
}
}