php mysql cursor_PHP中的MySQL使用--基于PDO

最新推荐文章于 2021-03-10 23:20:21 发布
最新推荐文章于 2021-03-10 23:20:21 发布
阅读量283 收藏
点赞数
文章标签： php mysql cursor
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39858298/article/details/113259190
版权

一、准备活动

PHP Data Object 数据库访问抽象层 统一各种数据库访问接口

1.查看PHP的配置信息

调用一个函数即可输出一个界面。默认PDO是支持MySQL的

AAffA0nNPuCLAAAAAElFTkSuQmCC

phpinfo();

如果不支持，在php.ini中打开选项即可

AAffA0nNPuCLAAAAAElFTkSuQmCC

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 写一个文件决定数据库

AAffA0nNPuCLAAAAAElFTkSuQmCC

---->[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 = <<

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

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

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

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 |

+----+--------------------------------------+------------+----------+-----------+------------+

关于错误信息的获取

AAffA0nNPuCLAAAAAElFTkSuQmCC

$sql_delete = <<

DELETE FROM php_picXXX

WHERE pic_width> 2500;

EOT;

$len = $conn->exec($sql_delete);//2

if ($len === false) {

echo $conn->errorCode();

echo "

";

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

AAffA0nNPuCLAAAAAElFTkSuQmCC

$sql_query = <<

SELECT * FROM php_pic;

EOT;

$res = $conn->query($sql_query);

foreach ($res as $data) {

print_r($data);

}

打印出记录信息

AAffA0nNPuCLAAAAAElFTkSuQmCC

$sql_query = <<

SELECT * FROM php_pic;

EOT;

$res = $conn->query($sql_query);

foreach ($res as $data) {

echo "id:" . $data["id"] . "
";

echo "路径: " . $data["pic_path"] . "
";

echo "大小: " . $data["pic_length"] . "
";

echo "类型: " . $data["pic_mime"] . "
";

echo "图片宽: " . $data["pic_width"] . "
";

echo "图片高: " . $data["pic_height"] . "
";

echo "

";

}

5.通过 prepare 方法 查询

AAffA0nNPuCLAAAAAElFTkSuQmCC

$cursor = $conn->prepare($sql_query);//准备

$res = $cursor->execute();//执行

if ($res) {

while ($data = $cursor->fetch()) {

echo "id:" . $data["id"] . "
";

echo "路径: " . $data["pic_path"] . "
";

echo "大小: " . $data["pic_length"] . "
";

echo "类型: " . $data["pic_mime"] . "
";

echo "图片宽: " . $data["pic_width"] . "
";

echo "图片高: " . $data["pic_height"] . "
";

echo "

";

}

}

其中fetch可以传入参数，来控制结果的形式，下面举几个小例子

AAffA0nNPuCLAAAAAElFTkSuQmCC

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) . '
';

}

//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.前端界面与后端数据接收

AAffA0nNPuCLAAAAAElFTkSuQmCC

---->[pdo/form.php]------------------------------

添加页面

添加页面

图片路径：

图片大小：

图片类型：

png

jpg/jpeg

图片宽：

图片高：

---->[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 . '
';

echo $pic_length . '
';

echo $pic_mime . '
';

echo $pic_width . '
';

echo $pic_height . '
';

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.查询操作并形成表格

AAffA0nNPuCLAAAAAElFTkSuQmCC

---->[pdo/get_pic.php]------------------------------

查询页面

查询页面

图片路径：

图片大小：

---->[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 = <<

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 .= "

";

$table .= "

id";

$table .= "

pic_path";

$table .= "

pic_length";

$table .= "

pic_mime";

$table .= "

pic_width";

$table .= "

pic_height";

$table .= "";

while ($data = $cursor->fetch()) {

$table .= "

";

$table .= "

" . $data["id"] . "";

$table .= "

" . $data["pic_path"] . "";

$table .= "

" . $data["pic_length"] . "";

$table .= "

" . $data["pic_mime"] . "";

$table .= "

" . $data["pic_width"] . "";

$table .= "

" . $data["pic_height"] . "";

$table .= "";

}

}

echo $table;

5.SQL注入

也就是用户故意在表单里写入sql语句，导致应用的行为异常，

解决方法很简单，也就是将用户的输入都变成字符串，特殊符号转义

AAffA0nNPuCLAAAAAElFTkSuQmCC

echo $pic_path.'
';//'or 1=1 #

echo $conn->quote($pic_path);//'\'or 1=1 #'

$sql_query = <<

SELECT * FROM php_pic WHERE pic_path = $pic_path AND pic_length= $pic_length;

EOT;

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

$sql_query = <<

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

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 .= "

";

$table .= "

id";

$table .= "

pic_path";

$table .= "

pic_length";

$table .= "

pic_mime";

$table .= "

pic_width";

$table .= "

pic_height";

$table .= "";

while ($cursor->fetch()) {

$table .= "

";

$table .= "

" . $id_col . "";

$table .= "

" . $pic_path_col . "";

$table .= "

" . $pic_length_col . "";

$table .= "

" . $pic_mime_col . "";

$table .= "

" . $pic_width_col . "";

$table .= "

" . $pic_height_col . "";

$table .= "";

}

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 "

$err
";

}

}

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] . '
' . 'SQL错误信息 ' . $errArr[2] . '
' . "ERROR ON : $sql";

