New Website

I've made a new website, as lynx.io is dead. You can find it here: http://macr.ae/—it's similar in format to lynx.io, but has better articles, and they're all written by me.

JavaScript disabled

While it will still mostly work, a lot of this site's functionality relies on JavaScript - please enable it for the best experience.

PHP Data Objects (PDO) - The Basics

This is the first half of a two-part tutorial. In this part, I will explain how to connect to, select rows from, and insert data into a database; in the next part, I will cover some more advanced stuff.


PHP Data Objects (PDO) is an extension for PHP that provides the developer with yet another way to access databases using SQL in PHP. It provides what is called a "data-access abstraction layer", which means that you use the same functions to access the database regardless of which database system (MySQL, MSSQL, Oracle, etc.) you're using. You cannot perform any database functions using the PDO extension by itself; you have to use a database-specific PDO driver to access a database server (although it's not as complicated as it sounds – everything comes installed already). PDO ships by default with PHP 5.1, and is available as a PECL extension for PHP 5.0. PDO requires the Object Orientated features in the core of PHP 5, and so will not run with any earlier versions of PHP.

So why should you use PDO over the alternatives, such as mysql_* or MySQLi? The mysql_ library was meant only for MySQL versions earlier than 4.1, which was released in 2004. It’s 2011 now, meaning that mysql_* has been obsolete for 7 years! It is also slow, insecure, and leads to ugly code, as it isn’t object-orientated. You also can't use any of the more recent MySQL functions, either.

Once we have accepted that the mysql_* libraries are a bad choice, we have to choose between PDO or MySQLi. They're both good choices, but there are a couple differences. MySQLi only supports MySQL, so if you decide that you want to use a different database system, you cannot easily change. This, however, does add a bit of overhead to PDO, and the MySQLi library is marginally faster. PDO is also more object-orientated than MySQLi, which I prefer.

Connecting to the database

Use the following code to connect to the database:

<?php

// Database connection info
$host = 'localhost';
$port = 3306;
$database = 'yourdatabase';
$username = 'yourusername';
$password = 'yourpassword';

// Construct the DSN
$dsn = "mysql:host=$host;port=$port;dbname=$database";

// Create the connection
$db = new PDO($dsn, $username, $password);

Let's break that down a bit.

For the connection information, you can just use the information that you would use for mysql_* or MySQLi. In the next section we construct the DSN, or "Data Source Name". It’s basically just a string that tells the server what type of database we're connecting to, and how to connect to it. Finally, the last line creates the connection.

Querying the database

Let's say that you have a table called 'posts' that you want to print all the posts from. The table structure:

CREATE TABLE posts (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    post text NOT NULL,
    PRIMARY KEY (id)
)

The code used to print the posts is very simple:

<?php

include('db.php');

$statement = $db->query('SELECT * FROM posts');

while ($result = $statement->fetchObject()) {
        echo $result->post . PHP_EOL;
}

Pretty simple, right? It queries the database using standard SQL, then fetches an object with the values. You can then do what you want with it, as it is a standard class with the values assigned to variables in the class. You could use $statement->fetchArray() to get an array instead.

Inserting data using prepared statements

Prepared statements is a great feature of PDO which helps secure your code against SQL injection. It works like this:

<?php

$statement = $db->prepare('INSERT INTO posts (user_id, post) VALUES (?, ?)');
$statement->execute(array($user['id'], $post));

It's not as complicated as it looks - first, you have the SQL with question marks instead of the values, then you have the code that executes it with the values in an array as an argument. It's the same as doing this (almost):

$db->query('INSERT INTO posts (user_id, post)
    VALUES (' . $user['id'] . ', ' . $post . ')');

The advantage of using prepared statements over traditional SQL queries is that input given to prepared statements does not need to be escaped, and so practically nullifies the risk of SQL injection. It's also easier to maintain.


I hope you found this tutorial useful, I will be writing part two soon.

About Callum Macrae:

Callum Macrae is the founder of lynx.io and a JavaScript developer from the United Kingdom. He is currently writing his first book, to be published by O'Reilly Media.

You can view more articles by this author here.

Tags: php database mysql pdo sql

Comments

Ratus says:

I think you meant $port, not $post, in your connection snippet.

Callum Macrae says:

Yes, I did. Thanks for pointing that out, I've corrected it.

~Callum

caraga says:

excellent for beginners like me

Livewire90210 says:

Absolutely priceless; worked like a charm, although I think I told you this on the IRC chan we're both on :)

Thanks!

Sok says:

Thank you! I'm beginner, poor at PDO. Very good and useful code for me.

oweceej says:

Thank you for the post.

I am having problems with the insert statement.It says the variables defined in the array are not defined.

I then tried it without using the array ie

$db->query('INSERT INTO posts (user_id, post) VALUES(' .$user['id'] .',' .$post . ')');

and I get the same error. Do you have any answer to what I'm doing wrong please?

Thank you.

Callum Macrae says:

Try this:

$statement = $db-&gt;prepare('INSERT INTO posts (user_id, post) VALUES (?, ?)');
$statement-&gt;execute(array(4, 'This is a test post&quot;));

The problem was that the variables were undefined, as the code was just trying to demonstrate the usages of PDO.

oweceej says:

Thanks Callum I'm grateful to you for this.

says:

Add comment

 

You can use markdown in comments (press "m" for a cheatsheet).

Enable JavaScript to post a comment

Markdown Cheat Sheet

# This is an <h1> tag
## This an <h2> tag
###### This is an <h6> tag

Inline markup: _this text is italic_, **this is bold**, and `code()`.

[Link text](link URL "Optional title")
[Google](http://google.com/ "Google!")

![Alt text](image URL)

![This is a fish](images/fish.jpg)

1. Ordered list item 1
2. Ordered list item 2

* Unordered list item 1
* Unordered list item 2
* Item 2a
* Item 2b

And some code:

// Code is indented by one tab
echo 'Hello world!';

Horizontal rules are done using four or more hyphens:

----

> This is a blockquote

This is an <h1> tag

This an <h2> tag

This is an <h6> tag

Inline markup: this text is italic, this is bold, and code().

Link text Google

This is a fish

  1. Ordered list item 1
  2. Ordered list item 2
  • Unordered list item 1
  • Unordered list item 2
    • Item 2a
    • Item 2b

And some code:

// Code is indented by one tab
echo 'Hello world!';

Horizontal rules are done using four or more hyphens:


This is a blockquote

Toggle MarkDown / HTML (t), full reference or close this