在PHP中使用PDO [英] Using PDO in PHP

问题描述

我在这里发现了许多与MySQL和设计查询有关的问题，我们总是告诉他们的第一件事是不要使用mysql_ *函数-它们已被弃用."因此，我想我将添加一个快速指南，以在您的PHP程序中设置PDO连接，以便我们在这里介绍新的PHP开发人员，并帮助使其程序更安全.

I find a lot of questions on here relating to MySQL and designing queries, and the first thing that we always tell them is "Don't use the mysql_* functions - they're being deprecated." So I thought I would add a quick guide to setting up a PDO connection in your PHP program so that we can refer new PHP developers here, and help make their programs more secure.

那些具有PHP和PDO经验的人，请随时进行编辑和添加必要的内容，以使其对PDO初学者更加有用.

Those experience in PHP and PDO, please feel free to edit and add where necessary to make this more useful for beginners in PDO.

推荐答案

步骤1: 设置新的PDO连接

这并不像有时所想的那么难.首先，您可以查找那些mysql_connect/mysql_select_db并将其替换为以下代码:

This is not nearly as hard as it is sometimes made out to be. To begin with, you can hunt down those mysql_connect/mysql_select_db and replace them with this code:

//Obviously, replace these with your own values
$host = 'host_name';
$dbname = 'database_name';
$user = 'user_name';
$pass = 'user_pass';
try
{
    $DB = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); 
}
catch(PDOException $e)
{  
    echo $e->getMessage();  
}

这将创建您的PDO对象，该对象具有mysql_ *调用的所有相同功能，然后具有某些功能.

This will create your PDO object which has all the same functionality of the mysql_* calls and then some.

第2步: 提交查询

一旦有了PDO对象，就可以开始使用它查询数据库.我们将首先看一个基本的选择查询，因为我们将使用的技术在大多数查询类型中都是相似的. 现在，您可以直接查询，但是这会消除PDO的某些功能.相反，我们可以使用准备好的语句.这样，PDO将为我们工作，以防止注入甚至意外查询损坏.这是一个示例:

once you have your PDO object, you can begin using it to query your database. We'll look at a basic select query first, since the techniques we'll use are similar in most query types. Now, you can query directly, but that takes away some of the power of PDO. Instead, we can use prepared statements. By doing so, PDO will work for us to prevent injection or even accidental query breakage. Here's an example:

$query = "SELECT * FROM table_name WHERE col1=':value1' AND col2=':value2'";
$statement = $DB->prepare($query);
$statement->execute(array(':value1' => 1, ':value2' => 2));

至此，我们已经查询了数据库，并在其中有一个语句对象，其中包含结果.这样做的好处是，代替执行语句中的1或2，我们可以使用用户生成的值，而无需检查SQL注入尝试，因为PDO会捕获它们并自动对其进行修复. (尽管，理所当然，在使用用户生成的值之前，您仍然应该检查它们是否存在.)

At this point, we've queried the database, and have a statement object with the results in it. The bonus here is that, in place of 1 or 2 in the execute statement, we could use a user-generated value, without even checking for SQL injection attempts, because PDO catches them and fixes them automatically. (Though, granted, you should still check that they exist before using user-generated values.)

第3步: 检索结果

现在，我们需要获取要搜索的数据，以便可以使用它.使用PDO非常简单，只需调用fetch命令，就像之前使用过 mysql_fetch_array()命令一样.您还希望将其包含在while循环中以检索所有结果，因为它的作用与 mysql_fetch_array()几乎相同.

Now, we need to get the data we were searching for, so we can use it. With PDO it's quite simple, all you need is to call the fetch command, just like you would have used that mysql_fetch_array() command before. You'll also want to include it in a while loop to retrieve all the results, since it acts almost identically to mysql_fetch_array().

//You can use several options in fetch, to determine what kind of results you get.
//PDO::FETCH_ASSOC -> gives you column names as the array indices
//PDO::FETCH_NUM   -> Gives you the column number as the indices
// By default, it uses PDO::FETCH_BOTH which does both.
while($row = $statement->fetch(PDO::FETCH_ASSOC))
{
     echo "Col1: " . $row['col1'] . "<br />";
     echo "Col2: " . $row['col2'] . "<br />";
     echo "Col3: " . $row['col3'] . "<br />";
     echo "Col4: " . $row['col4'];
}

显然，这是一个非常简单的布局，但是您可以看到它的工作方式，并可以根据需要对其进行修改.这和您当前的mysql_ *代码完全一样，只是它以更简单，更安全的方式进行了.

Obviously, this is a pretty simple layout, but you can see how it works, and can modify it for your needs. This does exactly the same thing as your current mysql_* code does, except it does it in a simpler more secure manner.

第4步: 可能性

从这里，您可以看到如何替换基本的mysql_ *函数.您也可以用PDO调用替换所有其他mysql函数，以下是一些示例:

From here, you can see how to replace your basic mysql_* functions. You can replace all other mysql functions with PDO calls as well, a few examples are:

mysql_num_rows() == $ statement-> rowCount()(执行查询后使用) mysql_real_escape_string() == 您甚至不需要它了！
mysql_insert_id() == $ statement-> lastinsertid()

mysql_num_rows() == $statement->rowCount() (used after you've executed your query) mysql_real_escape_string() == You don't even need this anymore!
mysql_insert_id() == $statement->lastinsertid()

有关PHP PDO使用的权威指南，请参见:
http://us3.php.net/manual/en/book.pdo. php

The definitive guide to PHP's PDO usage can be found here:
http://us3.php.net/manual/en/book.pdo.php

这是一个问题，着眼于mysqli vs PDO的优缺点:
mysqli或PDO-优缺点是什么?

And here is a question that looks at the strengths and weaknesses of mysqli vs PDO:
mysqli or PDO - what are the pros and cons?

这篇关于在PHP中使用PDO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！