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 |
+----+--------------------------------------+------------+----------+-----------+------------+

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

PHP中的MySQL使用--基于PDO

一、准备活动

1.查看PHP的配置信息

2.连接数据库

2.1：方式1 写死在代码里

2.2：方式2 写一个文件决定数据库

3.执行语句exec() 创建表

二、增删改查

1.增加记录

2.修改记录

3.删除记录

4.查询操作：query() 方法

5.通过 prepare 方法 查询

6.获取数据库连接属性

三、结合表单进行数据库操作

1.前端界面与后端数据接收

2.将表单信息插入数据库

3.查询操作并形成表格

5.SQL注入

6.预处理方式的占位参数 放置SQL注入

7.参数与变量的绑定

8.绑定列

四、封装PDO

1. 配置文件:pdo/config.php

2.封装类：Pdor

2.查询所有封装

3.使用

4.查询一条

5.增删改封装 ： execute

6.增删改封装 ： exec

五、强封装

1.单个查询强封装

2. WHERE、ORDER 、GROUP、HAVING等语句的支持

3.添加方法的数组形式封装

3.执行语句`exec()` 创建表

5.通过 prepare 方法查询

6.预处理方式的占位参数放置SQL注入

2.封装类：`Pdor`

5.增删改封装： `execute`

6.增删改封装： `exec`