阅读 684

PHP中的MySQL使用--基于PDO

个人所有文章整理在此篇,将陆续更新收录:知无涯,行者之路莫言终(我的编程之路)

一、准备活动

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 |
+----+--------------------------------------+------------+----------+-----------+------------+
复制代码

关于错误信息的获取

错误信息的捕捉.png

    $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 对象,可以遍历获取数据

查询结果.png

    $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可以传入参数,来控制结果的形式,下面举几个小例子

数据返回形式.png


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语句,导致应用的行为异常,
解决方法很简单,也就是将用户的输入都变成字符串,特殊符号转义

sql注入.png

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.使用

获取数据.png

<?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.查询一条

查询一条.png

---->[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 |
+----+--------------------------------------+------------+----------+-----------+------------+
复制代码

本篇就这样,其他的,根据字符串拼接的套路自己去玩吧

关注下面的标签,发现更多相似文章
评论