php 如何正确设置 PDO 连接

How to properly set up a PDO connection

提问by ThomasK

From time to time I see questions regarding connecting to database.
Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I've never thought about it because the way I do it works for me.

我不时看到有关连接到数据库的问题。
大多数答案不是我做的方式，或者我可能只是没有正确得到答案。反正; 我从来没有想过它，因为我这样做的方式对我有用。

But here's a crazy thought; Maybe I'm doing this all wrong, and if that's the case; I would really like to know how to properly connect to a MySQL database using PHP and PDO and make it easy accessible.

但这是一个疯狂的想法；也许我做错了，如果是这样的话；我真的很想知道如何使用 PHP 和 PDO 正确连接到 MySQL 数据库并使其易于访问。

Here's how I'm doing it:

这是我的做法：

First off, here's my file structure (stripped down):

首先，这是我的文件结构（精简）：

public_html/

* index.php  

* initialize/  
  -- load.initialize.php  
  -- configure.php  
  -- sessions.php   

index.php
At the very top, I have require('initialize/load.initialize.php');.

index.php
在最顶部，我有require('initialize/load.initialize.php');.

load.initialize.php

加载初始化文件

#   site configurations
    require('configure.php');
#   connect to database
    require('root/somewhere/connect.php');  //  this file is placed outside of public_html for better security.
#   include classes
    foreach (glob('assets/classes/*.class.php') as $class_filename){
        include($class_filename);
    }
#   include functions
    foreach (glob('assets/functions/*.func.php') as $func_filename){
        include($func_filename);
    }
#   handle sessions
    require('sessions.php');

I know there's a better, or more correct, way to include classes, but can't remember what it was. Haven't gotten the time to look into it yet, but I think it was something with autoload. something like that...

我知道有一种更好或更正确的方法来包含类，但不记得是什么了。还没有时间研究它，但我认为它与autoload. 类似的东西...

configure.php
Here I basically just override some php.ini-properties and do some other global configuration for the site

configure.php
这里我基本上只是覆盖了一些php.ini-properties 并为站点做一些其他的全局配置

connect.php
I've put the connection onto a class so other classes can extendsthis one...

connect.php
我已经把连接放到一个类上，所以其他类可以扩展这个......

class connect_pdo
{
    protected $dbh;

    public function __construct()
    {
        try {
            $db_host = '  ';  //  hostname
            $db_name = '  ';  //  databasename
            $db_user = '  ';  //  username
            $user_pw = '  ';  //  password

            $con = new PDO('mysql:host='.$db_host.'; dbname='.$db_name, $db_user, $user_pw);  
            $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $con->exec("SET CHARACTER SET utf8");  //  return all sql requests as UTF-8  
        }
        catch (PDOException $err) {  
            echo "harmless error message if the connection fails";
            $err->getMessage() . "<br/>";
            file_put_contents('PDOErrors.txt',$err, FILE_APPEND);  // write some details to an error-log outside public_html  
            die();  //  terminate connection
        }
    }

    public function dbh()
    {
        return $this->dbh;
    }
}
#   put database handler into a var for easier access
    $con = new connect_pdo();
    $con = $con->dbh();
//

Here I do believe there's room for massive improvement since I recently started learning OOP, and using PDO instead of mysql.
So I've just followed a couple of beginners tutorials and tried out different stuff...

在这里，我相信自从我最近开始学习 OOP 并使用 PDO 而不是 mysql 以来，还有很大的改进空间。
所以我刚刚学习了几个初学者教程并尝试了不同的东西......

sessions.php
Beside handling regular sessions, I also initialize some classes into a session like this:

session.php
除了处理常规会话，我还将一些类初始化为这样的会话：

if (!isset($_SESSION['sqlQuery'])){
    session_start();
    $_SESSION['sqlQuery'] = new sqlQuery();
}

This way this class is available all over the place. This might not be good practice(?)...
Anyway, this is what this approach allows me to do from everywhere:

这样，这个类就可以在任何地方使用。这可能不是一个好的做法（？）......
无论如何，这就是这种方法允许我从任何地方做的事情：

