如何正确设置PDO连接 [英] How to properly set up a PDO connection
问题描述
我时常看到有关连接到数据库的问题。
大多数答案不是我的方式，或者我可能只是没有得到正确的答案。无论如何;我从来没有想过，因为我这样做对我有用。
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.
但这里是一个疯狂的想法;也许我这样做错了，如果是这样的话;我真的想知道如何使用PHP和PDO正确连接到MySQL数据库，并使其容易访问。
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 accesable.
这是我怎么做：
Here's how I'm doing it:
首先，这里是我的文件结构 ：
First off, here's my filestructure (stripped down):
public_html /
public_html/
-
index.php
index.php
初始化/
- load.initialize.php
- configure.php
- sessions.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 的东西。
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...
configure.php
这里我基本上只是覆盖了一些 php.ini - 为网站做一些其他全局配置
configure.php
Here I basically just override some php.ini-properties and do some other global configuration for the site
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，并使用PDO而不是mysql。
所以我刚刚跟着几个初学者tutoraials，尝试了不同的东西...
sessions.php
除了处理常规会话，我还将一些类初始化为一个会话，如下所示：
sessions.php
Beside handeling 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 avalible all over the place. This might not be good practice(?)...
Anyway, this is what this approch allows me to do from everywhere:
echo $_SESSION['sqlQuery']->getAreaName('county',9); // outputs: Aust-Agder (the county name with that id in the database)
code> sqlQuery - 类，其中扩展 my connect_pdo - class ，我有一个名为 getAreaName 的公共函数，用于处理对我的数据库的请求。
很漂亮我认为。
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 whitin 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);
Sience我把连接放到一个变量里面* connect_pdo.php *，我只是引用它我很好去。有用。我得到我的预期结果...
Sience I put the connection into a variable inside *connect_pdo.php*, I just have refering 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 instad, areas I could or should change for improvement etc...
我渴望学习...
推荐答案
目标
如我所见，您的目标是双重的：
The goal
As I see it, your aim in this case is twofold:
- 为每个数据库创建和维护单个/可重复使用的连接
- 确保连接已正确设置
我建议使用匿名函数和工厂模式用于处理PDO连接。其使用方式如下：
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!
此外，我强烈建议您阅读关于使用PDO的正确教程（在线有一个错误教程的日志）。
Also, I would strongly recommend reading a proper tutorial about use of PDO (there are a log of bad tutorial online).
这篇关于如何正确设置PDO连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
