Problem
I get queries about connecting to databases from time to time. Most of the time, I don’t do things the way they’re supposed to be done, or I don’t get the answers right. Anyway, I’ve never considered it because my method works for me.
But here’s a wild thought: maybe I’m doing everything wrong, and if that’s the case, I’d love to learn how to properly connect to a MySQL database using PHP and PDO and make it easily accessible.
This is how I’m going about it:
To begin, here is a simplified version of my file structure:
public_html/
* index.php
* initialize/
-- load.initialize.php
-- configure.php
-- sessions.php
index.php I have require(‘initialize/load.initialize.php’); at the very top.
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’m sure there’s a better or more accurate approach to add classes, but I can’t recall what it is. I haven’t had the opportunity to investigate it yet, but I believe it was related to autoload. something along those lines…
configure.php I’m basically simply overriding certain php.ini-properties and doing some other site-wide configuration.
connect.php I’ve put the connection into a class so that it may be extended by other classes…
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();
//
Since I’ve only recently started learning OOP and using PDO instead of mysql, I believe there’s a lot of space for development. So far, I’ve only followed a number of beginner courses and experimented with various techniques…
sessions.php In addition to handling regular sessions, I also initialize some classes into a session, as shown below:
if (!isset($_SESSION['sqlQuery'])){
session_start();
$_SESSION['sqlQuery'] = new sqlQuery();
}
This manner, this class is accessible from anywhere. This might not be a good idea(?) In any case, this strategy allows me to accomplish the following from anywhere:
echo $_SESSION['sqlQuery']->getAreaName('county',9); // outputs: Aust-Agder (the county name with that id in the database)
I have a public function named getAreaName in my sqlQuery-class, which extends my connect pdo-class, that handles the database request. I think it’s very cool.
It works perfectly. So that’s how I’m going about it. Also, whenever I need to get something from my database that isn’t contained within a class, I simply perform something like 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);
Because I saved the connection as a variable in connect pdo.php, all I have to do now is refer to it. It’s effective. I get the outcomes I expected…
But, regardless, I’d appreciate it if you could tell me if I’m completely off the mark here. What I should do instead, places where I could or should improve, and so on…
I’m keen to get knowledge…
Asked by ThomasK
Solution #1
As far as I can see, your goal in this scenario is twofold:
When dealing with PDO connections, I advocate using both anonymous functions and factory patterns. The following is an example of how it could be used:
$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, either in a new file or at the bottom of the same file:
$something = $factory->create('Something');
$foobar = $factory->create('Foobar');
The factory itself should resemble the following:
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 method allows you to have a centralized structure that ensures that connections are only made when they are needed. It would also make unit testing and maintenance much more straightforward.
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.
Please note that this is a very simplistic example. You also might benefit from watching two following videos:
Also, I strongly advise you to study a comprehensive lesson on how to utilize PDO (there are a log of bad tutorial online).
Answered by tereško
Solution #2
I wouldn’t recommend utilizing $_SESSION to access your database connection on a global scale.
You can choose from the following options (in order of worst to best):
The last one comes highly recommended. 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, without a framework, it is a little more advanced and necessitates more “wiring.” If dependency injection is too difficult for you, instead of a slew of global variables, utilise a singleton registry.
Answered by Ian Unruh
Solution #3
On my own, I recently came to a similar conclusion/question. In case anyone is interested, here’s what I did:
<?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, simply change the following line:
$DB = new \Library\PDO(/* normal arguments */);
And, if you’re using it to (LibraryPDO $DB), the type-hinting.
It’s very similar to both the accepted and your answers, but it has a distinct benefit. Consider the following code:
$DB = new \Library\PDO( /* args */ );
$STH = $DB->prepare("SELECT * FROM users WHERE user = ?");
$STH->execute(array(25));
$User = $STH->fetch();
While it appears to be regular PDO (it only alters because of the Library), it doesn’t truly initialize the object until you call the first method, whichever that is. Because the PDO object generation is little expensive, this makes it more optimized. It’s a transparent class, sometimes known as a Ghost, which is a type of Lazy Loading. You can use $DB like any other PDO instance, passing it around and performing the same operations.
Answered by Francisco Presencia
Solution #4
$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();
}
Answered by hi-code
Solution #5
Your setup has a couple fundamental flaws:
Here’s a directory structure I’ve recommended, which is close to what I use for medium-sized projects:
init.php Replaces public_html/initialize. Your PDO connection details
are held here.
classes/ Replaces public_html/classes
vendor/autoload.php Your class autoload script generated using the
industry standard Composer command line tool
composer.json The file where you describe how autoload.php
operates among other things. For example if you
don't use namespaces (maybe you should) it might be:
{"autoload": {"psr-4": { "": "classes/" }}}
public_html/index.php Your landing page
public_html/other.php Some other page
public_html/css/foobar.css ...and so on for all static resources
The init.php file could look like this:
date_default_timezone_set('Etc/UTC');
require 'vendor/autoload.php';
$pdoConnect = function() {
static $pdo = false;
if (!$pdo) {
$pdo = new PDO('mysql:dbname=db;host=localhost', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
}
return $pdo;
};
// similar anonymous functions for session_start(), etc.
The following is an example of index.php:
require '../init.php';
$pdo = $pdoConnect();
// go from there
other.php could be similar, but it’s possible that it doesn’t connect to the database and so doesn’t run $pdoConnect.
You should write the majority of your code inside the classes directory as much as possible. Make index.php, other.php, and other files as short as possible.
Answered by Abhi Beckert
Post is based on https://stackoverflow.com/questions/11369360/how-to-properly-set-up-a-pdo-connection