self::throwException($err);

return false;

}

}

3.使用

AAffA0nNPuCLAAAAAElFTkSuQmCC

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;';

AAffA0nNPuCLAAAAAElFTkSuQmCC

4.查询一条

AAffA0nNPuCLAAAAAElFTkSuQmCC

---->[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;

AAffA0nNPuCLAAAAAElFTkSuQmCC

/**增删改

* @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 = <<

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.单个查询强封装

比如根据指定的键，我想查三列，

AAffA0nNPuCLAAAAAElFTkSuQmCC

$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

AAffA0nNPuCLAAAAAElFTkSuQmCC

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

+----+--------------------------------------+------------+----------+-----------+------------+

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

weixin_39858298
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
php_pdo_mysql.dll_dll_PDO_MYSQL_php_
09-29
php pdo mysql dll file
php mysqli 绑定变量,php – Mysqli参数绑定问题
weixin_42113794的博客
03-28 154
我需要额外的一双眼睛.任何帮助将不胜感激.这是一个非常简单的搜索查询,但无论出于何种原因,我找不到该错误.好吧,我知道bug在哪里.我只是无法超越它.无论如何…..我从POST变量中获取搜索值,设置该变量,然后按如下方式设置列变量…$term = "'%".$_POST['searchTerm']."%'";$field = "columnName";当我回应这些时,它们会完美地出现.因此,如果我...
mysql游标的使用_mysql cursor游标的使用，实例
weixin_32927369的博客
01-18 353
mysql被oracle收购后，从mysql-5.5开始,将InnoDB作为默认存储引擎，是一次比较重大的突破。InnoDB作为支持事务的存储引擎，拥有相关的RDBMS特性：包括ACID事务支持，数据完整性(外健)，灾难恢复能力等特性。使用mysql做为数据库的话，将来程序员肯定要写很多，存储过程，function等。在写些东东的时候，游标肯定是少不了的。下面简单简介一下。一，什么是游标(curs...
testlink mysql配置_TestLink安装配置手册
weixin_39547596的博客
02-22 494
TestLink安装配置手册范围本文仅作为安装和配置TestLink的参考文档。本文的第一部分为TestLink的安装步骤，第二部分是TestLink的配置方法。本文和其他相关文档可在中寻求帮助一般TestLink安装的步骤如下：1.下载TestLink2.解压文件3.创建数据库4.导入缺省数据5.编辑配置文件6. PHP文件扩展7.登录TestLink系统需求要使TestLink正常运行，需要安...
PDO 类库
同学小江的博客
11-24 1018
header('content-type:text/html;charset=utf-8'); class PdoMySQL{   public static $config=array();//设置连接参数，配置信息   public static $link=null;//保存连接标识符   public static $pconnect=false;//是否开启长连接   publ
mysql php 变量赋值,mysql变量赋值要注意的_MySQL
weixin_39675178的博客
03-10 52
bitsCN.commysql变量赋值要注意的mysqlsql今天由于项目需要，需要写个存储过程，这个可是一年才难得写一次。于是没办法，重新捡起来。开始写写到后来。。Sql代码 CREATE FUNCTION `getChildLstnotSun`(department_id int) RETURNS varchar(1000) BEGIN DECLARE sTemp VAR...
PHP-PDO-MySQL-Class：一个类似于Python MySQLdbPHP MySQL PDO类，当使用“ WHERE IN”语句时，它支持迭代器和参数绑定
02-03
安装将src/下的文件复制到您的程序中要么composer require lincanbin/php-pdo-mysql-class初始化<?phpdefine ( 'DBHost' , '127.0.0.1' );define ( 'DBPort' , 3306 );define ( 'DBName' , 'Database' );define ...
php中mysql连接方式PDO使用详解
10-24
本文主要详细介绍了PHP中mysql的连接方式PDO的各种使用方法，算是个人的一个小结，如有遗漏还请告之，有需要的小伙伴来参考下吧。
pdo_mysql.rar_PDO_pdo_mysql.so
09-23
php常用拓展，使用PDO链接数据库时必须用到
php基于PDO实现功能强大的MYSQL封装类实例
10-20
主要介绍了php基于PDO实现功能强大的MYSQL封装类,结合完整实例形式分析了php基于pdo实现mysql数据库连接、增删改查、事务等操作的方法,需要的朋友可以参考下
oracle thinphp 中文乱码_ThinkPHP与Oracle操作实践
weixin_39651488的博客
02-19 215
在一次工作的实际项目中，PHP程序员雷雪松操作使用Oracle数据库时遇到很多奇葩的错误。因为第一次使用ThinkPHP操作Oracle数据库，几乎查找了网上所有的资料，测试很多解决办法，搞得心力憔悴。由于网上关于ThinkPHP操作Oracle数据库的资料较少，有些没有实践验证、过时或无效，在前面PHP程序员雷雪松的博客中详细讲解PHP如何连接Oracle的配置。现在PHP程序员雷雪松通过自己实...
php使用PDO方法详解
weixin_30339457的博客
01-08 108
PDO::exec:返回的是int类型,表示影响结果的条数. 复制代码 代码如下: PDOStatement::execute 返回的是boolean型,true表示执行成功,false表示执行失败,这两个通常出现在如下代码: 复制代码 代码如下: $rs0 = $pdo->exec($sql); $pre = $pdo->prepare($sql); $rs1 ...
关于游标嵌套
Pei_hua100的专栏
09-17 548
游标嵌套使用时，@@FETCH_STATUS的值有时会从内部游标影响到外部的游标，使外部的游标只循环一次。这时要检查游标的使用方法。要先移动游标，然后就开始判断，为真进行进行业务逻辑处理，然后移动游标，这样就没问题了。示例如下： declare 外层游标 open 外层游标 fetch next ...提取外层游标行 while @@FETCH_STATUS = 0 begin declare 内层游标 open 内层游标 .
mysql cursor游标的使用，实例
iteye_6020的博客
03-28 686
mysql被oracle收购后，从mysql-5.5开始,将InnoDB作为默认存储引擎，是一次比较重大的突破。InnoDB作为支持事务的存储引擎，拥有相关的RDBMS特性：包括ACID事务支持，数据完整性（外健），灾难恢复能力等特性。 使用mysql做为数据库的话，将来程序员肯定要写很多，存储过程，function等。在写些东东的时候，游标肯定是少不了的。下面简单简介一下。一，什么是游标...
PHP中的MySQL使用--基于PDO
张风捷特烈的博客
03-18 500
个人所有文章整理在此篇，将陆续更新收录:知无涯，行者之路莫言终（我的编程之路） 一、准备活动 PHP Data Object 数据库访问抽象层 统一各种数据库访问接口 1.查看PHP的配置信息 调用一个函数即可输出一个界面。默认PDO是支持MySQL的 <?php phpinfo(); 如果不支持，在ph...
《PHP与Mysql之四fetch函数》
self_realian的博客
06-02 1183
PHP与Mysql之四fetch函数 一、mysql_fetch_row()----获取和显示数据 <?php $link =mysql_connect('localhost','root','root');//连接数据库 mysql_set_charset("utf8");//设置编码 mysql_select_db("page");//选择数据库 $query=mysql_qu
php+mysql实现数据库操作
热门推荐
测试猫
12-09 1万+
首先我们要下载个本地服务器Wampserver，并配置好参数(自行百度)。注意：配置服务器时，端口冲突要改端口，而且电脑装有mysql运行环境的话，在计算机的管理服务把mysql环境停止运行。如果运行服务器，发现弹窗报错，一般电脑缺少Microsoft Visual C++之类的运行环境，装个VC2013和VC2015试试。运行还要把php警告去除，去除方法： 需要打开php/php.ini文件
php pdo mysql mysql_i 三种连接方式
最新发布
09-03
MySQLi（MySQL Improved）是PHP中的一种连接MySQL数据库的扩展，是对原有mysql扩展的增强和改进。MySQLi提供了面向对象和面向过程两种API，支持事务处理、预处理语句、存储过程等功能，同时也提供了对MySQL的新特性...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值