Translation

Many PHP developers are accustomed to using the mysql and mysqli extensions to work with databases. But since version 5.1 in PHP there are more convenient way- PHP Data Objects. This class, called PDO for short, provides methods for working with objects and prepared statements that will significantly improve your productivity!

Introduction to PDO

“PDO – PHP Data Objects is a layer that offers universal method working with multiple databases."

It leaves the concern for the syntax features of various DBMSs to the developer, but makes the process of switching between platforms much less painful. Often this only requires changing the database connection string.

This article is written for people who use mysql and mysqli to help them migrate to the more powerful and flexible PDO.

DBMS support

This extension can support any database management system for which a PDO driver exists. At the time of writing, the following drivers are available:

PDO_CUBRID (CUBRID)
PDO_DBLIB (FreeTDS/Microsoft SQL Server/Sybase)
PDO_FIREBIRD (Firebird/Interbase 6)
PDO_IBM (IBM DB2)
PDO_INFORMIX (IBM Informix Dynamic Server)
PDO_MYSQL (MySQL 3.x/4.x/5.x)
PDO_OCI (Oracle Call Interface)
PDO_ODBC (ODBC v3 (IBM DB2, unixODBC and win32 ODBC))
PDO_PGSQL (PostgreSQL)
PDO_SQLITE (SQLite 3 and SQLite 2)
PDO_SQLSRV (Microsoft SQL Server)
PDO_4D (4D)

However, not all of them are on your server. You can see the list of available drivers like this:
print_r(PDO::getAvailableDrivers());

Connection

