Page 1 of 1

Introduction to PDO Rate Topic: ***** 6 Votes

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,414
  • Joined: 08-June 10

Post icon  Posted 08 February 2011 - 05:30 AM

*
POPULAR

Introduction to PDO

what you need
  • a working installation of PHP 5
  • a basic understanding of OOP
  • Drinks & Cookies
  • a bookmark of the PHP Manual and Wikipedia


recommended reading


Installation
As of PHP 5.1.0 PDO is enabled by default, so it should be already set up. More installation details can be found here.

What is PDO?

PDO is the abbrevation of PHP Data Objects. It is a lightweight system (to be precise, it is rather an interface) to access data from databases with PHP. Lets see what the PHP developers say about PDO:

The PHP Manual said:

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.


How to use PDO?

The PDO system consists of 3 classes: PDO, PDOStatement & PDOException.
The PDO class is responsible for maintaining the database connection doing some conection related stuff (like Transactions) and create the instances of the PDOStatement class.
The PDOStatement class is what you work with most of the time. It is handling the SQL queries and the fetching of the results.
The PDOException class is what you need for error handling.

The PDO class

The PDO class is used to create the database connection. For that to work you must pass 3 types of information:

  • where to find the database
  • a valid login name
  • the correct password for the login name

The first piece of information is passed in a specially formatted string, the so-called Data Source Name (DSN). This string slightly differs for each database, but common required informations are: database type (e.g. MySQL), database host location (e.g. localhost), database port and database name.
You will find the DSN for your database described in the PDO Drivers’ section.

For a better understanding, I’ll give some examples for what you need to connect to a MySQL database:

- making a simple connection, passing just the least necessary information.
$dsn = "mysql:host=localhost;dbname=my_database";
$pdo = new PDO($dsn, $login, $password);

- making an advanced connection, passing options like error mode and encoding
$dsn = "mysql:host=localhost;dbname=my_database";
$opt = array(
	// any occurring errors wil be thrown as PDOException
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	// an SQL command to execute when connecting
	PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);
$pdo = new PDO($dsn, $login, $password, $opt);

I’ll explain the use of the error mode in the PDOException section.

From the PDO class, there is one more method of interest: PDO->prepare(). This method is used when you want to create a PDOStatement instance to query the database.

The PDO class – additional options

The above mentioned options are the ones I think will be used most often, but there are other neat options, too.

  • PDO::ATTR_CASE, return the field names in the specified case
  • PDO::ATTR_ORACLE_NULLS (not only for Oracle), conversion between NULL and empty strings
  • PDO::ATTR_STRINGIFY_FETCHES, convert numeric results into strings
  • PDO::ATTR_TIMEOUT, specifies the timeout

A full description is available in the Manual.

The PDOStatement class – part I: create a query

As previously mentioned, PDOStatement deals with SQL queries. A PDOStatement instance is returned from calling PDO->prepare() with the appropriate SQL query template.

// using the PDO instantiation from above
$ps = $pdo->prepare("SELECT `name`, `color` FROM `fruits` WHERE `type` = ?");

