/**
* 得到最后执行的SQL语句
* @return bool|null
*/
public static function getLastSQL()
{
$link = self::$link;
if (!$link) return false;
return self::$queryStr;
}
/**
* 得到上一步插入操作产生AUTO_INCREMENT
* @return bool|null
*/
public static function getLastInsertId()
{
$link = self::$link;
if (!$link) return false;
return self::$lastInsertId;
}
/**
* 得到数据库的版本
* @return bool|mixed|null
*/
public static function getDBVersion()
{
$link = self::$link;
if (!$link) return false;
return self::$dbVersion;
}
/**
* 得到数据库中的数据表
* @return array
*/
public static function showTables()
{
$tables = array();
if (self::query("SHOW TABLES")) {
$result = self::getAll();
foreach ($result as $key => $val) {
$tables[$key] = current($val);//输出数组中的当前元素的值
}
}
return $tables;
}
PdoMYSQL.class.func
<?php
class PdoMYSQL
{
public static $config = array();//设置配置参数，配置信息
public static $link = null;//保存连接标识符
public static $pconnect = false;//是否开启长连接
public static $dbVersion = null;//保存数据库版本
public static $connected = false;//判断是否连接成功
public static $PDOStatement = null;//保存PDOStatement对象
public static $queryStr = null;//保存最后执行的操作
public static $error = null;//保存错误信息
public static $lastInsertId = null;//保存上一步插入操作产生AUTO_INCREMENT
public static $numRows = 0;//上一步操作产生受影响的记录的条数
public function __construct($dbConfig = '')
{
if (!class_exists("PDO")) {
self::throw_exception('不支持PDO，请先开启');
}
if (!is_array($dbConfig)) {
$dbConfig = array(
'hostname' => DB_HOST,
'username' => DB_USER,
'password' => DB_PWD,
'database' => DB_NAME,
'hostport' => DB_PORT,
'dsn' => DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME);
}
if (empty($dbConfig['hostname']))
self::throw_exception('没有定义数据库配置，请先定义');
self::$config = $dbConfig;
if (empty(self::$config['params']))
self::$config['params'] = array();
if (!isset(self::$link)) {
$configs = self::$config;
if (self::$pconnect) {
//开启长连接，添加到配置数组中
$configs['params'][constant("PDO::ATTR_PERSISTENT")] = true;
}
try {
self::$link = new PDO($configs['dsn'], $configs['username'], $configs['password'], $configs['params']);
} catch (PDOException $e) {
self::throw_exception($e->getMessage());
}
if (!self::$link) {
self::throw_exception('PDO连接错误');
return false;
}
self::$link->exec('SET NAMES ' . DB_CHARSET);
self::$dbVersion = self::$link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
self::$connected = true;
unset($configs);
}
}
/**得到所有记录
* @param null $sql
* @return mixed
*/
public static function getAll($sql = null)
{
if ($sql != null) {
self::query($sql);
}
$result = self::$PDOStatement->fetchAll(constant("PDO::FETCH_ASSOC"));
return $result;
}
/**
* 得到结果集的一条记录
* @param null $sql
* @return mixed
*/
public static function getRow($sql = null)
{
if ($sql != null) {
self::query($sql);
}
$result = self::$PDOStatement->fetch(constant("PDO::FETCH_ASSOC"));
return $result;
}
/** 根据主键查找记录
* @param $tabName
* @param $priId
* @param string $fields
* @return mixed
*/
public static function findById($tabName, $priId, $fields = '*')
{
$sql = 'SELECT %s FROM %s WHERE id=%d;';
return self::getRow(sprintf($sql, self::parseFields($fields), $tabName, $priId));
}
/**
* 执行普通查询
* @param $tables
* @param null $where
* @param string $fields
* @param null $group
* @param null $having
* @param null $order
* @param null $limit
* @return mixed
*/
public static function find($tables, $where = null, $fields = '*', $group = null, $having = null, $order = null, $limit = null)
{
$sql = 'SELECT ' . self::parseFields($fields) . ' FROM ' . $tables . ' '
. self::parseWhere($where)
. self::parseGroup($group)
. self::parseHaving($having)
. self::parseOrder($order)
. self::parseLimit($limit);
$dataAll = self::getAll($sql);
return count($dataAll) == 1 ? $dataAll[0] : $dataAll;
}
/**
* array(
* 'username'=>'imoocccc,
* 'password'=>'imooc222',
* 'email'=>'imooc111@imooc.com'
* )
* update user set username='imoocccc'......where id<=38
* order by username limit 3
*/
/** 更新记录
* @param $data
* @param $table
* @param null $where
* @param null $order
* @param null $limit
* @return bool|int
*/
public static function update($data, $table, $where = null, $order = null, $limit = null)
{
$sets = '';
foreach ($data as $key => $val) {
$sets .= $key . "='" . $val . "',";
}
//echo $sets;//username='imooc111',password='imooc212',email='imooc222@imooc22.com',
$sets = rtrim($sets, ',');
$sql = "UPDATE {$table} SET {$sets} " . self::parseWhere($where) . self::parseOrder($order) . self::parseLimit($limit);
return self::execute($sql);
}
/**
* 删除记录的操作
* @param $table
* @param null $where
* @param null $order
* @param int $limit
* @return bool|int
*/
public static function delete($table, $where = null, $order = null, $limit = 0)
{
$sql = "DELETE FROM {$table} " . self::parseWhere($where) . self::parseOrder($order) . self::parseLimit($limit);
return self::execute($sql);
}
/**
* 得到最后执行的SQL语句
* @return bool|null
*/
public static function getLastSQL()
{
$link = self::$link;
if (!$link) return false;
return self::$queryStr;
}
/**
* 得到上一步插入操作产生AUTO_INCREMENT
* @return bool|null
*/
public static function getLastInsertId()
{
$link = self::$link;
if (!$link) return false;
return self::$lastInsertId;
}
/**
* 得到数据库的版本
* @return bool|mixed|null
*/
public static function getDBVersion()
{
$link = self::$link;
if (!$link) return false;
return self::$dbVersion;
}
/**
* 得到数据库中的数据表
* @return array
*/
public static function showTables()
{
$tables = array();
if (self::query("SHOW TABLES")) {
$result = self::getAll();
foreach ($result as $key => $val) {
$tables[$key] = current($val);//输出数组中的当前元素的值
}
}
return $tables;
}
/**
* 销毁连接对象，关闭数据库
*/
public static function close()
{
self::$link = null;
}
/**
* 解析where条件
* @param $where
* @return string
*/
public static function parseWhere($where)
{
$whereStr = '';
if (is_string($where) && !empty($where)) {
$whereStr = $where;
}
return empty($whereStr) ? '' : ' WHERE ' . $whereStr;
}
/**
* 添加记录的操作
* array(
* 'username'=>'imooc',
* 'password'=>'imooc',
* 'email'=>'email'
* )
*
* INSERT user(username,password,email) VALUES('aa','aa','aa@aa.com');
*/
public static function add($data, $table)
{
$keys = array_keys($data);
array_walk($keys, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = join(',', $keys);
$values = "'" . join("','", array_values($data)) . "'";
$sql = "INSERT {$table}({$fieldsStr}) VALUES({$values});";
//echo $sql;
return self::execute($sql);
}
/**
* 解析group by
* @param $group
* @return string
*/
public static function parseGroup($group)
{
$groupStr = '';
if (is_array($group)) {
$groupStr .= ' GROUP BY' . implode(',', $group);
} else if (is_string($group) && !empty($group)) {
$groupStr .= ' GROUP BY ' . $group;
}
return empty($groupStr) ? '' : $groupStr;
}
/**
* 对分组结果通过Having子句进行二次删选
* @param $having
* @return string
*/
public static function parseHaving($having)
{
$havingStr = '';
if (is_string($having) && !empty($having)) {
$havingStr .= ' HAVING ' . $having;
}
return $havingStr;
}
/**
* 解析order by
* @param $order
* @return mixed
*/
public static function parseOrder($order)
{
$orderStr = '';
if (is_array($order)) {
$orderStr = ' ORDER BY ' . join(',', $order);
} elseif (is_string($order) && !empty($order)) {
$orderStr .= ' ORDER BY ' . $order;
}
return $orderStr;
}
/**
* 解析显示显示条件
* limit 3
* limit 0,3
* @param $limit
* @return string
*/
public static function parseLimit($limit)
{
$limitStr = '';
if (is_array($limit)) {
if (count($limit) > 1) {
$limitStr .= ' LIMIT ' . $limit[0] . ',' . $limit[1];
} else {
$limitStr .= ' LIMIT ' . $limit[0];
}
} elseif (is_string($limit) && !empty($limit)) {
$limitStr .= ' LIMIT ' . $limit;
}
return $limitStr;
}
/**
* 解析字段
* @param $fields
* @return string
*/
public static function parseFields($fields)
{
if (is_array($fields)) {
array_walk($fields, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = implode(',', $fields);
} else if (is_string($fields) && !empty($fields)) {
if (strpos($fields, '^') === false) {
//0==false 0:position
$fields = explode(',', $fields);
array_walk($fields, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = implode(',', $fields);
} else {
$fieldsStr = $fields;
}
} else {
$fieldsStr = '*';
}
return $fieldsStr;
}
/**
* 通过反引号引用字段
* @param $value
*/
public static function addSpecilChar(&$value)
{
if ($value == '*' || strpos($value, '.') !== false || strpos($value, '^') !== false) {
//不用做处理
} elseif (strpos($value, '^') === false) {
//$value = '^' . trim($value) . '^';
$value = '' . trim($value) . '';
}
}
/**执行增删改操作，返回受影响的记录的条数
* @param null $sql
* @return bool|int
*/
public static function execute($sql = null)
{
$link = self::$link;
if (!$link) return false;
self::$queryStr = $sql;
if (!empty(self::$PDOStatement)) self::free();
$result = $link->exec(self::$queryStr);
self::haveErrorThrowException();
if ($result) {
self::$lastInsertId = $link->lastInsertId();
self::$numRows = $result;
return self::$numRows;
} else {
return false;
}
}
/**
* 释放结果集
*/
public static function free()
{
self::$PDOStatement = null;
}
public static function query($sql = '')
{
$link = self::$link;
if (!$link) return false;
//判断之前是否有结果集，如果有，释放结果集
if (!empty(self::$PDOStatement)) self::free();
self::$queryStr = $sql;
self::$PDOStatement = $link->prepare(self::$queryStr);
$res = self::$PDOStatement->execute();
self::haveErrorThrowException();
return $res;
}
public static function haveErrorThrowException()
{
$obj = empty(self::$PDOStatement) ? self::$link : self::$PDOStatement;
$arrError = $obj->errorInfo();
/**
* Array
* (
* [0] => 42S02
* [1] => 1146
* [2] => Table 'test.user1' doesn't exist
* )
*/
// print_r($arrError);
if ($arrError[0] != '00000') {
self::$error = 'SQLSTATE ' . $arrError[0] . 'SQL Error: ' . $arrError[2] . '<br/>Error SQL: ' . self::$queryStr;
self::throw_exception(self::$error);
return false;
}
if (self::$queryStr == '') {
self::throw_exception('没有执行SQL语句');
return false;
}
}
/**
* 自定义错误处理
* @param $errMsg
*/
public
static function throw_exception($errMsg)
{
echo '<div style="width:80%;background-color:#ABCDEF;color: black;font-size: 20px;padding: 20px 0px">
' . $errMsg . '</div>';
}
}
require_once 'config.php';
$PdoMySQL = new PdoMYSQL();
//var_dump($PdoMySQL);//object(PdoMYSQL)[1]
//$sql = 'select * from user';
//print_r($PdoMySQL::getAll($sql));
//$sql = 'SELECT * FROM user WHERE id>4';
//print_r($PdoMySQL->getRow($sql));//Array ( [id] => 13 [username] => king4 [password] => king1 [email] => imoooc1@qq.com )
//$sql = 'INSERT user(username,password,email) ';
//$sql .= "VALUES('imooc111','imooc122','imooc@imooc.com')";
//var_dump($PdoMySQL::execute($sql));//int 1
//echo $PdoMySQL::$lastInsertId;//65
//$sql = 'DELETE FROM user WHERE id>15';
//var_dump($PdoMySQL::execute($sql));//int 22
//$sql = 'UPDATE user SET username="king1234" WHERE id=14';
//var_dump($PdoMySQL::execute($sql));//int 1
//$tabName = 'user';
//$priId = '3';
//print_r($PdoMySQL::findById($tabName, $priId));
//$tabName = 'user';
//$priId = '14';
////$fields = 'username,email';
//$fields = array('username', 'email');
//print_r($PdoMySQL::findById($tabName, $priId, $fields));
$tables = 'user';
//print_r($PdoMySQL::find($tables));
//print_r($PdoMySQL::find($tables,'id>=13'));
//print_r($PdoMySQL::find($tables,'id>=1','username,email'));
//print_r($PdoMySQL::find($tables, 'id>=1', '*,count(*) as total', 'email', 'count(*)>=2'));
//print_r($PdoMySQL::find($tables, 'id>5', '*', null, null, 'username DESC,id DESC'));
//print_r($PdoMySQL::find($tables, null, '*', null, null, null, '3,3'));
//print_r($PdoMySQL::find($tables, null, '*', null, null, null, array(2, 5)));
//$data = array(
// 'username' => 'imooct2g',
// 'password' => 'imooc',
// 'email' => 'imooc@imooc.com'
//);
//$PdoMySQL::add($data, 'user');
//$data = array(
// 'username' => 'imooc181',
// 'password' => 'imooc212',
// 'email' => 'imooc222@imooc22.com'
//);
//var_dump($PdoMySQL::update($data, $tables, 'id=15', 'id DESC'));
//var_dump($PdoMySQL::delete($tables, 'id>35'));
//var_dump($PdoMySQL::delete($tables, 'id>3', 'id DESC', '5'));
print_r($PdoMySQL::showTables());
Paste_Image.png
