一、准备活动
PHP Data Object 数据库访问抽象层 统一各种数据库访问接口
1.查看PHP的配置信息
调用一个函数即可输出一个界面。默认PDO是支持MySQL的
| 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); | |
| 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]------------------------------ | |
| <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]------------------------------ | |
| $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]---------------------配置文件-------------- | |
| 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.使用
| 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 | | |
| +----+--------------------------------------+------------+----------+-----------+------------+ |
本篇就这样，其他的，根据字符串拼接的套路自己去玩吧