mysql api
Introduction (All good things must come to an end)
简介 （ 所有美好的事物都必须结束 ） 原始MySQL API已经消失了。 PHP在5.5版中已弃用（几年前），并在所有当前版本中将其从PHP中删除 。 结果，今天的问题就在我们身上。 现在该是补救我们旧PHP脚本并将其更新到当前千年的时候了。 本文介绍了这些问题，并提供了分步和逐行的说明，可以使我们脱离MySQL的黑暗面，并进入当前的最佳实践。
WHY in the World Did PHP Do This?
为什么在世界上PHP这样做了？
Great question! The MySQL extension was by far the most popular data base extension used by PHP web sites. It had good documentation and support (consider phpMyAdmin, and after you've used that, go over to phpPgAdmin and compare features). MySQL was thoroughly debugged, and was widely accessible - you could find literally thousands of examples of PHP+MySQL code with a Google search. So why did PHP take such a draconian step? In my opinion, it comes down to two issues.
好问题！ 到目前为止，MySQL扩展是PHP网站使用的最流行的数据库扩展。 它具有良好的文档和支持（考虑phpMyAdmin ，并在使用完之后转到phpPgAdmin并比较功能）。 MySQL经过了彻底的调试，可广泛访问-通过Google搜索，您可以找到成千上万个PHP + MySQL代码示例。 那么，PHP为什么要采取如此严厉的措施呢？ 我认为，可以归结为两个问题。
First, PHP has "grown up" as a programming language. At one time, its proponents were looked down upon as script kiddies by the computer science community. Never mind the fact that parts of Google, much of Yahoo, and nearly all of Facebook, Digg, Wikipedia, WordPress, and Slack are PHP machines -- there was a stench is created by the millions of incompetently written PHP web sites that crashed and got hacked all the time. It's very easy to pick up a little knowledge about PHP and MySQL, and an unfortunate corollary is that it's very easy to use PHP and MySQL wrong! Security holes abound, and the unsophisticated programmer propagates these errors by copying PHP code without understanding the dangers. So in an effort to bring some rigor and science to the creation of PHP scripts, we got this explanation from P. Olson. As you might imagine, there was early controversy over the change, but that is in the past now.
首先，PHP已“成长”为一种编程语言。 一次，它的支持者被计算机科学界视作脚本小子。 不必介意Google，雅虎的大部分地区以及Facebook，Digg，Wikipedia，WordPress和Slack的几乎所有部分都是PHP机器的事实-数百万不合格PHP网站造成了恶臭，这些网站崩溃了，一直被黑客入侵。 掌握一些有关PHP和MySQL的知识非常容易，不幸的推论是，使用PHP和MySQL非常容易出错 ！ 安全漏洞比比皆是，老练的程序员在不了解危险的情况下通过复制PHP代码传播这些错误。 因此，为了使PHP脚本的创建更加严格和科学，我们从P. Olson那里得到了这种解释 。 就像您想象的那样，有关更改的争论很早，但是现在是过去。
Second, the PHP authors were actually right -- the MySQL extension was simply terrible about security. If it had been any good, there wouldn't have been a need for two million MySQL security articles! There was nothing inherently wrong in the extension; the trouble came from the way novice programmers misused the extension. As but one example, consider the use of external data in MySQL queries. A common-sense implementation would have prevented the use of a query variable that had not been escaped. But the original implementation of the MySQL extension imposed no such requirement, and the resulting security holes caused many web sites to fail.
其次，PHP编写者实际上是正确的-MySQL扩展在安全性方面简直糟透了 。 如果一切顺利，那么就不需要200万篇MySQL安全性文章了 ！ 扩展名天生没有错。 麻烦来自新手程序员滥用扩展的方式。 作为一个示例，请考虑在MySQL查询中使用外部数据。 常识性的实现会阻止使用未转义的查询变量。 但是MySQL扩展的原始实现没有施加这样的要求，并且由此产生的安全漏洞导致许多网站失败。
PHP tried to discourage the use of the MySQL extension at PHP 5.0 by removing the MySQL extension from the standard installation. But that did not have the desired effect of luring programmers away from the dangerous and sloppy code, and that was done more than 10 years ago. So a bigger stick was needed. The only way to make such a sweeping change in the behavior of a language interpreter (and a huge population of users) was to remove the dangerous parts of PHP and force programmers to stop using them.
PHP试图通过从标准安装中删除MySQL扩展来阻止在PHP 5.0上使用MySQL扩展 。 但这并没有达到使程序员远离危险而草率的代码的预期效果，而且十多年前就做到了。 所以需要更大的棍子。 彻底改变语言解释器（和大量用户）的行为的唯一方法是删除PHP的危险部分，并迫使程序员停止使用它们。
In addition to security, there are many good reasons to adopt object-oriented programming techniques. The MySQL extension by its very nature, frustrated this effort. The MySQL API was procedural, leading to code that was messy, difficult to maintain and impossible to reuse. In contrast, the MySQLi and PDO extensions offer object-oriented interfaces.
除了安全性外，还有许多充分的理由采用面向对象的编程技术。 MySQL扩展本身就使这项工作受挫。 MySQL API是过程性的 ，导致代码混乱，难以维护且无法重用。 相反，MySQL i和PDO扩展提供了面向对象的接口。
What are Others Saying and Doing About This Change?
其他人对此变化有何看法？
On April 7, 2014, the Make WordPress team issued this announcement:
2014年4月7日， Make WordPress团队发布了此公告：
absolutely shouldn’t be using PHP's mysql_*() functions any more绝对不应该再使用PHP的mysql _ *（）函数 WPDB functions instead. WPDB函数。
OK, What Should I Do?
好，我该怎么办？
First, adopt a coding standard that prohibits further use of the MySQL extension. After today, you must never write mysql_query() again! Next, learn about the alternatives to MySQL. Basically, you've got two good choices: MySQLi or PHP Data Objects ("PDO").
首先，采用禁止进一步使用MySQL扩展的编码标准。 今天之后，您再也不能写mysql_query（）了！ 接下来，了解MySQL的替代方法 。 基本上，您有两个不错的选择： MySQL i或PHP数据对象（“ PDO”） 。
Second, be aware of the environmental changes that accompany this PHP change. Installation libraries are different now. The way you install the MySQL extensions is different now. Please take a moment to read this page, especially if you're moving from an obsolete PHP 5.3 installation to a current release or version!
其次，要注意与PHP更改相关的环境更改。 安装库现在有所不同。 现在，您安装MySQL扩展的方式有所不同。 请花一点时间阅读此页面，尤其是当您从过时PHP 5.3安装过渡到当前发行版或版本时！
https://dev.mysql.com/downloads/connector/php-mysqlnd/
https://dev.mysql.com/downloads/connector/php-mysqlnd/
Do I Have to Change my PHP Programs?
我必须更改我PHP程序吗？
Yes. And the rest of this article shows annotated code examples to help you understand how to accomplish the changes. The scripts have been set up as teaching examples. You should be able to add your own MySQL credentials and install them on your own servers. Then you can run them to see how the scripts work and how the output from error messages differs.
是。 本文的其余部分将显示带注释的代码示例，以帮助您了解如何完成更改。 脚本已设置为教学示例。 您应该能够添加自己MySQL凭据并将其安装在自己的服务器上。 然后，您可以运行它们以查看脚本如何工作以及错误消息的输出如何不同。
The scripts are shown in complete form at the bottom of the article. In the annotated parts, only the essential pieces are shown to illustrate the different approaches.
这些脚本在文章底部以完整的形式显示。 在带注释的部分中，仅显示了基本部件以说明不同的方法。
In each code set, the first example is the familiar-but-obsolete MySQL extension. Following that, we have the MySQLi extension. In that example, I tried to aim for the most direct one-to-one replacement of existing MySQL programming. Of necessity this meant avoiding MySQLi prepared statements. They are introduced in the PDO example, and a complete MySQLi example showing the use of prepared statements is included with the code snippets at the end of the article. There are some differences in the syntax but for the most part the functionality lines up 1:1 between MySQL and MySQLi. That cannot be said for PDO, where some substantial programming changes come into play.
在每个代码集中，第一个示例是熟悉但过时MySQL扩展。 之后，我们有了MySQL i扩展。 在该示例中，我试图以最直接的一对一替换现有MySQL编程为目标。 需要这意味着避免我MySQL预处理语句。 它们在PDO示例中进行了介绍，并且在文章结尾的代码段中包含一个完整MySQL i示例，该示例演示了如何使用准备好的语句。 语法上有一些区别，但是在大多数情况下，MySQL和MySQL i之间的功能按1：1排列。 对于PDO而言，这还不能说，其中一些重大的编程更改正在起作用。
MySQLi or PDO?
MySQL i还是PDO？
Which extensions should you choose? Either will work. Object-oriented MySQLi requires the smallest number of code changes. PDO holds out the promise that you could, theoretically, change out the underlying data base without having to change your PHP scripts. I don't know any professional programmer who believes such a claim, given the variations in SQL. And PDO can only give you parameter binding if you use prepared statements, thus introducing a good bit of overhead. Your choice will largely depend on the amount of time you want devote to the required modifications. Can you trust your programmers to remember to escape external data before using it in a query? If the answer is "yes" then MySQLi is a good choice, even if many computer scientists would express a preference for PDO.
您应该选择哪些扩展名？ 两者都会起作用。 面向对象MySQL i需要最少的代码更改。 PDO承诺，从理论上讲，您可以更改基础数据库而不必更改PHP脚本。 考虑到SQL的变化，我不知道有哪个专业程序员会相信这种说法。 而且，PDO仅在使用准备好的语句时才能为您提供参数绑定 ，从而带来大量的开销。 您的选择将在很大程度上取决于您要花费在所需修改上的时间。 您可以相信程序员记住在查询中使用外部数据之前要对其进行转义吗？ 如果答案是“是”，那么即使许多计算机科学家对PDO表示偏爱，MySQL i也是一个不错的选择。
If you are considering PDO, this article is worth reading, but be aware that there are errors in the article's code examples. Read for comprehension, but don't copy the code unless you're willing to do some debugging!
如果您正在考虑使用PDO，那么本文值得一读 ，但是请注意， 本文的代码示例中存在错误 。 阅读以获取理解，但是除非您愿意进行一些调试，否则请不要复制代码！
Object-Oriented or Procedural?
面向对象还是程序的？
Object-oriented. There are no real advantages and many disadvantages if you get stuck in procedural database extensions. So just don't do that.
面向对象。 如果您陷于过程数据库扩展中，就没有真正的优点，也有很多缺点。 因此，请不要这样做。
From personal experience, here are the reasons why I choose object-oriented MySQLi.
根据个人经验，这就是我选择面向对象MySQL i的原因。
1. There can be two simultaneous connections to the DB engine. You do not have to convert all of the old MySQL code at once - you can convert it query-by-query.
1.可以有两个同时连接到数据库引擎。 您不必一次转换所有旧MySQL代码-您可以逐个查询地转换它。
2. There are almost no changes needed to the SQL query strings (in PDO almost every query string will have to change).
2.几乎不需要更改SQL查询字符串（在PDO中几乎每个查询字符串都必须更改）。
3. There is almost 100% 1:1 matching of functionality (in PDO some of the MySQL operations are lost, eg, data_seek() does not exist).
3.几乎100％的1：1功能匹配（在PDO中，一些MySQL操作丢失了，例如， data_seek（）不存在）。
4. There are relatively few code changes needed. If you were to choose procedural MySQLi instead of OOP MySQLi, every call to the query() function would have to change because the procedural version requires the database link identifier as the first argument. The OOP version infers the database link from the MySQLi object.
4.几乎不需要更改代码。 如果您选择过程式MySQL i而不是OOP MySQL i ，则每次对query（）函数的调用都必须更改，因为过程版本需要将数据库链接标识符作为第一个参数。 OOP版本从MySQLi对象推断数据库链接。
5. Anybody who really believes that using PDO will somehow magically let you change out the underlying data base engine, without creating havoc in your application software, has never tried to change out an underlying data base engine.
5.真正相信使用PDO的人会以某种方式神奇地让您更改基础数据库引擎，而又不会对应用程序软件造成破坏，因此从未尝试过更改基础数据库引擎。
Do I Have to Change my Query Strings?
我必须更改查询字符串吗？
Probably not if you choose MySQLi. Your queries require very modest changes (if any) when you choose the MySQLi extension. PDO is a different story. It uses an entirely different query structure with prepared statements and bound data. PDO does not put PHP variables into its query strings; it passes the variables separately and thus it requires changes in almost all query strings, as well as the attendant PHP programming.
如果选择MySQLi，可能不会。 当您选择MySQL i扩展时，您的查询需要非常适度的更改（如果有）。 PDO是另外一个故事。 它使用完全不同的查询结构以及准备好的语句和绑定数据。 PDO不会在其查询字符串中放入PHP变量； 它分别传递变量，因此需要更改几乎所有查询字符串以及附带PHP编程。
Do I Have to Change my Data Base Tables?
我是否需要更改数据库表？
No. We are changing the API extensions, but we are not changing the underlying data base tables or the essential nature of the SQL query language.
不会。我们正在更改API扩展，但是我们没有更改基础数据库表或SQL查询语言的本质。
This Sounds Like a Huge Amount of Work
这听起来像是大量的工作
Yes. You almost certainly want to use some kind of version control. But it does not have to be a daunting "all-or-nothing" task. You can make simultaneous connections to the MySQL server using MySQLi alongside existing MySQL API code, in the same PHP script, and you can change your queries one at a time. I have not tested it, but I would expect that the same might be true for PDO.
是。 您几乎肯定要使用某种版本控制 。 但这不必是一项艰巨的“全有或全无”任务。 您可以在同一PHP脚本中使用MySQL i和现有MySQL API代码同时连接到MySQL服务器，并且可以一次更改一个查询。 我尚未对其进行测试，但是我希望PDO可能也是如此。
What Will I Learn Here?
我在这里学到什么？
Each of these examples performs the same basic data base functions. We connect to the server and select a data base, we CREATE a table and load it with data. We run a SELECT query and count the results. We show the results set (using two methods of access to the rows of the results set). We DELETE a row from the table. And finally we run a query that is designed to fail so that we can see the differences in the error output. You can print out these three scripts and follow the logic as you read the annotation and watch them run.
这些示例均执行相同的基本数据库功能。 我们连接到服务器并选择一个数据库，我们创建一个表并向其中加载数据。 我们运行一个SELECT查询并计算结果。 我们显示结果集（使用两种方法访问结果集的行）。 我们从表中删除一行。 最后，我们运行一个旨在失败的查询，以便我们可以看到错误输出中的差异。 您可以打印出这三个脚本，并在阅读注释并观看它们运行时遵循逻辑。
Man Page References
手册页参考
You will need to read these online references to understand the code in the snippets below. No excuses, just do it. You might even want to bookmark the PHP man pages. You may also want to review this web page that provides a map of MySQL functions to the MySQLi and PDO extensions.
您需要阅读这些在线参考资料，以了解下面的代码片段中的代码。 没有任何借口，那就去做。 您甚至可能要为PHP手册页添加书签。 您可能还需要查看该网页，该网页提供了MySQL函数到MySQL i和PDO扩展的映射 。
MySQL
MySQL
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php
MySQLi
MySQL 我
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
PDO
PDO
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND PDO/MYSQL
// MAN PAGE: http://php.net/manual/en/book.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdoexception.php
// MAN PAGE: http://php.net/manual/en/class.pdostatement.php
// MAN PAGE: http://php.net/manual/en/pdo.construct.php
// MAN PAGE: http://php.net/manual/en/pdo.setattribute.php
// MAN PAGE: http://php.net/manual/en/pdo.query.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php#97942 <-- NO CURSOR SCROLLING
// MAN PAGE: http://php.net/manual/en/pdostatement.execute.php
// MAN PAGE: http://php.net/manual/en/pdo.lastinsertid.php
// MAN PAGE: http://php.net/manual/en/pdostatement.bindparam.php
// MAN PAGE: http://php.net/manual/en/pdostatement.rowcount.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchall.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchobject.php
Test Data
测试数据
Identical for MySQL, MySQLi, and PDO. It's the first thing you need if you're going to write computer programs. In fact it's so important that a wise programmer will write the test data first, before a single line of code is laid!
与MySQL，MySQL i和PDO相同。 如果要编写计算机程序，这是第一件事。 实际上，非常重要的一点是，明智的程序员必须在编写单行代码之前首先编写测试数据 ！
// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
Authentication Credentials
认证凭证
Identical for MySQL, MySQLi, and PDO. Data base authentication credentials are the same for these extensions, however PDO uses the concept of a Data Source Name (combining the Data Base Engine and the Data Base Name).
与MySQL，MySQL i和PDO相同。 这些扩展的数据库身份验证凭据相同，但是PDO使用数据源名称的概念（将数据库引擎和数据库名称组合在一起）。
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
Connect to the Server and Choose the Data Base
连接到服务器并选择数据库
Each of these connections has very different information available in the DB connection resource or object. For MySQL, the connection is a resource that does not tell us very much. For MySQLi, the connection is an object with a rich collection of information. For PDO, the connection is an object but it has no visible properties at this point.
这些连接中的每一个在DB连接资源或对象中都有非常不同的信息。 对于MySQL，连接是一种资源，不会告诉我们太多。 对于MySQL i来说 ，连接是一个具有丰富信息集合的对象。 对于PDO，连接是一个对象，但此时没有可见的属性。
MySQL uses connect and select function calls. Errors can be visualized using the appropriate error messages which can be found by calling the relevant MySQL functions.
MySQL使用connect和select函数调用。 可以使用适当的错误消息将错误可视化，这些错误消息可以通过调用相关MySQL函数找到。
MySQLi connects to the DB server by instantiation of a new MySQLi object. Errors, if any, can be found by visualizing the appropriate error messages which are properties of the MySQLi object.
MySQL i通过实例化一个新MySQL i对象连接到数据库服务器。 通过可视化相应的错误消息（MySQL i对象的属性）可以发现错误（如果有）。
Connect/select proceeds a bit differently in PDO. Check the cautionary notes about accidental exposure of PDO authentication data. For this reason we wrap the instantiation of the PDO object in try/catch code blocks so we can handle the exceptions in our own code, rather than having an uncaught exception expose information that should not be made public. More information on the PDOException object can be found in the online man pages. PDO uses the Data Source Name ("DSN") that includes both the host name and the data base name.
连接/选择在PDO中的进行方式略有不同。 检查有关意外暴露PDO身份验证数据的警告说明。 因此，我们将PDO对象的实例包装在try / catch代码块中，以便我们可以在自己的代码中处理异常 ，而不是使未捕获的异常暴露不应公开的信息。 有关PDOException对象的更多信息，请参见在线手册页。 PDO使用包含主机名和数据库名称的数据源名称（“ DSN”）。
MySQL
MySQL
// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$dbcon = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$err
= "NO DB CONNECTION: $db_host: "
. mysql_errno()
. ' '
. mysql_error()
;
trigger_error($err, E_USER_WARNING);
}
// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $dbcon))
{
$err
= "NO DB SELECTION: $db_name: "
. mysql_errno()
. ' '
. mysql_error()
;
trigger_error($err, E_USER_WARNING);
trigger_error('NO DATABASE', E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION LOOKS LIKE
var_dump($dbcon);
MySQLi
MySQL 我
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);
PDO
PDO
// WARNING: AN UNCAUGHT CONNECT ERROR WILL BARK OUT THE DB CREDENTIALS!
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
$pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error('NO PDO Connection', E_USER_ERROR);
}
// SHOW THE PDO CONNECTION OBJECT
var_dump($pdo);
PDO - Error Visualization
PDO-错误可视化
This sets the attributes of the PDO object to control what warnings and exceptions we can see. Without these settings, PDO is silent abouterror conditions. This concept is similarly applicable to MySQL and MySQLi, where we have to control error visualization on a query-by-query basis.
这将设置PDO对象的属性，以控制我们可以看到哪些警告和异常。 如果没有这些设置， PDO将对 错误情况 保持沉默 。 这个概念类似地适用于MySQL和MySQL i ，我们必须在逐个查询的基础上控制错误的可视化。
// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
CREATE TABLE Query Strings
创建表查询字符串
Identical for MySQL, MySQLi, and PDO. Even though our teaching example has a very simple table structure, we will be able to see that (1) the same structure works in all extensions and (2) the same query results can be retrieved in all extensions.
与MySQL，MySQL i和PDO相同。 即使我们的教学示例的表结构非常简单，我们也可以看到（1）在所有扩展中都可以使用相同的结构，并且（2）在所有扩展中都可以检索到相同的查询结果。
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
Run the CREATE TABLE Query and Handle Errors/Exceptions
运行CREATE TABLE查询并处理错误/异常
MySQL and MySQLi extensions use a similar-looking construct to run the query. MySQL calls the mysql_query() function. MySQLi calls the query() method on the MySQLi object. The PDO extension calls the query() method on the PDO object. We wrap this in a try/catch block that will enable us to handle any exceptional conditions. The exception, if any, will be reported to the script in the PDOException object.
MySQL和MySQL i扩展使用外观相似的结构来运行查询。 MySQL调用mysql_query（）函数。 MySQL i在MySQL i对象上调用query（）方法。 PDO扩展在PDO对象上调用query（）方法。 我们将其包装在try / catch块中，这将使我们能够处理任何特殊情况。 异常（如果有）将报告给PDOException对象中的脚本。
MySQL
MySQL
// RUN THE QUERY TO CREATE THE TABLE
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);
MySQLi
MySQL 我
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);
PDO
PDO
// RUN QUERY TO CREATE THE TABLE
try
{
$pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($pdos);
PDO - Prepare a Query
PDO-准备查询
This code prepares a query for use by the PDO object. We only need to prepare the query once, then it can be reused over and over with different data. Since the query and the data are sent to the SQL engine separately, the SQL statement is protected from SQL injection. The results of query preparation are returned to us in the form of a PDOStatement object, stored in $pdos. Take note of line 6. This is where we tell PDO what our input data looks like. The colon-prefixed names are array indexes. Important: Do not put quotes around these colon-prefixed fields! They are named placeholders, not variables that might be used in MySQLi queries. You can see how this lines things up if you look back at the array keys in the test data arrays. After the query is prepared, we can call the execute() method repeatedly, passing associative arrays with named keys. There is a query preparation analog for MySQLi, but no such preparation method for MySQL -- with MySQL we must remember to escape the data every time, and failure to escape the data correctly could result in a failed query. Line 11 calls the prepare() method on the PDO object and returns the PDOStatement object into $pdos.
这段代码准备了一个查询 ，供PDO对象使用。 我们只需要准备一次查询，然后就可以反复使用不同的数据。 由于查询和数据分别发送到SQL引擎，因此可以保护SQL语句免受SQL注入。 查询准备的结果以PDOStatement对象的形式返回给我们，该对象存储在$ pdos中 。 注意第6行。这是我们告诉PDO输入数据的样子的地方。 冒号前缀的名称是数组索引。 重要提示：请勿在这些冒号前缀的字段周围加引号！ 它们被命名为占位符，而不是在MySQL i查询中可能使用的变量。 如果您回头看一下测试数据数组中的数组键，您会看到这是如何排列的。 准备好查询后，我们可以重复调用execute（）方法，并传递带有命名键的关联数组。 MySQL i有一个查询准备类似物，但MySQL没有这样的准备方法-使用MySQL我们必须记住每次都转义数据，而未能正确转义数据可能会导致查询失败。 第11行调用PDO对象上的prepare（）方法，并将PDOStatement对象返回到$ pdos中 。
PDO
PDO
// PREPARE THE QUERY TO LOAD THE NAMES ("PREPARE" ONLY NEEDED ONCE)
$sql
=
"INSERT INTO my_table
( fname, lname ) VALUES
( :fname, :lname )
"
;
try
{
$pdos = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
Use an Iterator to Escape the Data and Load the Table
使用迭代器转义数据并加载表
The programs use identical iterators, but the method calls and sequences are different. Unlike PDO which uses prepared query statements, the MySQL and MySQLi extensions must call the mysql_real_escape_string() function or the real_escape_string() method, then use the escaped output to create the query string.
程序使用相同的迭代器，但方法调用和顺序不同。 与使用准备好的查询语句的PDO不同，MySQL和MySQL i扩展必须调用mysql_real_escape_string（）函数或real_escape_string（）方法，然后使用转义的输出来创建查询字符串。
MySQL and MySQLi make the expected function/method calls. PDO uses the execute() method on the prepared PDOStatement object.
MySQL和MySQL 我进行了预期的函数/方法调用。 PDO在准备好的PDOStatement对象上使用execute（）方法。
To test for query success and show any applicable error information, MySQL and MySQLi test for FALSE return values from the function/method calls, and execute the error handler code if necessary. PDO catches the PDOException object that would be thrown by a failing PDOStatement object.
为了测试查询是否成功并显示任何适用的错误信息，MySQL和MySQL i测试了来自函数/方法调用的FALSE返回值，并在必要时执行错误处理程序代码。 PDO捕获失败的PDOStatement对象将抛出的PDOException对象。
The last insert id (AUTO_INCREMENT KEY) is retrieved on a per-connection basis. This means that MySQL uses the data base connection resource in its call to mysql_insert_id(). For MySQLi, the number can be found in the insert_id property of the MySQLi Object. For PDO, the script calls the lastInsertId() method on the PDO object.
在每个连接的基础上检索最后一个插入ID （AUTO_INCREMENT KEY）。 这意味着MySQL在对mysql_insert_id（）的调用中使用数据库连接资源。 对于MySQL i ，可以在MySQL i对象的insert_id属性中找到该数字。 对于PDO，该脚本在PDO对象上调用lastInsertId（）方法。
MySQL
MySQL
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = mysql_real_escape_string($person['fname']);
$safe_ln = mysql_real_escape_string($person['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id = mysql_insert_id($dbcon);
echo "MySQL INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
MySQLi
MySQL 我
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = $mysqli->real_escape_string($person['fname']);
$safe_ln = $mysqli->real_escape_string($person['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
PDO
PDO
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// USE THE ARRAY OF KEYWORD => VALUE TO ATTACH fname AND lname
try
{
// RUN THE QUERY TO INSERT THE ROW
$pdos->execute($person);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id = $pdo->lastInsertId();
echo "PDO INSERTED A ROW CONTAINING <b>" . $person['fname'] . ' ' . $person['lname']. "</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
Create and Execute a SELECT Query
创建并执行SELECT查询
Both MySQL and MySQLi extensions follow similar patterns to escape the external data and substitute the escaped string into the SQL statement. After the query has been run MySQL has a resource id in $res and MySQLi has a results object.
MySQL和MySQL i扩展都遵循类似的模式来转义外部数据并将转义的字符串替换为SQL语句。 运行查询后，MySQL在$ res中有一个资源ID，而MySQL i有一个result对象。
With the PDO extension we do not need to escape the external data because the data is not put into the query string, but is sent to the SQL engine separately. Instead we create the query string using the colon-prefixed name that we will pass to the bindParam() method. We prepare the query on line 7 of the PDO example.
使用PDO扩展，我们不需要转义外部数据，因为该数据不会放入查询字符串中，而是会单独发送到SQL引擎。 相反，我们使用冒号前缀的名称创建查询字符串，该名称将传递给bindParam（）方法。 我们在PDO示例的第7行上准备查询。
Note that the prepare process is intended to give us a scrollable results set "cursor" which would, in theory, enable us to retrieve the rows of the results set in any order we chose. But alas, this tool is unavailable in PHP's implementation of PDO for MySQL data bases. In spite of being a high-value request, PHP has not implemented it. Next, we bind the :fname column with the $fname variable using the PDO::PARAM_STR constant to tell PDO that this is a string binding and not an array binding. Then we try to execute the query.
请注意，准备过程旨在为我们提供可滚动的结果集“游标”，从理论上讲，这将使我们能够以我们选择的任何顺序来检索结果集的行。 但是，可惜，该工具在PHPMySQL数据库PDO实现中不可用。 尽管是高价值的请求 ，PHP仍未实现它。 下一步，我们将结合：与使用PDO :: PARAM_STR常量告诉PDO，这是一个字符串约束力，而不是一个数组绑定$ FNAME变量FNAME列。 然后，我们尝试执行查询。
MySQL
MySQL
// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname = "RAY";
$safe_fn = mysql_real_escape_string($fname);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// IF SCRIPT GETS HERE WE HAVE A RESOURCE-ID IN $res
MySQLi
MySQL 我
// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname = "RAY";
$safe_fn = $mysqli->real_escape_string($fname);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = $mysqli->query($sql);
// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// IF SCRIPT GETS HERE WE HAVE A RESULT OBJECT IN $res
PDO
PDO
$fname = "RAY";
// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT id, lname FROM my_table WHERE fname = :fname ORDER BY lname, fname";
// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':fname', $fname, PDO::PARAM_STR);
try
{
$pdos->execute();
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
Find the Number of Rows in the Results Set
在结果集中找到行数
For MySQL, the number is found by calling the mysql_num_rows() function and passing it the $res resource from the query. With MySQLi, the number is found in the num_rows property of the $resmysqli_result object. With PDO, the number is usually found by calling the rowCount() method on the PDOStatement object. But... the rowCount() method may not always give you exactly what you want to know! Please see the man page for rowCount() and read the user-contributed notes to learn about the potential ambiguities.
对于MySQL，可通过调用mysql_num_rows（）函数并从查询中传递$ res资源来找到该数字。 在MySQL i中 ，该数字位于$ res mysqli_result对象的num_rows属性中。 使用PDO， 通常可以通过在PDOStatement对象上调用rowCount（）方法来找到数字。 但是 ... rowCount（）方法可能无法始终为您提供您想知道的确切信息！ 请查看rowCount（）的手册页，并阅读用户提供的注释以了解潜在的歧义。
MySQL
MySQL
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
MySQLi
MySQLi的
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
PDO
PDO
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
Retrieve the Rows of the Results Set
检索结果集的行
Using MySQL, the rows are retrieved using the mysql_fetch_array() function . This function was chosen to illustrate why you should not use this function. Unless you tell it otherwise, it retrieves twice as much data as is necessary to bring back the answers! The output from print_r() will show what is going on.
使用MySQL，可使用mysql_fetch_array（）函数检索行。 选择这个功能来说明为什么你不应该使用此功能。 除非另有说明，否则它检索的数据量是获得答案所需的两倍！ print_r（）的输出将显示正在发生的情况。
With MySQLi, the rows are retrieved using the fetch_array() method on the MySQLI_Result object. Like mysql_fetch_array() this method retrieves twice as much data is is needed to process the results. Better choices are shown below
使用MySQL i时 ，使用MySQLI_Result对象上的fetch_array （）方法检索行。 像mysql_fetch_array（）一样，此方法检索需要两倍的数据来处理结果。 更好的选择如下所示
In the PDO example, the rows are retrieved into an array of objects by calling the fetchAll() method on the PDOStatement object. Each of these objects has a property with the name of the column name and a value equal to the column value in the respective row. You can also retrieve the rows into a "record set" structure, which is a two-dimensional array of arrays, where the main array contains all of the rows, and each row is represented by an associative array. A useful function for processing record sets is array_column().
在PDO示例中，通过在PDOStatement对象上调用fetchAll（）方法将行检索到对象数组中。 这些对象中的每个对象都有一个属性，该属性具有列名的名称和一个等于相应行中列值的值。 您还可以将行检索到“记录集”结构中，该结构是数组的二维数组，其中主数组包含所有行，并且每一行均由关联数组表示。 处理记录集的有用函数是array_column（） 。
MySQL
MySQL
// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_array($res))
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
MySQLi
MySQL 我
// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_array())
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
PDO
PDO
// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "USING PDOStatement::FetchAll(PDO::FETCH_OBJ): ";
echo "<br/>" . PHP_EOL;
while ($row = $pdos->fetchAll(PDO::FETCH_OBJ))
{
// ROW BY ROW PROCESSING IS DONE HERE
foreach ($row as $key => $obj)
{
echo "$key: ";
print_r($obj);
echo PHP_EOL;
}
}
Reset the Results Set Pointer
重置结果集指针
MySQL and MySQLi can reset the results set pointer in the existing results set. We do not need to rerun the query to get to the same data for a different view. The PHP MySQL PDO implementation does not support this. The first row of the results set is numbered the same way as the first element of an array. Numbering starts with zero.
MySQL和MySQL i可以在现有结果集中重置结果集指针。 我们不需要重新运行查询即可获得不同视图的相同数据。 PHP MySQL PDO实现不支持此功能。 结果集的第一行的编号方式与数组的第一元素相同。 编号从零开始。
MySQL
MySQL
// RESET THE RESULTS SET POINTER TO THE TOP
mysql_data_seek($res,0);
MySQLi
MySQL 我
// RESET THE RESULTS SET POINTER TO THE TOP
$res->data_seek(0);
Retrieve the Rows of the Results Set (Again)
检索结果集的行（再次）
With MySQL and MySQLi, each row is retrieved in the form of an object with the column name representing a property of the object that points to the value from the row of the database. In PDO, a second retrieval is not possible; the query must be rerun, or the original results set must be saved in PHP variables so the values can be reused.
在MySQL和MySQL i中 ，每一行都以对象的形式检索，其列名表示该对象的属性，该属性指向数据库行中的值。 在PDO中，第二次检索是不可能的。 该查询必须重新运行，否则原始结果集必须保存在PHP变量中，以便可以重复使用这些值。
Note that retrieving the rows in the form of objects creates an easy-to-use syntax to incorporate the results inHEREDOCvariables! If you retrieve the rows in the form of arrays, the quoted variables must be encapsulated in curly braces for HEREDOC, making the syntax inconvenient and much harder to get right.
请注意，以对象形式检索行将创建易于使用的语法，以将结果合并到 HEREDOC 变量中！ 如果您以数组的形式检索行，则引号中的变量必须用大括号括起来以用于HEREDOC，这使语法不方便并且更难于正确使用。
MySQL
MySQL
// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_object($res))
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
MySQLi
MySQL 我
// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_object())
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
Delete a Row From the Table
从表中删除行
MySQL and MySQLi extensions follow similar patterns to escape the external data and substitute the escaped string into the SQL statement. The PDO extension uses the by-now-familiar :name notation with colons prepended to the array index name to prepare the query statement and bind the PHP variable. A try/catch block encloses the call to the query handler.
MySQL和MySQL i扩展遵循类似的模式来转义外部数据并将转义的字符串替换为SQL语句。 PDO扩展使用由-现在熟悉：名称与符号前面加上数组索引名准备查询语句和绑定PHP变量冒号。 try / catch块将对查询处理程序的调用括起来。
MySQL
MySQL
// DELETE A ROW FROM THE TABLE
$lname = "O'Reilly";
$safe_ln = mysql_real_escape_string($lname);
// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
MySQLi
MySQL 我
// DELETE A ROW FROM THE TABLE
$lname = "O'Reilly";
$safe_ln = $mysqli->real_escape_string($lname);
// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
PDO
PDO
// DELETE A ROW FROM THE TABLE
$lname = "O'Reilly";
// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = :lname LIMIT 33";
// BIND THE VARIABLE AND TRY THE QUERY
$pdos = $pdo->prepare($sql);
$pdos->bindParam(':lname', $lname, PDO::PARAM_STR);
try
{
$pdos->execute();
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
Determine the Number of Affected Rows
确定受影响的行数
MySQL calls the mysql_affected_rows() function referencing the data base connection. MySQLi reads the affected_rows property from the MySQLi object. PDO calls the rowCount() method on the PDOStatement object.
MySQL调用引用数据库连接的mysql_affected_rows（）函数。 MySQL i从MySQL i对象读取受影响的行属性。 PDO调用PDOStatement对象上的rowCount（）方法。
MySQL
MySQL
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = mysql_affected_rows($dbcon);
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
MySQLi
MySQL 我
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = $mysqli->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
PDO
PDO
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
Visualize Error and Exception Information
可视化错误和异常信息
A defective query is created to cause a failure of the MySQL server. Each of the MySQL extensions creates its own error information, which is displayed by the script. The PDOException object is far more comprehensive in its information. It would require considerable programming to get this kind of detailed information from MySQL or MySQLi. In the case of PDO, note that the PDOStatement object is not modified by the query failure and still contains the previous query statement.
创建有缺陷的查询会导致MySQL服务器故障。 每个MySQL扩展都创建自己的错误信息，该错误信息由脚本显示。 PDOException对象的信息要全面得多。 要从MySQL或MySQL i获得此类详细信息，将需要进行大量编程。 对于PDO，请注意PDOStatement对象不会因查询失败而被修改，并且仍包含先前的查询语句。
MySQL
MySQL
// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = mysql_query($sql);
// SHOW THE ERROR INFORMATION
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
echo $err;
}
QUERY FAIL: SELECT oopsie FROM my_table ERRNO: 1054 ERROR: Unknown column 'oopsie' in 'field list'
MySQLi
MySQL 我
// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = $mysqli->query($sql);
// SHOW THE ERROR INFORMATION
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
echo $err;
}
QUERY FAIL: SELECT oopsie FROM my_table ERRNO: 1054 ERROR: Unknown column 'oopsie' in 'field list'
PDO
PDO
// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
try
{
$pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
// SHOW PDOException AND PDOStatement
var_dump($exc);
var_dump($pdos);
}
object(PDOException)#5 (8) {
["message":protected]=>
string(79) "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'oopsie' in 'field list'"
["string":"Exception":private]=>
string(0) ""
["code":protected]=>
string(5) "42S22"
["file":protected]=>
string(49) "/home/account/public_html/RAY_EE_pdo_example.php"
["line":protected]=>
int(284)
["trace":"Exception":private]=>
array(1) {
[0]=>
array(6) {
["file"]=>
string(49) "/home/account/public_html/RAY_EE_pdo_example.php"
["line"]=>
int(284)
["function"]=>
string(5) "query"
["class"]=>
string(3) "PDO"
["type"]=>
string(2) "->"
["args"]=>
array(1) {
[0]=>
string(28) "SELECT oopsie FROM my_table "
}
}
}
["previous":"Exception":private]=>
NULL
["errorInfo"]=>
array(3) {
[0]=>
string(5) "42S22"
[1]=>
int(1054)
[2]=>
string(39) "Unknown column 'oopsie' in 'field list'"
}
}
object(PDOStatement)#3 (1) {
["queryString"]=>
string(50) "DELETE FROM my_table WHERE lname = :lname LIMIT 33"
}
Conclusion
结论
With more than 8 years of experience using the PDO Extension API, we can be confident that the API is debugged and ready for "prime time." The same can be said for the MySQLi Extension. And given the current status of MySQL, it's time to upgrade our scripts. This article has shown some links to the relevant man pages and some examples that illustrate the programming changes required to keep our programming running in the modern age.
拥有8年以上使用PDO Extension API的经验，我们可以确信该API已调试并且可以在“黄金时段”使用。 MySQL i Extension也可以这样说。 鉴于MySQL的当前状态 ，是时候升级我们的脚本了。 本文显示了一些指向相关手册页的链接，以及一些示例，这些示例说明了使程序保持现代运行所需的编程更改。
The MySQL Obsolete Complete Code Example
MySQL 过时的完整代码示例
<?php // RAY_EE_mysql_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php
// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$dbcon = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$err
= "NO DB CONNECTION: $db_host: "
. mysql_errno()
. ' '
. mysql_error()
;
trigger_error($err, E_USER_WARNING);
}
// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $dbcon))
{
$err
= "NO DB SELECTION: $db_name: "
. mysql_errno()
. ' '
. mysql_error()
;
trigger_error($err, E_USER_WARNING);
trigger_error('NO DATABASE', E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION LOOKS LIKE
var_dump($dbcon);
echo PHP_EOL;
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = mysql_real_escape_string($person['fname']);
$safe_ln = mysql_real_escape_string($person['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id = mysql_insert_id($dbcon);
echo "MySQL INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;
// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname = "RAY";
$safe_fn = mysql_real_escape_string($fname);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_array($res))
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
echo PHP_EOL;
// RESET THE RESULTS SET POINTER TO THE TOP
mysql_data_seek($res,0);
// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_object($res))
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
echo PHP_EOL;
// DELETE A ROW FROM THE TABLE
$lname = "O'Reilly";
$safe_ln = mysql_real_escape_string($lname);
// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = mysql_query($sql);
// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
trigger_error($err, E_USER_ERROR);
}
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = mysql_affected_rows($dbcon);
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = mysql_query($sql);
// SHOW THE ERROR INFORMATION
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. mysql_errno()
. ' ERROR: '
. mysql_error()
;
echo $err;
}
The MySQLi Complete Code Example
MySQL i完整代码示例
<?php // RAY_EE_mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);
echo PHP_EOL;
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$safe_fn = $mysqli->real_escape_string($person['fname']);
$safe_ln = $mysqli->real_escape_string($person['lname']);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";
// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;
// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname = "RAY";
$safe_fn = $mysqli->real_escape_string($fname);
// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = $mysqli->query($sql);
// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_array())
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
echo PHP_EOL;
// RESET THE RESULTS SET POINTER TO THE TOP
$res->data_seek(0);
// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_object())
{
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
echo PHP_EOL;
// DELETE A ROW FROM THE TABLE
$lname = "O'Reilly";
$safe_ln = $mysqli->real_escape_string($lname);
// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = $mysqli->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = $mysqli->query($sql);
// SHOW THE ERROR INFORMATION
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
echo $err;
}
The PDO Complete Code Example
PDO完整代码示例
<?php // RAY_EE_pdo_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND PDO/MYSQL
// MAN PAGE: http://php.net/manual/en/book.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdoexception.php
// MAN PAGE: http://php.net/manual/en/class.pdostatement.php
// MAN PAGE: http://php.net/manual/en/pdo.construct.php
// MAN PAGE: http://php.net/manual/en/pdo.setattribute.php
// MAN PAGE: http://php.net/manual/en/pdo.query.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php#97942 <-- NO CURSOR SCROLLING
// MAN PAGE: http://php.net/manual/en/pdostatement.execute.php
// MAN PAGE: http://php.net/manual/en/pdo.lastinsertid.php
// MAN PAGE: http://php.net/manual/en/pdostatement.bindparam.php
// MAN PAGE: http://php.net/manual/en/pdostatement.rowcount.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchall.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchobject.php
// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// WARNING: AN UNCAUGHT CONNECT ERROR WILL BARK OUT THE DB CREDENTIALS!
// $pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, 'foo');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
$pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE PDO CONNECTION OBJECT
var_dump($pdo);
echo PHP_EOL;
// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
$pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($pdos);
// PREPARE THE QUERY TO LOAD THE NAMES ("PREPARE" ONLY NEEDED ONCE)
$sql
=
"INSERT INTO my_table
( fname, lname ) VALUES
( :fname, :lname )
"
;
try { $pdos = $pdo->prepare($sql); } catch(PDOException $exc) { var_dump($exc); }
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
// USE THE ARRAY OF KEYWORD => VALUE TO ATTACH fname AND lname
try
{
// RUN THE QUERY TO INSERT THE ROW
$pdos->execute($person);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id = $pdo->lastInsertId();
echo "PDO INSERTED A ROW CONTAINING <b>" . $person['fname'] . ' ' . $person['lname']. "</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;
// SOMETHING TO SEARCH FOR
$fname = "RAY";
// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT id, lname FROM my_table WHERE fname = :fname ORDER BY lname, fname";
// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':fname', $fname, PDO::PARAM_STR);
try
{
$pdos->execute();
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo "<br/>" . PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo "<br/>" . PHP_EOL;
}
echo PHP_EOL;
// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "USING PDOStatement::FetchAll(PDO::FETCH_OBJ): ";
echo "<br/>" . PHP_EOL;
while ($row = $pdos->fetchAll(PDO::FETCH_OBJ))
{
// ROW BY ROW PROCESSING IS DONE HERE
foreach ($row as $key => $obj)
{
echo "$key: ";
print_r($obj);
echo PHP_EOL;
}
}
echo PHP_EOL;
// DELETE A ROW FROM THE TABLE
$lname = "O'Reilly";
// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = :lname LIMIT 33";
// BIND THE VARIABLE AND TRY THE QUERY
$pdos = $pdo->prepare($sql);
$pdos->bindParam(':lname', $lname, PDO::PARAM_STR);
try
{
$pdos->execute();
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
try
{
$pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
// SHOW PDOException AND PDOStatement
var_dump($exc);
var_dump($pdos);
}
The MySQLi Complete Code Example Using Prepared Statements
使用预备语句MySQL i完整代码示例
<?php // demo/EE_mysqli_prepare_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-param.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.execute.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.store-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.fetch.php
// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray" , "lname" => "Paseur" )
, array( "fname" => "Bill" , "lname" => "O'Reilly" )
, array( "fname" => "Ray" , "lname" => "Capece" )
, array( "fname" => "John" , "lname" => "Paseur" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);
echo PHP_EOL;
// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
$err
= "QUERY FAIL: "
. $sql
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);
// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( ?,? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
$err
= "QUERY PREPARE() FAIL: "
. $sql
. '<br>ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
// BIND INPUT VARIABLES TO THE QUERY
$ins->bind_param('ss', $person['fname'], $person['lname']);
// RUN THE QUERY TO INSERT THE ROW
$ins->execute();
// GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
$id = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['fname']}, {$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;
// PREPARE A SELECT QUERY
$sql = "SELECT id, lname FROM my_table WHERE (fname=? AND lname=?) OR id=? ORDER BY lname, fname";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
$err
= "QUERY PREPARE() FAIL: "
. $sql
. '<br>ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
// BIND INPUT VARIABLES TO THE QUERY
$fname = "Ray";
$lname = "Paseur";
$id = 1;
$sel->bind_param('ssi', $fname, $lname, $id);
// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($my_id, $my_lname);
// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();
// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>FOUND $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
echo "id='$my_id' lname='$my_lname' ";
echo PHP_EOL;
}
// RELEASE THE OBJECT
$sel->close();
// PREPARE A DELETE QUERY
$sql = "DELETE FROM my_table WHERE lname=? LIMIT 33";
$del = $mysqli->prepare($sql);
// BIND THE INPUT VARIABLE TO THE QUERY
$lname = "O'Reilly";
$del->bind_param('s', $lname);
// RUN THE QUERY
$del->execute();
// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num = $del->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED NO DATA ";
echo PHP_EOL;
}
else
{
echo "<br/>QUERY: $sql ";
echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
echo PHP_EOL;
}
echo PHP_EOL;
// CONSTRUCT A QUERY THAT WILL FAIL AT PREPARE TIME
$sql = "SELECT oopsie FROM my_table ";
$sel = $mysqli->prepare($sql);
// SHOW THE ERROR INFORMATION
if (!$sel)
{
$err
= "QUERY PREPARE() FAIL: "
. $sql
. '<br>ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
Please give us your feedback!
请给我们您的反馈意见！
If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles. If you have questions or comments, please add them. Thanks!
如果您发现本文有帮助，请单击下面的“竖起大拇指”按钮。 这样做可以使EE社区了解对EE成员有价值的内容，并为将来的文章提供指导。 如果您有任何问题或意见，请添加。 谢谢！
翻译自: https://www.experts-exchange.com/articles/11177/Why-PHP-Removed-Support-for-the-MySQL-API.html
mysql api
543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
