如何用 PDO 替换 MySQL 函数? [英] How to replace MySQL functions with PDO?

查看：26 发布时间：2021/12/26 20:02:24 php sql pdo

本文介绍了如何用 PDO 替换 MySQL 函数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在 php.net 上阅读有关 MySQL 函数的内容时.我遇到了这个消息

<块引用>

警告这个扩展从 PHP 5.5.0 开始被弃用，将来会被删除.相反，应使用 MySQLi 或 PDO_MySQL 扩展.另请参阅 MySQL:选择 API 指南和相关常见问题以了解更多信息.此函数的替代方法包括:

我读过有关 PDO 的文章.如何使用 MySQL 或 MSSQL 将我的代码更新为 PDO?

解决方案

我看到很多关于实现my_sql 函数的代码.其他人(包括我自己)的评论迫使提问者放弃MySQL 函数并开始使用 PDO 或 MySQLI.这篇文章是来帮忙的.您可以参考它，因为它解释了为什么不推荐使用它们以及 PDO 是什么，以及实现 PDO 的最小代码示例.

首先:

从mysql 函数 到PDO 的转换不是一个简单的搜索和替换案例.PDO 是 PHP 语言的面向对象编程插件.这意味着使用 mysql 函数 编写代码的另一种方法.首先为什么要转换?

为什么不推荐使用 mysql 函数?

<块引用>

mysql 扩展很古老，从 15 年前发布的 PHP 2.0 开始就已经存在(！！)；这与试图摆脱过去不良做法的现代 PHP 截然不同.mysql 扩展是一个非常原始的、低级的 MySQL 连接器，它缺乏许多方便的特性，因此很难以安全的方式正确应用；因此，这对菜鸟不利.许多开发人员不了解 SQL 注入，而且 mysql API 非常脆弱，即使您知道它也很难阻止它.它充满了全局状态(例如隐式连接传递)，这使得编写难以维护的代码变得容易.由于它很旧，在 PHP 核心级别维护可能会非常困难.

mysqli 扩展更新了很多，并修复了上述所有问题.PDO 也是相当新的，也解决了所有这些问题，以及更多.

由于这些原因* mysql 扩展将在未来某个时候被删除.

source Deceze

如何实施 PDO

PDO 提供了一种连接多个数据库的解决方案.此答案仅涵盖 MySQL 和 MSSQL 服务器.

连接到 MySQL 数据库，先决条件

这相当简单，不需要任何 PHP 预先设置.现代 PHP 安装标配一个模块，该模块允许 PDO 连接到 MySQL 服务器.

<块引用>

模块为php_pdo_mysql.dll

连接到 MSSQL 数据库，先决条件

这是一个更高级的设置.您需要 php_pdo_sqlsrv_##_ts.dll 或 php_pdo_sqlsrv_##_nts.dll 驱动程序.它们是特定于版本的，因此是 ##.在撰写本文时，Microsoft 已发布PHP 5.5.x 的官方驱动程序.5.6 驱动程序尚未由 Microsoft 正式发布，但可以通过其他.

<块引用>

模块是 php_pdo_sqlsrv_##_ts.dll 用于线程安全变体该模块是 php_pdo_sqlsrv_##_nts.dll 用于非线程安全变体

使用 PDO 连接到数据库要连接到数据库，您需要从 PDO 构造创建一个新的 PDO 实例.

$connection = new PDO(arguments);

PDO 构造函数采用 1 个必需参数和 3 个可选参数.

DSN 或数据源名称，主要是一个字符串，包含有关驱动程序、主机和数据库名称的信息.自 PHP 7.4 起，它还可以包含用户名和密码.
用户名
密码
选项

连接到MySQL

$dsn = 'mysql:dbname=databasename;host=127.0.0.1';$user = 'dbuser';$password = 'dbpass';$dbh = new PDO($dsn, $user, $password);

我们来看看$dsn:首先它定义了驱动程序(mysql).然后是数据库名称，最后是主机.

连接到 MSSQL

$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename';$user = 'dbuser';$password = 'dbpass';$dbh = new PDO($dsn, $user, $password);

我们来看看$dsn:首先它定义了驱动程序(sqlsrv).然后是主机，最后是数据库名称.

当您创建实例时，会建立与数据库的连接.在执行 PHP 脚本期间，您只需执行一次此操作.

<块引用>

您需要将 PDO 实例创建包装在 try-catch 子句中.如果创建失败，则会显示一个回溯，其中会显示有关您的应用程序的关键信息，例如用户名和密码.为避免这种情况，捕获错误.

试试{$connection = new PDO($dsn, $user, $password);}catch( PDOException $Exception ){echo "无法连接数据库.";出口;}

<块引用>