You may notice a thing or two in this SQL string: First of all, there is a ? where you would normally place the value (e.g. $type or $_GET['type']. This is due to the type of query we’re doing here, which is called "Prepared Statement". You need it to counter SQL Injection attacks. The security of Prepared Statements is based on the fact that it separates the data from the SQL instruction, thus any SQL code that may be passed in the data is treated as data and not as SQL instruction and can therefore do no harm in the database.
To differentiate between data and SQL instruction, a placeholder is put where the data should be pasted later. Of these placeholders, there are two types available in PDO: the question mark placeholder (?) and the named placeholder (a colon followed by a name token). Named placeholders are useful when you need to place one bit of data in several places (this may occur in JOINed tables).

WARNING: you must never place data in Prepared Statements, otherwise you are susceptible to SQL Injection attacks!

Before you place question marks all over your SQL query, bear in mind that table names and field names (i.e. anything that can be quoted by the backtick) are not data and can not be replaced by placeholders.

For a better readability, please refrain from using the wildcard character (*). If you explicitly name the fields you want to retrieve, this relieves you from getting data you don’t need and your query gets more self-explaining. You’ll benefit from that when you have to examine SQL errors. A typo is easier detectable, a result class error can be spotted faster, etc.

The PDOStatement class – part II: pass data

You learned in the previous section that Prepared Statements are immune against SQL Injection attacks. From this it follows that we do not have to escape data anymore – good bye to mysql_real_escape_string() & Co.

Now that we know that, how do we actually pass the data to the Prepared Statement? There is the regular and the lazy method. First I’ll show the lazy method:
// using the PDO instantiation from above
$ps = $pdo->prepare("SELECT `name`, `color` FROM `fruits` WHERE `type` = ?");
$ps->execute(array("vegetable"));

In the lazy method, you pass the data into the PDOStatement->execute() method. For that to work you have to follow some constraints:
  • execute() always expects an array, even if there is only a single value
  • the data type of the value must only be String1

In this example you got to know one of PDOStatement’s most important methods: execute(). You will need that to actually send data to the database. Or in other words, without calling execute() you won’t get results (but an error).

Another example, demonstrating a user login query with named placeholders.
// note the SQL structure. All we want to know is, whether the
// user exists in the database and if (s)he submitted the correct 
// password. For that we only need the number of matching rows
// which we can get by the SQL function COUNT(). There is no need
// to do that on the PHP side.
// The Heredoc Syntax is for demonstrating the SQL structure
$sql = <<<SQL
SELECT 
	COUNT(*) 
FROM 
	`login_table` 
WHERE 
	`username` = :login 
	AND 
	`pwhash` = :pass
SQL;
// create the Prepared Statement
$ps = $pdo->prepare($sql);

// note that there is no input sanitising necessary
$params = array(
	'login' => $_GET['username'],
	'pass'  => hash('ripemd160', $_GET['password'])
);

// both input data are strings, thus the simplified method will do
$ps->execute($params);

// we only have 1 value, so we fetch directly
$loggedIn = (bool) $ps->fetchColumn();


In the regular method passing data to the Prepared Statement we use another method of the PDOStatement class, PDOStatement->bindValue() or PDOStatement->bindParam().

The above example with data binding
$sql = <<<SQL
SELECT 
	COUNT(*) 
FROM 
	`login_table` 
WHERE 
	`username` = :login 
	AND 
	`pwhash` = :pass
SQL;
// create the Prepared Statement
$ps = $pdo->prepare($sql);

// again, no input sanitising necessary
$ps->bindValue("login", $_GET['username'], PDO::PARAM_STR);
$ps->bindValue("pass", hash('ripemd160', $_GET['password']), PDO::PARAM_STR);

// execute the statement
$ps->execute();

// we only have 1 value, so we fetch directly
$loggedIn = (bool) $ps->fetchColumn();

Which one to choose depends on the situation. As a rule of thumb you should use bindValue() when you have to pass data only once and bindParam() if you have to pass multiple data (e.g. all values of an array). An example is the database setup in this PDO tutorial (very first code block, lines #28-29 & #43-46).

The noteable difference between bindValue() and bindParam() is the second parameter. In bindValue() it has to be the value to be passed (you have to know the value at this point) while bindParam() expects a variable (it doesn’t matter if the variable is assigned something yet or even if it has been used before).

Finally, the third parameter defines the data type of the value to be passed. The available values are:
  • PDO::PARAM_BOOL (for booleans)
  • PDO::PARAM_NULL (for SQL NULL)
  • PDO::PARAM_INT (for SQL INTEGER)
  • PDO::PARAM_STR (for string types)
  • PDO::PARAM_LOB (for Large OBject types)
  • PDO::PARAM_STMT (for a recordset type, currently not supported)
  • PDO::PARAM_INPUT_OUTPUT (for an INOUT parameter of a Stored Procedure)

Most of the time you can use one of the first four types. The the LOB type is mostly used when transferring uploaded data. A detailed explanation would exceed this tutorial, though.

The PDOStatement class – part III: fetch data

Finally we reach the topic that PDO is about—fetching data. For this we need to call PDOStatement->fetch() and we get returned the current row according to our chosen fetch option. When it comes to fetch options, PDO really stands out. Unfortunately this is also the point where you as programmer have to do some thinking beforehand. It would be a waste to use PDO the way you have used mysql_* functions until now. Simply using it for returning an array of fields is like having a truck and pulling the trailer yourself.

This is the list of fetch options available in PDO:
  • PDO::FETCH_LAZY
  • PDO::FETCH_ASSOC
  • PDO::FETCH_NAMED
  • PDO::FETCH_NUM
  • PDO::FETCH_BOTH
  • PDO::FETCH_OBJ
  • PDO::FETCH_BOUND
  • PDO::FETCH_COLUMN
  • PDO::FETCH_CLASS
  • PDO::FETCH_INTO
  • PDO::FETCH_FUNC
  • PDO::FETCH_GROUP
  • PDO::FETCH_UNIQUE
  • PDO::FETCH_KEY_PAIR
  • PDO::FETCH_CLASSTYPE
  • PDO::FETCH_SERIALIZE
  • PDO::FETCH_PROPS_LATE

You did count right, there are 17! (in words: seventeen) fetch options in PDO. And that’s not all since some fetch options can (resp. must) be combined. But for the sake of clarity I’ll push in a demonstration example first.
// instantiate PDO as usual …
$ps = $pdo->prepare("SELECT `name` FROM `fruits`");
$ps->execute();
$ps->setFetchMode(PDO::FETCH_COLUMN, 0);

foreach ($ps as $row)
{
	echo $row, PHP_EOL;
}


This very simple code already shows PDO’s key feature: when using foreach(), you do not need (or rather must not) call the fetch() method manually. The fetch options are set by a separate method called setFetchMode().

Some options are additional and can not be used alone. i.e. PDO::FETCH_UNIQUE / PDO::FETCH_GROUP go with PDO::FETCH_COLUMN and PDO::FETCH_CLASSTYPE / PDO::FETCH_PROPS_LATE go with PDO::FETCH_CLASS. You combine two options simply by doing a bitwise OR (|).
$ps->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE);

Two of PDO’s fetch options even have a shortcut call (though that is not available in foreach()):
  • PDOStatement->fetchColumn(): shorthand for PDO::FETCH_COLUMN
  • PDOStatement->fetchObject(): shorthand for PDO::FETCH_OBJ or PDO::FETCH_CLASS

The last method to mention is PDOStatement->fetchAll(), which will return the complete result set as an Array. Bear in mind that you should only call that if you explicitly need an array (e.g. for using array functions). DO NOT USE IT TO LOOP THROUGH THE RESULTS AFTERWARDS, a PDOStatement can do that better.

Primitive fetch options
  • PDO::FETCH_ASSOC
    Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set.
    If the result set contains multiple columns with the same name, PDO::FETCH_ASSOC returns only a single value per column name.
  • PDO::FETCH_NAMED
    Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set.
    If the result set contains multiple columns with the same name, PDO::FETCH_NAMED returns an array of values per column name.
  • PDO::FETCH_NUM
    Specifies that the fetch method shall return each row as an array indexed by column number as returned in the corresponding result set, starting at column 0.
  • PDO::FETCH_BOTH
    Specifies that the fetch method shall return each row as an array indexed by both column name and number as returned in the corresponding result set, starting at column 0.


Extended fetch options
  • PDO::FETCH_COLUMN
    Specifies that the fetch method shall return only a single requested column from the next row in the result set.
  • PDO::FETCH_BOUND
    Specifies that the fetch method shall return TRUE and assign the values of the columns in the result set to the PHP variables to which they were bound with the PDOStatement->bindParam() or PDOStatement->bindColumn() methods.
  • PDO::FETCH_KEY_PAIR
    Fetch into an array where the 1st column is a key and the 2nd column the value.
    Throws a PDOException if any but 2 columns are requested in the SQL.


"Operational" fetch options
  • PDO::FETCH_CLASS
    Specifies that the fetch method shall return a new instance of the requested class, mapping the columns to named properties in the class.
  • PDO::FETCH_OBJ
    Same as PDO::FETCH_CLASS using "stdClass"
  • PDO::FETCH_FUNC2
    Returns the results of calling the specified function, using each row's columns as parameters in the call.
  • PDO::FETCH_INTO
    Specifies that the fetch method shall update an existing instance of the requested class, mapping the columns to named properties in the class.
  • PDO::FETCH_SERIALIZE
    Same as PDO::FETCH_INTO but object is provided as a serialized string.
  • PDO::FETCH_LAZY
    Specifies that the fetch method shall return each row as an object with variable names that correspond to the column names returned in the result set. PDO::FETCH_LAZY creates the object variable names as they are accessed.


Combined fetch options
  • PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE
    Executes the class’ constructor before the class members are populated from the DB.
  • PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE
    Determine the class name from the value of first column.
  • PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE3
    Fetch only the unique values of a single column from the result set.
  • PDO::FETCH_COLUMN | PDO::FETCH_GROUP3
    Return an associative array grouped by the values of a specified column.

A tutorial on how to use PDO::FETCH_CLASS is given here.

The PDOException class

As demonstrated in the DB Error handling Tutorial, you wrap your whole database code in a try … catch block, so that when an error occurs, all subsequent code that builds upon the successfull execution of the code beforehand will be skipped and therefore does not cause any more errors. You also have the possibility to hide errors from the user.
try
{
	// use the appropriate values …
	$pdo = new PDO($dsn, $login, $password);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$ps = $pdo->prepare("SELECT `name` FROM `fruits` WHERE `type` = ?");
	$ps->bindValue(1, $_GET['type']);
	$ps->execute();
	$ps->setFetchMode(PDO::FETCH_COLUMN, 0);

	$text = "";
	foreach ($ps as $row)
	{
		$text .= $row . "<br>";
	}
	// a function or method would use a return instead
	echo $text;
}
catch (Exception $e)
{
	// apologise
	echo '<p class="error">Oops, we have encountered a problem, but we will deal with it. Promised.</p>';
	// notify admin
	send_error_mail($e->getMessage());
}
// any code that follows here will be executed

In contrast to die() (which instantly kills the whole script), this gives you the opportunity to finish the page in an orderly manner.






1 — the data type as specified in the database. Strings are possible for CHAR, VARCHAR, BINARY, VARBINARY, ENUM, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP (although the date and time types may be passed as numbers as well) and all number types except INT (PDO does not know floats and non-integer numerics). Some SQL data types like BINARY, TEXT and BLOB may also use PDO’s Large Object (LOB) type.
2 – this only seems to work as PDOStatement->fetchAll(PDO::FETCH_FUNC, $fn_name);
3 – only makes sense when using PDOStatement->fetchAll()

This post has been edited by Dormilich: 15 March 2011 - 11:42 PM


Is This A Good Question/Topic? 26
  • +

Replies To: Introduction to PDO

#2 diego_pmc  Icon User is offline

  • D.I.C Addict

Reputation: 81
  • View blog
  • Posts: 565
  • Joined: 13-May 09

Posted 08 February 2011 - 12:32 PM

Thanks for the tutorial. Much appreciated, Dormilich! :)
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is online

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,688
  • Joined: 30-January 09

