一、准备活动
PHP Data Object 数据库访问抽象层 统一各种数据库访问接口
1.查看PHP的配置信息
调用一个函数即可输出一个界面。默认PDO是支持MySQL的
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686b5f556.jpg)
<?php
phpinfo();
如果不支持,在php.ini中打开选项即可
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686bb5a73.jpg)
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)
2.2:方式2 写一个文件决定数据库
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686c173af.jpg)
---->[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)
---->[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;
} 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](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686c6ca52.jpg)
$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](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686ccc449.jpg)
$sql_query = <<<EOT
SELECT * FROM php_pic;
EOT;
$res = $conn->query($sql_query);
foreach ($res as $data) {
print_r($data);
}
打印出记录信息
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686d1b31f.jpg)
$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 方法 查询
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686d1b31f.jpg)
$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](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686dc684e.jpg)
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/>';
}
$conn->setAttribute(键,值)
三、结合表单进行数据库操作
1.前端界面与后端数据接收
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686e3ca13.jpg)
---->[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.查询操作并形成表格
![](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686ea156b.jpg)
---->[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](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686f1fada.jpg)
echo $pic_path.'<br/>';
echo $conn->quote($pic_path);
$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;
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 = [
'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'])) {
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);
}
}
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;
}
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);
return $res;
}
}
private function freeStateIfNotNull(){
if (!empty(self::$PDOStatement)) {
self::$PDOStatement = null;
}
}
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](https://www.leyeah.com/upload/cms-images/2024/05/13/6641686f990ca.jpg)
<?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;';
![](https://www.leyeah.com/upload/cms-images/2024/05/13/66416870159a1.jpg)
4.查询一条
![查询一条.png](https://www.leyeah.com/upload/cms-images/2024/05/13/664168708cb03.jpg)
---->[Pdor::queryRow]-----------------
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
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);
return $res;
}
return false;
}
6.增删改封装 : exec
此方法返回改变的条数rowCount,和插入时的lastInsertId,更新和删除lastInsertId=0;
![](https://www.leyeah.com/upload/cms-images/2024/05/13/66416870d2eda.jpg)
public function exec($sql = null)
{
$conn = self::$conn;
if ($sql != null && $conn) {
$len = $conn->exec($sql);
self::ifErrorHandleSQL($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.单个查询强封装
比如根据指定的键,我想查三列,
![](https://www.leyeah.com/upload/cms-images/2024/05/13/66416871276be.jpg)
$all = $pdor->queryByKey('php_pic', 19, ['pic_path', 'pic_length', 'pic_width']);
print_r($all);
|---- 封装 -------------------------------
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;
}
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
![](https://www.leyeah.com/upload/cms-images/2024/05/13/66416871906dc.jpg)
$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 |
+----+--------------------------------------+------------+----------+-----------+------------+
本篇就这样,其他的,根据字符串拼接的套路自己去玩吧