Methods for connecting to different DBMSs may differ slightly. Below are examples of connecting to the most popular ones. You will notice that the first three have identical syntax, unlike SQLite.
try ( # MS SQL Server and Sybase via PDO_DBLIB $DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass); $DBH = new PDO("sybase:host=$host ;dbname=$dbname", $user, $pass); # MySQL via PDO_MYSQL $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); # SQLite $DBH = new PDO("sqlite:my/database/path/database.db"); catch(PDOException $e) ( echo $e->getMessage(); )
Please pay attention to the try/catch block - it is always worth wrapping all your PDO operations in it and using the exception mechanism (more on this later).

$DBH stands for “database handle” and will be used throughout the article.

You can close any connection by redefining its variable to null.
# closes connection $DBH = null;
More information on the topic of distinctive options of different DBMSs and methods of connecting to them can be found on php.net.

Exceptions and PDO

PDO can throw exceptions on errors, so everything should be in a try/catch block. Immediately after creating a connection, PDO can be put into any of three error modes:
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
But it is worth noting that an error when trying to connect will always throw an exception.

PDO::ERRMODE_SILENT

This is the default mode. You'll likely use roughly the same thing to catch errors in the mysql and mysqli extensions. The next two modes are more suitable for DRY programming.

PDO::ERRMODE_WARNING

This mode will cause a standard Warning and allow the script to continue executing. Convenient for debugging.

PDO::ERRMODE_EXCEPTION

In most situations, this type of script execution control is preferable. It throws an exception, allowing you to cleverly handle errors and hide sensitive information. Like, for example, here:
# connect to the database try ( $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) ; # Damn! I typed DELECT instead of SELECT! $DBH->prepare("DELECT name FROM people")->execute(); ) catch(PDOException $e) ( echo "Houston, we have problems."; file_put_contents("PDOErrors" .txt", $e->getMessage(), FILE_APPEND); )
There is a syntax error in the SQL expression that will throw an exception. We can record the details of the error in a log file and hint to the user in human language that something has happened.

Insert and Update

Inserting new data and updating existing data are some of the most common database operations. In the case of PDO, this process usually consists of two steps. (The next section is all about both UPDATE and INSERT)

A trivial example of inserting new data:
# STH means "Statement Handle" $STH = $DBH->prepare("INSERT INTO folks (first_name) values ("Cathy")"); $STH->execute();
Actually, you can do the same thing with one exec() method, but the two-step method gives all the benefits of prepared statements. They help protect against SQL injections, so it makes sense to use them even for a one-time query.

Prepared Statements

Using prepared statements strengthens protection against SQL injections.

A Prepared statement is a pre-compiled SQL statement that can be executed repeatedly by sending only different sets of data to the server. Additional benefit is the impossibility of carrying out SQL injection through the data used in placeholders.

Below are three examples of prepared statements.
# without placeholders - the door to SQL injections is open! $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)"); # unnamed placeholders $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # named placeholders $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");
The first example is here for comparison only and should be avoided. The difference between nameless and named placeholders is how you pass data to prepared statements.

Unnamed placeholders

# assign variables to each placeholder, with indices from 1 to 3 $STH->bindParam(1, $name); $STH->bindParam(2, $addr); $STH->bindParam(3, $city); # insert one line $name = "Daniel" $addr = "1 Wicked Way"; $city = "Arlington Heights"; $STH->execute(); # insert another line, with different data $name = "Steve" $addr = "5 Circle Drive"; $city = "Schaumburg"; $STH->execute();
There are two steps here. On the first one, we assign variables to all placeholders (lines 2-4). Then we assign values to these variables and execute the query. To send a new set of data, simply change the variable values and run the request again.

If your SQL expression has many parameters, then assigning a variable to each is very inconvenient. In such cases, you can store the data in an array and pass it:
# the set of data we will insert $data = array("Cathy", "9 Dark and Twisty Road", "Cardiff"); $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); $STH->execute($data);
$data will be inserted in place of the first placeholder, $data in place of the second, etc. But be careful: if your indexes are messed up, this won't work.

Named placeholders

# the first argument is the name of the placeholder # it usually starts with a colon # although it works without them $STH->bindParam(":name", $name);
Here you can also pass an array, but it must be associative. The keys should be, as you might guess, the names of the placeholders.
# the data we insert $data = array("name" => "Cathy", "addr" => "9 Dark and Twisty", "city" => "Cardiff"); $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); $STH->execute($data);
One of the conveniences of using named placeholders is the ability to insert objects directly into the database if the property names match the parameter names. For example, you can insert data like this:
# class for a simple object class person ( public $name; public $addr; public $city; function __construct($n,$a,$c) ( $this->name = $n; $this->addr = $a ; $this->city = $c; ) # so on... ) $cathy = new person("Cathy","9 Dark and Twisty","Cardiff"); # and here's the interesting part $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); $STH->execute((array)$cathy);
Converting an object to an array during execute() causes the properties to be treated as array keys.

Data sampling

Data can be retrieved using the ->fetch() method. Before calling it, it is advisable to explicitly indicate in what form you require them. There are several options:

PDO::FETCH_ASSOC: returns an array with column names as keys
PDO::FETCH_BOTH (default): returns an array with indexes both in the form of column names and their serial numbers
PDO::FETCH_BOUND: assigns column values to the corresponding variables specified using the ->bindColumn() method
PDO::FETCH_CLASS: assigns column values to the corresponding properties of the specified class. If there is no property for some column, it will be created
PDO::FETCH_INTO: updates an existing instance of the specified class
PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ
PDO::FETCH_NUM: returns an array with keys as column numbers
PDO::FETCH_OBJ: returns an anonymous object with properties corresponding to the column names

In practice, you will usually need three: FETCH_ASSOC, FETCH_CLASS, and FETCH_OBJ. To specify the data format, use the following syntax:
$STH->setFetchMode(PDO::FETCH_ASSOC);
You can also set it directly when calling the ->fetch() method.

FETCH_ASSOC

This format creates associative array with column names as indexes. It should be familiar to those who use the mysql/mysqli extensions.
# since this is a regular query without placeholders, # you can immediately use the query() method $STH = $DBH->query("SELECT name, addr, city from folks"); # set the fetch mode $STH->setFetchMode(PDO::FETCH_ASSOC); while($row = $STH->fetch()) ( echo $row["name"] . "\n"; echo $row["addr"] . "\n"; echo $row["city"] . "\n" ;
The while() loop will iterate through the entire query result.

FETCH_OBJ

This type of data acquisition creates an instance of the std class for each row.
# create a query $STH = $DBH->query("SELECT name, addr, city from folks"); # select the fetch mode $STH->setFetchMode(PDO::FETCH_OBJ); # print the result while($row = $STH->fetch()) ( echo $row->name . "\n"; echo $row->addr . "\n"; echo $row->city . "\ n"; )

FETCH_CLASS

When using fetch_class, data is written to instances of the specified class. In this case, values are assigned to the properties of the object BEFORE calling the constructor. If properties with names matching column names do not exist, they will be created automatically (with the scope public).

If your data requires mandatory processing immediately after it is received from the database, it can be implemented in the class constructor.

For example, let's take a situation where you need to hide part of a person's residential address.
class secret_person ( public $name; public $addr; public $city; public $other_data; function __construct($other = "") ( $this->addr = preg_replace("//", "x", $this-> addr); $this->other_data = $other;
When creating an object, all lowercase Latin letters must be replaced with x. Let's check:
$STH = $DBH->query("SELECT name, addr, city from folks"); $STH->setFetchMode(PDO::FETCH_CLASS, "secret_person"); while($obj = $STH->fetch()) ( echo $obj->addr; )
If the address in the database looks like '5 Rosebud', then the output will be '5 Rxxxxxx'.

Of course, sometimes you will want the constructor to be called BEFORE assigning values. PDO allows this too.
$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "secret_person");
Now that you have completed the previous example additional option(PDO::FETCH_PROPS_LATE), the address will not be modified, since nothing happens after writing the values.

Finally, if necessary, you can pass arguments to the constructor directly when creating the object:
$STH->setFetchMode(PDO::FETCH_CLASS, "secret_person", array("stuff"));
You can even pass different arguments to each object:
$i = 0; while($rowObj = $STH->fetch(PDO::FETCH_CLASS, "secret_person", array($i))) ( // do something $i++; )

Other Useful Methods

While this article cannot (and does not attempt to) cover every aspect of working with PDO (it is a huge module!), the following few features cannot be left out without mention.
$DBH->lastInsertId();
The ->lastInsertId() method returns the id of the last inserted record. It is worth noting that it is always called on a database object (called $DBH in this article), and not on an object with an expression ($STH).
$DBH->exec("DELETE FROM folks WHERE 1"); $DBH->exec("SET time_zone = "-8:00"");
The ->exec() method is used for operations that do not return any data other than the number of records affected by them.
$safe = $DBH->quote($unsafe);
The ->quote() method places quotes in string data so that it is safe to use them in queries. Useful if you don't use prepared statements.
$rows_affected = $STH->rowCount();
The ->rowCount() method returns the number of records that participated in the operation. Unfortunately, this function did not work with SELECT queries until PHP 5.1.6. If it is not possible to update the PHP version, the number of records can be obtained like this:
$sql = "SELECT COUNT(*) FROM folks"; if ($STH = $DBH->query($sql)) ( # check the number of records if ($STH->fetchColumn() > 0) ( # do a full selection here because the data was found! ) else ( # print a message that no data satisfying the request was found) )

Conclusion

I hope this material will help some of you migrate from the mysql and mysqli extensions.

meta tag keywords example (4)

Target

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

create and maintain single/reusable connection for each database
make sure the connection is configured correctly

Solution

$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); $factory = new StructureFactory($provider);

Then in another file or below 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 you can have a centralized structure that ensures that a connection is only created when needed. It would also make the process easier unit testing and service.

The supplier in this case will be found somewhere during the bootstrap phase. This approach will also give a clear location where you can define the configuration you are using to connect to the DB.

Keep in mind that this extremely simplified example. You may also like to watch the following two videos:

Also, I highly recommend reading a proper tutorial about using PDO (there is a bad tutorial log online).

From time to time I see questions about connecting to a database.
Most of the answers are not how I do it, or I just can't answer correctly. Anyway; I never thought about it because the way I do it works for me.

But here's a crazy thought; Perhaps I'm doing all this wrong, and if so; I would really like to know how to properly connect to the database MySQL data With using PHP and PDO and make it available.

Here's how I do it:

First, here's mine file structure (truncated) :

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

index.php
At the very top I 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 is a better, or more correct way to include classes, but I don't remember what it was. Didn't get time to look into it yet, but I think it was something with autoload . something like that...

configure.php
Here I'm basically just overriding some php.ini-properties and do some other global settings for the site

connect.php
I set the connection to the class so that other classes can expand this...

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() . "
"; 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();

This is where I really believe there is room for massive improvement as I recently started learning OOP and have been using PDO instead of mysql.
So I just followed a few tutorials for beginners and tried out different things...

sessions.php
Besides handling normal sessions, I also initialize some classes in the session like this:

If (!isset($_SESSION["sqlQuery"]))( session_start(); $_SESSION["sqlQuery"] = new sqlQuery(); )

So this class is available everywhere. This may not be a very good practice(?)...
Anyway, this is what this approach allows me to do everywhere:

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

Inside mine class sqlQuery which extends mine Class connect_pdo , I have a public function getAreaName that handles the query in my database.
Pretty neat I think.

Works like a charm
So that's basically how I do it.
Also, whenever I need to retrieve something from my DB from 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);

Because I'm inserting the connection into a variable inside connect_pdo.php, I just reference it and I'm good to go. It works. I'm getting the expected results...

But regardless of this; I would really appreciate it if you guys could tell me if I leave here. Instead I would have to change areas that I could or should change for improvement etc...

I really want to study...

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

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

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 change this line:

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

And a hint type if you use it (\Library\PDO$DB).

This is indeed similar to the accepted answer and yours; however, it has a significant 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 may look like regular PDO (it's modified by that \Library\ only), it doesn't actually initialize the object until you call the first method, whichever it is. This makes it more optimized since creating a PDO object is a bit expensive. This is a transparent class, or what it is called Ghost, a form. You can treat $DB like a regular PDO instance, pass it around, do the same operations, etc.

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

You can do one of several things (okay the worst for the best practitioner):

Accessing $dbh using global $dbh inside your functions and classes

Use a singleton registry and access it globally, for example:

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

Add the database handler to the classes it needs:

Class MyClass ( public function __construct($dbh) ( /* ... */ ) )

However, it is a little more advanced and requires more "wiring" without the frame. Thus, if dependency injection is too complex for you, use a singleton registry instead of a collection of global variables.

Provides methods for preparing expressions and working with objects that can make you more productive!

Introduction to PDO

"PDO - PHP Data Objects is a database access layer that provides unified methods for accessing different databases."

It does not rely on specific database syntax and allows you to easily switch to another database type and platform simply by changing the connection string in most cases.

This lesson is not a description of the process of working with SQL. It is intended for those who use extensions mysql or mysqli to help them move to a more powerful and portable PDO.

Database support

The extension supports any database for which there is a PDO driver. Drivers are currently available for the following database types:

PDO_DBLIB (FreeTDS / Microsoft SQL Server / Sybase)
PDO_FIREBIRD (Firebird/Interbase 6)
PDO_IBM (IBM DB2)
PDO_INFORMIX (IBM Informix Dynamic Server)
PDO_MYSQL (MySQL 3.x/4.x/5.x)
PDO_OCI (Oracle Call Interface)
PDO_ODBC (ODBC v3 (IBM DB2, unixODBC and win32 ODBC))
PDO_PGSQL (PostgreSQL)
PDO_SQLITE (SQLite 3 and SQLite 2)
PDO_4D (4D)

For the system to work, it is enough to install only those drivers that are really needed. You can get a list of drivers available on the system as follows:

Print_r(PDO::getAvailableDrivers());

Connection

Different databases may have slightly different connection methods. Methods for connecting to several popular databases are shown below. You will notice that the first three are identical to each other, and only SQLite has a specific syntax.

try ( # MS SQL Server and Sybase with PDO_DBLIB $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass"); $DBH = new PDO("sybase:host=$host ;dbname=$dbname, $user, $pass"); # MySQL with PDO_MYSQL $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); # SQLite $DBH = new PDO("sqlite:my/database/path/database.db"); catch(PDOException $e) ( echo $e->getMessage(); )

Pay attention to the block try/catch- you should always wrap PDO operations in a block try/catch and use the exception mechanism. Typically only one connection is made, our example shows multiple connections to show the syntax. $DBH contains a database handle and will be used throughout our tutorial.

You can close any connection by setting the handle to null.

# Close the connection $DBH = null;

You can learn more about specific options and connection strings for various databases from the documents on PHP.net.

Exceptions and PDO

PDO can use exceptions to handle errors. This means that all PDO operations must be enclosed in a block try/catch. PDO can throw three levels of errors, the error control level is selected by setting the error control mode attribute for the database descriptor:

$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Regardless of the control level set, a connection error always raises an exception and should therefore always be enclosed in a block try/catch.