要抛出 SQL 服务器返回的错误，请使用 setAttribute 将此选项添加到 PDO 实例:$connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

`执行查询`

PDO 使用准备好的语句.这是PDO 方法和mysql 函数之间的真正区别.后者很容易受到SQL-INJECTION 的影响.一个人会像这样构建一个查询:

$SQL = 'SELECT ID FROM users WHERE user = '.$username ;

当恶意网站或个人发布用户名时注入器；删除表用户.结果将是毁灭性的.您需要通过使用引号转义和封装字符串和变量来证明您的代码.这不得不做对于每个查询.在较大的网站或维护不善的代码上，拥有允许 SQL 注入的表单的风险可能会变得非常高.准备好的语句消除了第一层 SQL 注入的机会，就像上面的例子一样.

PDO 驱动程序充当 PHP 服务器和数据库服务器之间的中间人，称为数据访问抽象层.它不会重写您的 SQL 查询，但确实提供了一种连接到多种数据库类型的通用方法并为您处理将变量插入到查询中.Mysql 函数构建了对 PHP 代码执行的查询.使用 PDO，查询实际上是在数据库服务器上构建的.

准备好的 SQL 示例:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';

注意区别；PHP 变量不是在字符串外使用 $ ，而是在字符串内使用 : 引入变量.另一种方式是:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = ?';

如何执行实际查询

您的 PDO 实例提供了两种执行查询的方法.当您没有变量时，您可以使用 query()，变量使用 prepare().query() 在调用时立即执行.请注意调用的面向对象方式(->).

$result = $connection->query($SQL);

准备方法

prepare 方法接受两个参数.第一个是 SQL 字符串，第二个是数组形式的选项.一个基本的例子