echo $_SESSION['sqlQuery']->getAreaName('county',9);  // outputs: Aust-Agder (the county name with that id in the database)

Inside my sqlQuery-class, which extendsmy connect_pdo-class, I have a public function called getAreaNamewhich handles the request to my database.
Pretty neat I think.

在 my sqlQuery- class里面，extendsmy connect_pdo- class，我有一个公共函数调用getAreaName它处理对我的数据库的请求。
我觉得很整洁。

Works like a charm
So that's basically how I'm doing it.
Also, whenever I need to fetch something from my DB from not within a class, I just do something similar to this:

像魅力一样工作
所以这基本上就是我的做法。
此外，每当我需要从不在类中的数据库中获取某些内容时，我只需执行类似以下操作：

$id = 123;

$sql = 'SELECT whatever FROM MyTable WHERE id = :id';
$qry = $con->prepare($sql);
$qry -> bindParam(':id', $id, PDO::PARAM_INT);
$qry -> execute();
$get = $qry->fetch(PDO::FETCH_ASSOC);

Since I put the connection into a variable inside connect_pdo.php, I just have referring to it and I'm good to go. It works. I get my expected results...

由于我将连接放入connect_pdo.php 中的一个变量中，因此我只是参考了它，我很高兴。有用。我得到了预期的结果...

But regardless of that; I would really appreciate if you guys could tell me if I'm way off here. What I should do instead, areas I could or should change for improvement etc...

但不管怎样；如果你们能告诉我我是否离开这里，我将不胜感激。我应该做什么，我可以或应该改变的领域等......

I'm eager to learn...

我渴望学习...

回答by tere?ko

The goal

目标

As I see it, your aim in this case is twofold:

在我看来，你在这种情况下的目标是双重的：

• create and maintain a single/reusable connection per database
• make sure that the connection has been set up properly

• 为每个数据库创建和维护单个/可重用的连接
• 确保连接已正确设置

Solution

解决方案

I would recommend to use both anonymous function and factory pattern for dealing with PDO connection. The use of it would looks like this :

我建议同时使用匿名函数和工厂模式来处理 PDO 连接。它的使用看起来像这样：

$provider = function()
{
    $instance = new PDO('mysql:......;charset=utf8', 'username', 'password');
    $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    return $instance;
};

$factory = new StructureFactory( $provider );

Then in a different file or lower in the same file:

然后在不同的文件或同一文件中的更低版本中：

$something = $factory->create('Something');
$foobar = $factory->create('Foobar');

The factory itself should look something like this:

工厂本身应该是这样的：

class StructureFactory
{
    protected $provider = null;
    protected $connection = null;

    public function __construct( callable $provider )
    {
        $this->provider = $provider;
    }

    public function create( $name)
    {
        if ( $this->connection === null )
        {
            $this->connection = call_user_func( $this->provider );
        }
        return new $name( $this->connection );
    }

}

This way would let you have a centralized structure, which makes sure that connection is created only when required. It also would make the process of unit-testing and maintenance much easier.

这种方式可以让您拥有一个集中式结构，确保仅在需要时创建连接。它还将使单元测试和维护过程更加容易。

The provider in this case would be found somewhere at the bootstrap stage. This approach would also give a clear location where to define the configuration, that you use for connecting to the DB.

在这种情况下，提供程序将在引导阶段的某个地方找到。这种方法还提供了一个明确的位置来定义用于连接到数据库的配置。

Keep in mind that this is an extremely simplified example. You also might benefit from watching two following videos:

请记住，这是一个极其简化的示例。您还可以从观看以下两个视频中受益：

• Global State and Singletons
• Don't Look For Things!

• 全局状态和单身人士
• 不要找东西！

Also, I would strongly recommend reading a proper tutorialabout use of PDO (there are a log of bad tutorial online).

此外，我强烈建议您阅读有关 PDO 使用的正确教程（网上有一个糟糕教程的日志）。

回答by Ian Unruh

I would suggest not using $_SESSIONto access your DB connection globally.

我建议不要使用$_SESSION全局访问您的数据库连接。

