如何正确设置 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 accessible.
这是我的做法:
首先，这是我的文件结构(精简):
First off, here's my file structure (stripped down):
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 相关.类似的东西...
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-properties 并为站点做一些其他的全局配置
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 以来，还有很大的改进空间.
所以我刚刚学习了一些初学者教程并尝试了不同的东西......
sessions.php
除了处理常规会话，我还将一些类初始化为这样的会话:
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)
在我的 sqlQuery-class 中，它extends 我的 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 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);
由于我将连接放入 connect_pdo.php 中的一个变量中，所以我只是参考了它，我很高兴.有用.我得到了预期的结果...
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...
我渴望学习...
推荐答案
目标
在我看来，您在这种情况下的目标是双重的:
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:
另外，我强烈建议阅读一个合适的教程 关于 PDO 的使用(网上有不良教程的日志).
Also, I would strongly recommend reading a proper tutorial about use of PDO (there are a log of bad tutorial online).
这篇关于如何正确设置 PDO 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