$connection->prepare($SQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

在我们的 SQL 字符串示例中，我们使用了一个名为 :username 的命名变量.我们仍然需要将一个 PHP 变量、整数或字符串绑定到它.我们可以通过两种方式做到这一点.要么构建一个包含命名变量作为 key 的数组，要么使用 bindParam 或 bindValue 方法.为简单起见，我将解释数组变量和方法 bindValue.

数组你可以对命名变量做这样的事情，你提供变量作为数组键:

$queryArguments = array(':username' => $username);

这对于索引变量(?):

$queryArguments = array($username);

当您添加了所有需要的变量后，您可以调用方法 execute() 来执行查询.从而将数组作为参数传递给函数 execute.

$result = $connection->execute($queryArguments);

bindValue bindValue 方法允许您将值绑定到 PDO 实例.该方法采用两个必需参数和一个可选参数.可选参数设置值的数据类型.

对于命名变量:

$connection->bindValue(':username', $username);

对于索引变量:

$connection->bindValue(1, $username);

将值绑定到实例后，您可以调用 execute 而无需传递任何参数.

$result = $connection->execute();

<块引用>

注意:一个命名变量只能使用一次！使用它们两次将导致执行查询失败.根据您的设置，这会或不会引发错误.

`获取结果`

同样，我将只介绍从返回的集合中获取结果的基础知识.PDO 是一个相当先进的附加组件.

使用 fetch 和 fetchAll

如果您执行了选择查询或执行了返回结果集的存储过程:

获取 fetch 是一种最多可以使用三个可选参数的方法.它从结果集中获取一行.默认情况下，它返回一个 array ，其中包含列名作为键和索引结果.我们的示例查询可能会返回类似

ID EMAIL1 人@example.com

fetch 将返回:

数组([ID] =>1[0] =>1[电子邮件] =>有人@example.com[1] =>有人@example.com)

要回显结果集的所有输出:

while($row = $result->fetch()){回声 $row['ID'];回声 $row['EMAIL'];}

您可以在此处找到其他选项:fetch_style;

fetchAll 获取单个数组中的所有行.使用与 fetch 相同的默认选项.

$rows = $result->fetchAll();

如果您使用的查询不返回结果，例如插入或更新查询，您可以使用方法 rowCount 来检索受影响的行数.

<小时>一个简单的类:
class pdoConnection {公共 $isConnected;受保护的 $connection;公共函数 __construct($dsn, $username, $password, $options = array()) {$this->isConnected = true;尝试 {$this->connection = new PDO($dsn, $username, $password, $options);$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$this->connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);//设置默认返回数组中的命名"属性.} catch (PDOException $e) {$this->isConnected = false;抛出新异常($e->getMessage());}}公共函数断开(){$this->connection = null;$this->isConnected = false;}公共函数查询($SQL){尝试 {$result = $this->connection->query($SQL);返回 $result;} catch (PDOException $e) {throw new PDOException($e->getMessage());}}公共函数准备($SQL，$params = array()){尝试 {$result = $this->connection->prepare($SQL);$result->execute($params);返回 $result;} catch (PDOException $e) {throw new PDOException($e->getMessage());}}}
使用方法:
$dsn = 'mysql:dbname=databasename;host=127.0.0.1';$user = 'dbuser';$password = 'dbpass';$db = new pdoConnection($dsn, $user, $password);$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';$result = $db->prepare($SQL, array(":username" => 'someone'));while($row = $result->fetch()){回声 $row['ID'];回声 $row['EMAIL'];}
When reading on php.net about MySQL functions. I encountered this message

  Warning 
  This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:
  
  
  mysqli_connect() 

  PDO::__construct()
  
I've read about PDO. How can I update my code to PDO using either MySQL or MSSQL?
 解决方案 
I see a lot of code posted on SO implementing my_sql functions. And comments from others (including myself) pressing the questioners to abandon MySQL functions and start using PDO or MySQLI. This post is here to help. You can refer to it as it provides explanation to why they are deprecated and what PDO is, plus a minimal code example to implement PDO.

First of all:

Conversion from mysql functions to PDO is not a simple case of search and replace. PDO is an Object Oriented Programming add on for the PHP language. 
That means an other approach in writing the code as with the mysql functions. First why convert?

Why are mysql functions deprecated?


  The mysql extension is ancient and has been around since PHP 2.0, released 15 years ago (!!); which is a decidedly different beast than the modern PHP which tries to shed the bad practices of its past. The mysql extension is a very raw, low-level connector to MySQL which lacks many convenience features and is thereby hard to apply correctly in a secure fashion; it's therefore bad for noobs. Many developers do not understand SQL injection and the mysql API is fragile enough to make it hard to prevent it, even if you're aware of it. It is full of global state (implicit connection passing for instance), which makes it easy to write code that is hard to maintain. Since it's old, it may be unreasonably hard to maintain at the PHP core level.
  
  The mysqli extension is a lot newer and fixes all the above problems. PDO is also rather new and fixes all those problems too, plus more.
  
  Due to these reasons* the mysql extension will be removed sometime in the future.
source Deceze

How to implement PDO

PDO offers one solution for connecting to multiple databases. This answer covers only MySQL and MSSQL servers.

Connecting to a MySQL database, prerequisites

This is fairly simple and doesn't require any pre set-up of PHP. Modern PHP installations are standard shipped with a module that allows PDO connections to MySQL servers.

  The module is php_pdo_mysql.dll
Connecting to a MSSQL database, prerequisites

This is a more advanced set-up. You need php_pdo_sqlsrv_##_ts.dll or php_pdo_sqlsrv_##_nts.dll drivers. They are version specific hence the ##. At the moment of writing, Microsoft has released
official drivers for PHP 5.5.x. The 5.6 drivers aren't yet officially released by Microsoft, but are available as non-official builds by others.

  The module is php_pdo_sqlsrv_##_ts.dll for the thread safe variant
  The module is php_pdo_sqlsrv_##_nts.dll for the non-thread safe variant
Connecting to a database using PDO
To connect to a database you need to create a new PDO instance from the PDO construct.
$connection = new PDO(arguments);
The PDO constructor takes 1 required arguments and 3 optional. 

DSN or Data Source Name, mostly this is a string containing information about the driver, host and database name. Since PHP 7.4 it can also include username and password. 
Username
Password
Options
Connecting to MySQL
$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);
Let's take a look at $dsn: First it defines the driver (mysql). Then the database name and finally the host.

Connecting to MSSQL
$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);
Let's take a look at $dsn: First it defines the driver (sqlsrv). Then the host and finally the database name.

When you create the instance a connection is made to the database. You only have to do this once during the execution of a PHP script.

  You need to wrap the PDO instance creation in a try-catch clause. If the creation fails a back trace is shown revealing critical information about your application, like username and password. To avoid this catch the errors.


try 
{
    $connection = new PDO($dsn, $user, $password);
}
catch( PDOException $Exception ) 
{   
     echo "Unable to connect to database.";
     exit;
}



  To throw errors returned by your SQL server add this options to your PDO instance using setAttribute:  $connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


Performing queries

PDO uses prepared statements. This is a real difference between PDO's approach and mysql functions. The latter was very susceptible to SQL-INJECTION. One would build a query like this:
$SQL = 'SELECT ID FROM users WHERE user = '.$username ;
When a malicious website or person posts the username injector; DROP TABLE users. The results will be devastating. You needed to proof your code by escaping and encapsulating strings and variables with quotes. This had to be done
for every query. On larger websites or poorly maintained code the risk of having a form that allowed SQL injection could become very high. Prepared statements eliminates the chance of first tier SQL injection like the example above.

The PDO drivers act as a man-in-the-middle between your PHP-server and database server, called a data-access abstraction layer. It doesn't rewrite your SQL queries, but do offer a generic way to connect to multiple database types
and handles the insertion of variables into the query for you. Mysql functions constructed the query on execution of the PHP code. With PDO the query actually gets build on the database server.

A prepared SQL example:
$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
Note the difference; Instead of a PHP variable using $ outside the string, we introduce a variable using : within the string. Another way is:
$SQL = 'SELECT ID, EMAIL FROM users WHERE user = ?';
How to perform the actual query

Your PDO instance provides two methods of executing a query. When you have no variables you can use query(), with variables use prepare(). query() is immediately executed upon calling. Please note the object oriented way of the call (->).
$result = $connection->query($SQL);
The prepare method

The prepare method takes two arguments. The first is the SQL string and the second are options in the form of an Array. A basic example
$connection->prepare($SQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
In our SQL string example we've used a named variable called :username. We still need to bind a PHP variable, integer or string to it. We can do this in two ways. Either build an array containing the named variables as key or use the method bindParam or bindValue.
I will explain the array variant and the method bindValue for the sake of simplicity. 

Array

You can do something like this for named variables, where you provide the variable as array key:
$queryArguments = array(':username' => $username);
And this for indexed variables (?):
$queryArguments = array($username);
When you have added all the variables you need you can call upon the method execute() to perform the query. Thereby passing the array as argument to the function execute.
$result = $connection->execute($queryArguments);
bindValue

The bindValue method allows you to bind values to the PDO instance. The method takes two required arguments and one optional. The optional arguments set the data-type of the value.

For named variables:
$connection->bindValue(':username', $username);
For indexed variables:
$connection->bindValue(1, $username);
After binding the values to the instance, you can call upon execute without passing any arguments.
$result = $connection->execute();



  NOTE: You can only use a named variable once! Using them twice will result in a failure to execute the query. Depending on your settings this will or will not throw an error.


Fetching the results

Again I will only cover the basics for fetching results from the returned set. PDO is a fairly advanced add-on.

Using fetch and fetchAll

If you did a select query or executed a stored procedure that returned a result set:

fetch

fetch is a method that could take up to three optional arguments. It fetches a single row from the result set. By default it returns an array containing the column names as keys and indexed results.
Our example query could return something like
ID      EMAIL
1       someone@example.com
fetch will return this as:
Array
(
    [ID] => 1
    [0] => 1
    [EMAIL] => someone@example.com
    [1] => someone@example.com
)
To echo all output of a result set:
while($row = $result->fetch())
{
    echo $row['ID'];
    echo $row['EMAIL'];
}
There are other options you can find here: fetch_style;

fetchAll

Fetches all rows in a single array. Using the same default option as fetch.
$rows = $result->fetchAll();
If you used a query that didn't return results like a insert or update query you can use the method rowCount to retrieve the amount of rows affected.



A simple class:

class pdoConnection {
    public $isConnected;

    protected $connection;

    public function __construct($dsn, $username, $password, $options = array()) {
        $this->isConnected = true;
        try {
            $this->connection = new PDO($dsn, $username, $password, $options);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); //sets the default to return 'named' properties in array.
        } catch (PDOException $e) {
            $this->isConnected = false;
            throw new Exception($e->getMessage());
        }
    }

    public function disconnect() {
        $this->connection = null;
        $this->isConnected = false;
    }

    public function query($SQL) {
        try {
            $result = $this->connection->query($SQL);
            return $result;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage());
        }
    }

    public function prepare($SQL, $params = array()) {
        try {
            $result = $this->connection->prepare($SQL);
            $result->execute($params);
            return $result;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage());
        }
    }
}
How to use:
$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$db = new pdoConnection($dsn, $user, $password);

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
$result = $db->prepare($SQL, array(":username" => 'someone'));

while($row = $result->fetch())
{
    echo $row['ID'];
    echo $row['EMAIL'];
}   


                        
这篇关于如何用 PDO 替换 MySQL 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！


                    
                        查看全文

如何用 PDO 替换 MySQL 函数? [英] How to replace MySQL functions with PDO?

问题描述

为什么不推荐使用 mysql 函数?

如何实施 PDO

`执行查询`

`获取结果`

一个简单的类:

Why are mysql functions deprecated?

How to implement PDO

Performing queries

Fetching the results

A simple class:

相关文章

PHP最新文章

热门教程

热门工具

登录关闭

如何用 PDO 替换 MySQL 函数? [英] How to replace MySQL functions with PDO?

问题描述

为什么不推荐使用 mysql 函数?

如何实施 PDO

执行查询

获取结果

一个简单的类:

Why are mysql functions deprecated?

How to implement PDO

Performing queries

Fetching the results

A simple class:

相关文章

PHP最新文章

热门教程

热门工具

登录 关闭

`执行查询`

`获取结果`

登录关闭