You can do one of a few things (in order of worst to bestpractices):

您可以执行以下操作之一（按从最坏到最佳实践的顺序）：

• Access $dbhusing global $dbhinside of your functions and classes

• Use a singleton registry, and access that globally, like so:

  $registry = MyRegistry::getInstance();
  $dbh = $registry->getDbh();
  

• Inject the database handler into the classes that need it, like so:

  class MyClass {
      public function __construct($dbh) { /* ... */ }
  }
  

• $dbh使用global $dbh您的函数和类内部访问

• 使用单例注册表，并全局访问它，如下所示：

  $registry = MyRegistry::getInstance();
  $dbh = $registry->getDbh();
  

• 将数据库处理程序注入需要它的类，如下所示：

  class MyClass {
      public function __construct($dbh) { /* ... */ }
  }
  

I would highly recommend the last one. It is known as dependency injection (DI), inversion of control (IoC), or simply the Hollywood principle (Don't call us, we'll call you).

我强烈推荐最后一个。它被称为依赖注入 (DI)、控制反转 (IoC)，或者简称为好莱坞原则（不要打电话给我们，我们会打电话给你）。

However, it is a little more advanced and requires more "wiring" without a framework. So, if dependency injection is too complicated for you, use a singleton registry instead of a bunch of global variables.

但是，它更高级一些，并且需要更多的“布线”而没有框架。因此，如果依赖注入对您来说太复杂，请使用单例注册表而不是一堆全局变量。

回答by Francisco Presencia

I recently came to a similar answer/question on my own. This is what I did, in case anyone is interested:

我最近自己找到了一个类似的答案/问题。这就是我所做的，以防有人感兴趣：

<?php
namespace Library;

// Wrapper for \PDO. It only creates the rather expensive instance when needed.
// Use it exactly as you'd use the normal PDO object, except for the creation.
// In that case simply do "new \Library\PDO($args);" with the normal args
class PDO
  {
  // The actual instance of PDO
  private $db;

  public function __construct() {
    $this->args = func_get_args();
    }

  public function __call($method, $args)
    {
    if (empty($this->db))
      {
      $Ref = new \ReflectionClass('\PDO');
      $this->db = $Ref->newInstanceArgs($this->args);
      }

    return call_user_func_array(array($this->db, $method), $args);
    }
  }

To call it you only need to modify this line:

要调用它，您只需要修改这一行：

$DB = new \Library\PDO(/* normal arguments */);

And the type-hinting if you are using it to (\Library\PDO $DB).

以及类型提示，如果您将其用于 (\Library\PDO $DB)。

It's really similar to both the accepted answer and yours; however it has a notably advantage. Consider this code:

它与公认的答案和您的答案都非常相似；但是它有一个显着的优势。考虑这个代码：

$DB = new \Library\PDO( /* args */ );

$STH = $DB->prepare("SELECT * FROM users WHERE user = ?");
$STH->execute(array(25));
$User = $STH->fetch();

While it might look like normal PDO (it changes by that \Library\only), it actually doesn't initialize the object until you call the first method, whichever it is. That makes it more optimized, since the PDO object creation is slightly expensive. It's a transparent class, or what it's called a Ghost, a form of Lazy Loading. You can treat the $DB as a normal PDO instance, passing it around, doing the same operations, etc.

虽然它可能看起来像普通的 PDO（它\Library\只是通过那个改变），但它实际上不会在您调用第一个方法之前初始化对象，无论它是什么。这使得它更加优化，因为 PDO 对象创建稍微昂贵。它是一个透明的类，或者称为Ghost，一种形式的Lazy Loading。您可以将 $DB 视为一个普通的 PDO 实例，传递它，执行相同的操作等。

回答by hi-code

$dsn = 'mysql:host=your_host_name;dbname=your_db_name_here'; // define host name and database name
    $username = 'you'; // define the username
    $pwd='your_password'; // password
    try {
        $db = new PDO($dsn, $username, $pwd);
    }
    catch (PDOException $e) {
        $error_message = $e->getMessage();
        echo "this is displayed because an error was found";
        exit();
}