一、准备活动
PHP Data Object 数据库访问抽象层 统一各种数据库访问接口
1.查看PHP的配置信息
调用一个函数即可输出一个界面。默认PDO是支持MySQL的
<?php
phpinfo();
如果不支持，在php.ini中打开选项即可
2.连接数据库
2.1：方式1 写死在代码里
|-- ---------------
$dsn = 'mysql:host=localhost;dbname=datatype';//数据源
$user = 'root';
$pwd = 'xxxxx';
$conn = new PDO($dsn, $user, $pwd);
var_dump($conn);//object(PDO)#1 (0) { }
复制代码
2.2：方式2 写一个文件决定数据库
---->[pdo/pdo_conn.php]------------------
$path = __DIR__.'\config.txt';
$dsn = 'uri:file://' . $path . '';//数据源
$user = 'root';
$pwd = 'xxxxx';
$conn = new PDO($dsn, $user, $pwd);
var_dump($conn);//object(PDO)#1 (0) { }
---->[pdo/config.txt]------------------
mysql:dbname=datatype;host=localhost
3.执行语句exec() 创建表
不支持查询操作，返回受影响的行数。数据表使用此文中的pic表：MySQL指南之SQL语句基础
try {
$dsn = 'mysql:host=localhost;dbname=datatype';//数据源
$user = 'root';
$pwd = 'toly';
$conn = new PDO($dsn, $user, $pwd);
//---------------------建表--------------------------
$sql_create_table = <<<EOT
CREATE TABLE IF NOT EXISTS php_pic(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pic_path VARCHAR(120) NOT NULL,
pic_length INT UNSIGNED DEFAULT 0,
pic_mime TINYINT UNSIGNED,
pic_width SMALLINT UNSIGNED,
pic_height SMALLINT UNSIGNED
);
EOT;
$len = $conn->exec($sql_create_table);
echo $len;//0
} catch (Exception $e) {
$e->getMessage();
}
mysql> SHOW TABLES;
+--------------------+
| Tables_in_datatype |
+--------------------+
| php_pic |
+--------------------+
mysql> DESC php_pic;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pic_path | varchar(120) | NO | | NULL | |
| pic_length | int(10) unsigned | YES | | 0 | |
| pic_mime | tinyint(3) unsigned | YES | | NULL | |
| pic_width | smallint(5) unsigned | YES | | NULL | |
| pic_height | smallint(5) unsigned | YES | | NULL | |
+------------+----------------------+------+-----+---------+----------------+
二、增删改查
1.增加记录
//---------------------插入记录--------------------------
$sql_insert = <<<EOT
INSERT INTO pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES
('30000X20000.jpg',116342886,1,30000,20000),
('3000X2000.jpg',3404969,1,3000,2000),
('300X200.jpg',99097,1,300,200),
('30X20.jpg',10158,1,30,20),
('6dc9e8455c47d964e1a8a4ef04cf9477.jpg',236254,1,974,319);
EOT;
$len = $conn->exec($sql_insert);
echo $len;//5
---->[命令行]------------------
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 1 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 2 | 3000X2000.jpg | 3404969 | 1 | 3000 | 2000 |
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 974 | 319 |
+----+--------------------------------------+------------+----------+-----------+------------+
2.修改记录
//---------------------修改记录--------------------------
$sql_update = <<<EOT
UPDATE php_pic SET pic_height=10086,pic_width=2333
WHERE id =5;
EOT;
$len = $conn->exec($sql_update);//1
---->[命令行]------------------
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 1 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 2 | 3000X2000.jpg | 3404969 | 1 | 3000 | 2000 |
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |
+----+--------------------------------------+------------+----------+-----------+------------+
3.删除记录
//---------------------删除记录--------------------------
$sql_delete = <<<EOT
DELETE FROM php_pic
WHERE pic_width> 2500;
EOT;
$len = $conn->exec($sql_delete);//2
echo $len;
---->[命令行]------------------
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |
+----+--------------------------------------+------------+----------+-----------+------------+
关于错误信息的获取
$sql_delete = <<<EOT
DELETE FROM php_picXXX
WHERE pic_width> 2500;
EOT;
$len = $conn->exec($sql_delete);//2
if ($len === false) {
echo $conn->errorCode();
echo "<hr/>";
$err= $conn->errorInfo();
print_r($err);
}
---->[命令行]------------------
mysql> DELETE FROM php_picXXX
-> WHERE pic_width> 2500;
ERROR 1146 (42S02): Table 'datatype.php_picxxx' doesn't exist
4.查询操作：query() 方法
返回一个PDOStatement 对象,可以遍历获取数据
$sql_query = <<<EOT
SELECT * FROM php_pic;
EOT;
$res = $conn->query($sql_query);
foreach ($res as $data) {
print_r($data);
}
打印出记录信息
$sql_query = <<<EOT
SELECT * FROM php_pic;
EOT;
$res = $conn->query($sql_query);
foreach ($res as $data) {
echo "id:" . $data["id"] . "<br/>";
echo "路径: " . $data["pic_path"] . "<br/>";
echo "大小: " . $data["pic_length"] . "<br/>";
echo "类型: " . $data["pic_mime"] . "<br/>";
echo "图片宽: " . $data["pic_width"] . "<br/>";
echo "图片高: " . $data["pic_height"] . "<br/>";
echo "<hr/>";
}
5.通过 prepare 方法 查询
$cursor = $conn->prepare($sql_query);//准备
$res = $cursor->execute();//执行
if ($res) {
while ($data = $cursor->fetch()) {
echo "id:" . $data["id"] . "<br/>";
echo "路径: " . $data["pic_path"] . "<br/>";
echo "大小: " . $data["pic_length"] . "<br/>";
echo "类型: " . $data["pic_mime"] . "<br/>";
echo "图片宽: " . $data["pic_width"] . "<br/>";
echo "图片高: " . $data["pic_height"] . "<br/>";
echo "<hr/>";
}
}
其中fetch可以传入参数，来控制结果的形式，下面举几个小例子
6.获取数据库连接属性
$attr_arr = ['AUTOCOMMIT','ERRMODE','CASE','PERSISTENT','TIMEOUT','ORACLE_NULLS',
'SERVER_INFO','SERVER_VERSION', 'CONNECTION_STATUS',
];
foreach ($attr_arr as $attr) {
$attr="PDO::ATTR_$attr";
echo $attr . "----:";
$attr = constant($attr);
echo $conn->getAttribute($attr) . '<br/>';
}
//PDO::ATTR_AUTOCOMMIT----:1
//PDO::ATTR_ERRMODE----:0
//PDO::ATTR_CASE----:0
//PDO::ATTR_PERSISTENT----:
//PDO::ATTR_TIMEOUT----:
//Warning: PDO::getAttribute(): SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute in J:\PHP\toly\pdo\pdo_conn.php on line 88
//
//PDO::ATTR_ORACLE_NULLS----:0
//PDO::ATTR_SERVER_INFO----:Uptime: 187237 Threads: 2 Questions: 969 Slow queries: 0 Opens: 2033 Flush tables: 1 Open tables: 1004 Queries per second avg: 0.005
//PDO::ATTR_SERVER_VERSION----:5.7.22
//PDO::ATTR_CONNECTION_STATUS----:localhost via TCP/IP
$conn->setAttribute(键，值) # 设置属性
三、结合表单进行数据库操作
1.前端界面与后端数据接收
---->[pdo/form.php]------------------------------
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>添加页面</title>
</head>
<body>
<h1>添加页面</h1>
<form action="do_add_pic.php" method="post">
<label>图片路径：</label>
<input type="text" name="pic_path" placeholder="请输入图片路径"><br>
<label>图片大小：</label>
<input type="number" name="pic_length" placeholder="请输入图片大小"><br>
<label>图片类型：</label>
<select id="select" name="pic_mime">
<option value="png">png</option>
<option value="jpg">jpg/jpeg</option>
</select><br>
<label>图片宽：</label>
<input type="number" name=" pic_width" placeholder=" 图片宽">
<label>图片高：</label>
<input type="number" name=" pic_height" placeholder=" 图片高"><br>
<input type="submit" name="submit">
</form>
</body>
</html>
---->[pdo/do_add_pic.php]------------------------------
<?php
$pic_path = $_POST['pic_path'];
$pic_length = $_POST['pic_length'];
$pic_mime = $_POST['pic_mime'];
$pic_width = $_POST['pic_width'];
$pic_height = $_POST['pic_height'];
$pic_mime = $pic_mime === "png" ? 0 : 1;
echo $pic_path . '<br/>';
echo $pic_length . '<br/>';
echo $pic_mime . '<br/>';
echo $pic_width . '<br/>';
echo $pic_height . '<br/>';
2.将表单信息插入数据库
$dsn = 'mysql:host=localhost;dbname=datatype';//数据源
$user = 'root';
$pwd = 'xxxxx';
$conn = new PDO($dsn, $user, $pwd);
$sql_insert = "INSERT INTO php_pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES
('$pic_path',$pic_length,$pic_mime,$pic_width,$pic_height);";
$exec = $conn->exec($sql_insert);//5
---->[命令行]------------------
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |
| 6 | hello.jpg | 88 | 1 | 99 | 99 |
+----+--------------------------------------+------------+----------+-----------+------------+
3.查询操作并形成表格
---->[pdo/get_pic.php]------------------------------
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>查询页面</title>
</head>
<body>
<h1>查询页面</h1>
<form action="do_find_pic.php" method="post">
<label>图片路径：</label>
<input type="text" name="pic_path" placeholder="请输入图片路径"><br>
<label>图片大小：</label>
<input type="number" name="pic_length" placeholder="请输入图片大小"><br>
<input type="submit" name="获取">
</form>
</body>
</html>
---->[pdo/do_find_pic.php]------------------------------
$pic_path = $_POST['pic_path'];
$pic_length = $_POST['pic_length'];
$dsn = 'mysql:host=localhost;dbname=datatype';//数据源
$user = 'root';
$pwd = 'toly';
$conn = new PDO($dsn, $user, $pwd);
$sql_query = <<<EOT
SELECT * FROM php_pic WHERE pic_path = '$pic_path' AND pic_length= $pic_length;
EOT;
$cursor = $conn->prepare($sql_query);//准备
$res = $cursor->execute();//执行
if ($res) {
$table = "<table border='1' cellspacing='0' cellpadding='0' width='70%' >"
$table .= "<tr/>";
$table .= "<td >id</td>";
$table .= "<td >pic_path</td>";
$table .= "<td >pic_length</td>";
$table .= "<td >pic_mime</td>";
$table .= "<td >pic_width</td>";
$table .= "<td >pic_height</td>";
$table .= "</tr>";
while ($data = $cursor->fetch()) {
$table .= "<tr/>";
$table .= "<td >" . $data["id"] . "</td>";
$table .= "<td >" . $data["pic_path"] . "</td>";
$table .= "<td >" . $data["pic_length"] . "</td>";
$table .= "<td >" . $data["pic_mime"] . "</td>";
$table .= "<td >" . $data["pic_width"] . "</td>";
$table .= "<td >" . $data["pic_height"] . "</td>";
$table .= "</tr>";
}
}
echo $table;
5.SQL注入
也就是用户故意在表单里写入sql语句，导致应用的行为异常，
解决方法很简单，也就是将用户的输入都变成字符串，特殊符号转义
echo $pic_path.'<br/>';//'or 1=1 #
echo $conn->quote($pic_path);//'\'or 1=1 #'
$sql_query = <<<EOT
SELECT * FROM php_pic WHERE pic_path = $pic_path AND pic_length= $pic_length;
EOT;
6.预处理方式的占位参数 放置SQL注入
$sql_query = <<<EOT
SELECT * FROM php_pic WHERE pic_path = :pic_path AND pic_length= :pic_length;
EOT;
$cursor = $conn->prepare($sql_query);//准备
$res = $cursor->execute([':pic_path'=>$pic_path,':pic_length'=>$pic_length]);//执行
接下来的另一种占位形式可谓他乡遇故知啊，和Android一毛一样
$sql_query = <<<EOT
SELECT * FROM php_pic WHERE pic_path =? AND pic_length=?;
EOT;
$cursor = $conn->prepare($sql_query);//准备
$res = $cursor->execute([$pic_path, $pic_length]);//执行
7.参数与变量的绑定
参数绑定到变量好处很明显,变动起来方便
$sql_insert = "INSERT INTO php_pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES
(:pic_path,:pic_length,:pic_mime,:pic_width,:pic_height);";
$state = $conn->prepare($sql_insert);
$state->bindParam(':pic_path', $pic_path, PDO::PARAM_STR);
$state->bindParam(':pic_length', $pic_length, PDO::PARAM_INT);
$state->bindParam(':pic_mime', $pic_mime, PDO::PARAM_INT);
$state->bindParam(':pic_width', $pic_width, PDO::PARAM_INT);
$state->bindParam(':pic_height', $pic_height, PDO::PARAM_INT);
$state->execute();
---->[命令行]------------------
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |
| 6 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 7 | 30000X20000.jpg | 116342886 | 1 | 30000 | 99 |
| 8 | 30000X20000.jpg | 116342886 | 1 | 99 | 99 |
| 9 | hello.jpg | 88 | 1 | 99 | 99 |
| 10 | card.png | 3333 | 0 | 4567 | 7889 |
+----+--------------------------------------+------------+----------+-----------+------------+
|--- 问号型的绑定
$sql_insert = "INSERT INTO php_pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES
(?,?,?,?,?);";
$state = $conn->prepare($sql_insert);
$state->bindParam(1, $pic_path, PDO::PARAM_STR);
$state->bindParam(2, $pic_length, PDO::PARAM_INT);
$state->bindParam(3, $pic_mime, PDO::PARAM_INT);
$state->bindParam(4, $pic_width, PDO::PARAM_INT);
$state->bindParam(5, $pic_height, PDO::PARAM_INT);
$state->execute();
---->[命令行]------------------
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |
| 6 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 7 | 30000X20000.jpg | 116342886 | 1 | 30000 | 99 |
| 8 | 30000X20000.jpg | 116342886 | 1 | 99 | 99 |
| 9 | hello.jpg | 88 | 1 | 99 | 99 |
| 10 | card.png | 3333 | 0 | 4567 | 7889 |
| 11 | toly.png | 5543 | 0 | 4567 | 7889 |
+----+--------------------------------------+------------+----------+-----------+------------+
8.绑定列
这样获取数据会比较方便些
$cursor = $conn->prepare($sql_query);//准备
$res = $cursor->execute([$pic_path, $pic_length]);//执行
$cursor->bindColumn(1, $id_col);
$cursor->bindColumn(2, $pic_path_col);
$cursor->bindColumn(3, $pic_length_col);
$cursor->bindColumn(4, $pic_mime_col);
$cursor->bindColumn(5, $pic_width_col);
$cursor->bindColumn(6, $pic_height_col);
if ($res) {
$table = "<table border='1' cellspacing='0' cellpadding='0' width='70%' >";
$table .= "<tr/>";
$table .= "<td >id</td>";
$table .= "<td >pic_path</td>";
$table .= "<td >pic_length</td>";
$table .= "<td >pic_mime</td>";
$table .= "<td >pic_width</td>";
$table .= "<td >pic_height</td>";
$table .= "</tr>";
while ($cursor->fetch()) {
$table .= "<tr/>";
$table .= "<td >" . $id_col . "</td>";
$table .= "<td >" . $pic_path_col . "</td>";
$table .= "<td >" . $pic_length_col . "</td>";
$table .= "<td >" . $pic_mime_col . "</td>";
$table .= "<td >" . $pic_width_col . "</td>";
$table .= "<td >" . $pic_height_col . "</td>";
$table .= "</tr>";
}
echo $table;
}
四、封装PDO
1. 配置文件:pdo/config.php
---->[pdo/config.php]---------------------配置文件--------------
<?php
define("DB_HOST", "localhost");
define("DB_PORT", "3306");
define("DB_USER", "root");
define("DB_PWD", "xxxxxx");
define("DB_NAME", "datatype");
define("DB_TYPE", "mysql");
define("DB_CHARSET", "utf8");
复制代码
2.封装类：Pdor
属性和构造函数
class Pdor{
private static $config = [];//配置
private static $conn;//连接
private static $pconn = false;//是否支持长连接
private static $dbInfo;//数据信息
private static $connected = false;//是否连接成功
private static $PDOStatement;//PDOStatement
//---------- 单例模式------------------------
private static $INSTANCE;
static function getInstance()
{
if (self::$INSTANCE) {
return self::$INSTANCE;
} else {
self::$INSTANCE = new self();
return self::$INSTANCE;
}
}
private function __construct($config = '')
//---------- 单例模式------------------------
if (!class_exists("PDO")) {
self::throwException("不支持PDO");
return;
}
if (!is_array($config)) {//构造方法未传入配置 ，则使用配置文件构建$config变量
$config = [
'hostname' => DB_HOST,
'hostport' => DB_PORT,
'username' => DB_USER,
'password' => DB_PWD,
'database' => DB_NAME,
'dbms' => DB_TYPE,
'dsn' => DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME,
];
}
if (empty($config['hostname'])) {//构造方法未传入配置,无配置文件
self::throwException("数据库未配置");
return;
}
self::$config = $config;
if (empty(self::$config['params'])) {//params属性为空
self::$config['params'] = [];
}
if (!isset(self::$conn)) {//未连接
$configs = self::$config;
if (self::$pconn) {//设置是否正常长连接
$configs['params'][constant("PDO::ATTR_PERSISTENT")] = true;
}
try {//连接数据库
self::$conn = new \PDO($configs['dsn'], $configs['username'], $configs['password']);
} catch (\Exception $e) {
self::throwException($e->getMessage());
}
if (!self::$conn) {//没连上
self::throwException("连接异常");
return;
}
self::$conn->exec('SET NAMES ' . DB_CHARSET);//设置字符集
self::$dbInfo['version'] = self::$conn->getAttribute(constant('PDO::ATTR_SERVER_VERSION'));
self::$connected = true;
unset($configs);
}
}
/**异常处理
* @param $err
*/
private function throwException($err){
echo "<div style='text-align: center; width:70%;color:#fff;margin: 10px ;padding: 10px ; background-color: red ; border: blue 5px solid ; font-size: larger' > $err</div>";
}
}
2.查询所有封装
public function queryAll($sql = null){
$this->query($sql);
$res = self::$PDOStatement->fetchAll(constant("PDO::FETCH_ASSOC"));
return $res;
}
/** 查询
* @param null $sql
* @return bool
*/
public function query($sql = null){
self::freeStateIfNotNull();
$conn = self::$conn;
if ($sql != null && $conn) {
self::$querySQL = $sql;
self::$PDOStatement = $conn->prepare($sql);
$res = self::$PDOStatement->execute();
self::ifErrorHandleSQL($sql);// 如果sql语句有误 打印
return $res;
}
}
/**
* 释放结果集
*/
private function freeStateIfNotNull(){
if (!empty(self::$PDOStatement)) {
self::$PDOStatement = null;
}
}
/**
* 如果sql语句有误 打印
*/
private function ifErrorHandleSQL($sql){
$err = empty(self::$PDOStatement) ? self::$conn : self::$PDOStatement;
$errArr = $err->errorInfo();
if ($errArr[0] != '00000') {
$err = '错误码：' . $errArr[0] . '<br/>' . 'SQL错误信息 ' . $errArr[2] . '<br/>' . "ERROR ON : $sql";
self::throwException($err);
return false;
}
}
3.使用
<?php
use lib\db\Pdor;
include '../lib/db/Pdor.php';
include './config.php';
$pdor = Pdor::getInstance();
$sql = 'SELECT * FROM php_pic;';
$all = $pdor->queryAll($sql);
print_r($all);
看一下错误的时候：可以自己定义错误的样式
$sql = 'SELECT * FROM php8_pic;';
4.查询一条
---->[Pdor::queryRow]-----------------
/**查询一条数据
* @param null $sql
* @return mixed
*/
public function queryRow($sql = null){
$this->query($sql);
$res = self::$PDOStatement->fetch(constant("PDO::FETCH_ASSOC"));
return $res;
}
|--- 使用
$sql_query_one = 'SELECT * FROM php_pic WHERE id=8;';
$one = $pdor->queryRow($sql_query_one);
print_r($one);
5.增删改封装 ： execute
此方法返回true/false
/**增删改
* @param null $sql
* @return mixed
*/
public function execute($sql = null)
{
$conn = self::$conn;
self::freeStateIfNotNull();
if ($sql != null && $conn) {
self::$PDOStatement = $conn->prepare($sql);
$res = self::$PDOStatement->execute();
self::ifErrorHandleSQL($sql);// 如果sql语句有误 打印
return $res;
}
return false;
}
6.增删改封装 ： exec
此方法返回改变的条数rowCount，和插入时的lastInsertId，更新和删除lastInsertId=0;
/**增删改
* @param null $sql
* @return mixed
*/
public function exec($sql = null)
{
$conn = self::$conn;
if ($sql != null && $conn) {
$len = $conn->exec($sql);//0
self::ifErrorHandleSQL($sql);// 如果sql语句有误 打印
return [
'rowCount' => $len,
'lastInsertId' => $conn->lastInsertId(),
];
}
return false;
}
|--- 使用-----------------------------
$sql_insert = <<<EOT
INSERT INTO php_pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES
('30000X20000.jpg',116342886,1,30000,20000),
('3000X2000.jpg',3404969,1,3000,2000),
('300X200.jpg',99097,1,300,200),
('30X20.jpg',10158,1,30,20),
('6dc9e8455c47d964e1a8a4ef04cf9477.jpg',236254,1,974,319);
EOT;
$all = $pdor->exec($sql_insert);
print_r($all);
你以为这就结束了?is just start !
五、强封装
1.单个查询强封装
比如根据指定的键，我想查三列，
$all = $pdor->queryByKey('php_pic', 19, ['pic_path', 'pic_length', 'pic_width']);
print_r($all);
|---- 封装 -------------------------------
/**
* @param $table 表名
* @param $id 对应值
* @param string $attrs 属性集
* @param string $key 索引
* @return mixed
*/
public function queryByKey($table, $id, $attrs = "*", $key = 'id'){
$sql = "SELECT %s FROM %s WHERE $key = %d";
$sql = sprintf($sql, $this->parseAttrs($attrs), $table, $id);
return $this->queryRow(sprintf($sql));
}
/**
* 解析属性
*/
private function parseAttrs($attrs){
if (is_array($attrs)) {
array_walk($attrs, array('lib\db\Pdor', 'handleAttr'));
$res = implode(',', $attrs);
} else {
$res = "*";
}
return $res;
}
/**通过反引号将属性括起来
* @param $value
* @return string
*/
public static function handleAttr(&$value){
if ($value === '*' || strpos($value, "." !== false || strpos($value, "`") != false)) {
} elseif (strpos($value, "`") == false) {
$value = '`' . trim($value) . '`';
}
return $value;
}
2. WHERE、ORDER 、GROUP、HAVING等语句的支持
来个链式调用装个13
$pdor->query('php_pic')->where("pic_height>500")->where("id>5")->where('pic_width>500')
->order('pic_width DESC')
->ok(['pic_path', 'pic_length', 'pic_width']);
封装起来也挺简单，不过感觉不怎么完美，有时间再推敲推敲
private $sql;
private $table = [];
private $where = [];
private $order = [];
private $having = [];
private $group;
public function submit($attrs = "*")
{
$where = '';
$order = '';
$group = '';
$having = '';
$head = 'SELECT ' . $this->parseAttrs($attrs) . ' FROM ' . $this->table;
if (!empty($this->where)) {
$where = $where . " WHERE ";
}
foreach ($this->where as $str) {
$where .= $str . ' AND ';
}
if (!empty($this->having)) {
$having = $having . " HAVING ";
}
foreach ($this->having as $str) {
$having .= $str . ' AND ';
}
foreach ($this->order as $str) {
$order .= " ORDER BY " . $str . ',';
}
$where = substr($where, 0, -4);
$having = substr($having, 0, -4);
$order = substr($order, 0, -1);
if (!empty($this->group)) {
$group = "GROUP BY " . $this->group;
}
$this->sql = $head . $where . $group . $having . $order . ";";
return $this->queryAll($this->sql);
}
public function query($table)
{
$this->table = $table;
return $this;
}
public function group($attr)
{
$this->group = $attr;
return $this;
}
public function where($where)
{
array_push($this->where, $where);
return $this;
}
public function having($having)
{
array_push($this->having, $having);
return $this;
}
public function order($order)
{
array_push($this->order, $order);
return $this;
}
3.添加方法的数组形式封装
$data = [
'pic_path' => 'hekko.png',
'pic_length' => 1994,
'pic_mime' => 0,
'pic_width' => 3,
'pic_height' => 28,
];
$pdor->add("php_pic", $data);
/**
* 用数组添加
*/
public function add($table, $data)
{
$keys = array_keys($data);//获取键名
array_walk($keys, array('lib\db\Pdor', 'handleAttr'));
$resK = join(",", $keys);
$resV = array_values($data);
foreach ($resV as &$v) {
if (is_string($v)) {
$v = "'" . $v . "'";
}
}
$resV = join(",", $resV);
$sql = "INSERT INTO {$table} ({$resK}) VALUES ({$resV});";
echo $sql;
}
mysql> SELECT * FROM php_pic;
+----+--------------------------------------+------------+----------+-----------+------------+
| id | pic_path | pic_length | pic_mime | pic_width | pic_height |
+----+--------------------------------------+------------+----------+-----------+------------+
| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |
| 6 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 7 | 30000X20000.jpg | 116342886 | 1 | 30000 | 99 |
| 12 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 13 | 3000X2000.jpg | 3404969 | 1 | 3000 | 2000 |
| 14 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 15 | 30X20.jpg | 10158 | 1 | 2333 | 10086 |
| 16 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 974 | 319 |
| 17 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |
| 18 | 3000X2000.jpg | 3404969 | 1 | 3000 | 2000 |
| 19 | 300X200.jpg | 99097 | 1 | 300 | 200 |
| 20 | 30X20.jpg | 10158 | 1 | 30 | 20 |
| 21 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 974 | 319 |
| 22 | hekko.png | 1994 | 0 | 3 | 28 |
+----+--------------------------------------+------------+----------+-----------+------------+
本篇就这样，其他的，根据字符串拼接的套路自己去玩吧
点击查看更多内容
为 TA 点赞
评论
共同学习，写下你的评论
评论加载中...
作者其他优质文章
正在加载中
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