Posted 18 February 2011 - 06:19 PM

Bookmarked. Thanks for the great article
Was This Post Helpful? 0
  • +
  • -

#4 EnvXOwner  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 357
  • View blog
  • Posts: 2,319
  • Joined: 10-August 09

Posted 15 August 2011 - 11:51 AM

0_0, This was so helpful man. Thanks for being such an expert in PHP! :D
Was This Post Helpful? 0
  • +
  • -

#5 richmund12  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 38
  • Joined: 10-October 09

Posted 06 August 2012 - 12:25 PM

@Dormilich, thanks man. I read through everything and I'm excited to try and explore PDO. :) Helps a lot. thanks again.
Was This Post Helpful? 0
  • +
  • -

#6 kevinloyed  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 4
  • Joined: 07-August 12

Posted 08 August 2012 - 10:12 PM

Thanks for sharing this helpful post.
Was This Post Helpful? 0
  • +
  • -

#7 tim9009  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 01-October 10

Posted 30 September 2012 - 04:42 PM

Thank you!
This is just what I have been looking for all day. No one else could explain to me the workings of the PDO system. Again: thank you.
Was This Post Helpful? 0
  • +
  • -

#8 grizrule  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 88
  • Joined: 29-December 13

Posted 09 February 2014 - 06:25 PM

WOW, thank you. It was very clear. I will be reading this over and over again until I have a good understanding of the basics of PDO.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1