PDO 연결을 올바르게 설정하는 방법

PDO 연결을 올바르게 설정하는 방법

---

때때로 데이터베이스 연결에 관한 질문을 봅니다.
대부분의 대답은 내가하는 방식이 아니거나 정확하게 대답을 얻지 못할 수도 있습니다. 어쨌든; 내가하는 방식이 나를 위해 일하기 때문에 나는 그것에 대해 생각해 본 적이 없습니다.

그러나 여기에 미친 생각이 있습니다. 아마도 내가이 모든 일을 잘못하고있을 수도 있고, 만약 그렇다면; PHP와 PDO를 사용하여 MySQL 데이터베이스에 올바르게 연결하고 쉽게 액세스 할 수 있도록하는 방법을 알고 싶습니다.

내가하는 방법은 다음과 같습니다.

첫째, 여기 내 파일 구조이다 (옷을 벗었) :

public_html/

* index.php  

* initialize/  
  -- load.initialize.php  
  -- configure.php  
  -- sessions.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');

클래스를 포함하는 더 낫거나 더 정확한 방법이 있다는 것을 알고 있지만 그것이 무엇인지 기억할 수 없습니다. 아직 조사 할 시간이 없었지만 autoload. 그런 ...

configure.php
여기서는 기본적으로 일부 php.ini 속성을 재정의 하고 사이트에 대한 다른 전역 구성을 수행합니다.

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();
//

최근에 OOP를 배우고 mysql 대신 PDO를 사용하기 시작한 이후로 엄청난 개선의 여지가 있다고 생각합니다.
그래서 저는 몇 개의 초보자 튜토리얼을 따라했고 다른 것들을 시도했습니다 ...

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

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 extends my connect_pdo-class, I have a public function called getAreaName which handles the request to my database.
Pretty neat I think.

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

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

---

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 :

$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 tutorial about use of PDO (there are a log of bad tutorial online).

---

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

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

• Access $dbh using global $dbh inside 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) { /* ... */ }
  }
  

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

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.

---

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

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.

---

$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();
}

or read on http://ask.hcig.co.za/?p=179

참고URL : https://stackoverflow.com/questions/11369360/how-to-properly-set-up-a-pdo-connection