[PHP] How to use PDO

[IntactDev]

Hello my fellow DevBesters!

Today I will share with you the most useful (imo) interface for accessing databases. Here is what PHP.net says about it.

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a

You must be registered for see links

to access a database server.

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.

PDO ships with PHP 5.1, and is available as a PECL extension for PHP 5.0; PDO requires the new OO features in the core of PHP 5, and so will not run with earlier versions of PHP.

So let's get started

Step 1:
First, you need to define your database credentials into variables.

$config['PDO']['Hostname']    = 'localhost';  # Database hostname
$config['PDO']['Username']    = 'root';  # Database Username
$config['PDO']['Password']    = 'password';  # Database Password
$config['PDO']['Database']     = 'db';  # Database name

$config['PDO']['Data']['Source']['Name'] = 'mysql:dbname=' . $config['PDO']['Database'] . ';host='. $config['PDO']['Hostname'] .';';

Step 2:
Initiate your connect to the database, and check for errors.

try {
    $db = new PDO($config['PDO']['Data']['Source']['Name'], $config['PDO']['Username'], $config['PDO']['Password']);  # Connect to the database
} catch(PDOException $e) {
    die($e->getMessage());  # if there is an error, the script will end and return the error message.
}

Step 3:
Do database functions (like queries!)

$query = $db->prepare('SELECT first_name, last_name, email FROM users ORDER BY first_name ASC');  # Unlike MySQL, with PDO you have to prepare the query first.
$query->execute();  # Then, you simply execute the query.

# Let's say you wanted to fetch all the results, you would do something like this

$result = $query->fetchAll(); # Fetches all results from query.

# Then you could do the following...
echo $result['first_name'];  # Returns result of the 'first_name' column for the FIRST result.

I hope you enjoyed the tutorial. If I made any mistakes (and I don't think I did) just let me know and I'll gladly fix/improve them!

 

[josilol]

Nice! PDO is great and we use it on work, in school etc.
Here's some pointers and things you could add to make the tutorial even better:

Step 1:

• Change the name $config['PDO']['Data']['Source']['Name'] to be $config['PDO']['dsn'] instead. It's shorter and having an array of an array of an array of an array (did I pull that off right?) just to hold a single string value doesn't make that much sense.
• You should tell something about what dsn actually is and how easily you chance between different database systems.

Step 2:

• You should add a list on the most common dsn to the different databases available. You should also probably mention that there is a optional options array that you can pass with some additional configuration, some of these may not be altered later once the PDO object has been created.
• There are three different error modes available on PDO, it could be good to know about them. A short description about the different modes and how you change to use them should be enough. Some PHP versions may have different default error mode!
  
• It's also a good idea to make sure the Exception message is logged instead of just outputting it especially in a production environment.

Step 3:

• Actually you don't have to prepare a statement in PDO, it's good practice but it isn't a requirement. Instead you could use
  You must be registered for see links
  in the code example above since there is no data coming from the user.
• Again in your code example, I think $result['first_name']; would generate some sort of error, as fetchAll() is returning an array with the row as a result or FALSE if there are 0 results to fetch. What you actually were looking for is $result[0]['first_name']; which would output the first_name of the first element in the array (the first row that is).
• You should probably add something about the different FETCH styles that are available.
• Some more examples with INSERT, UPDATE, DELETE would be great!

 

[Nehalem]

Great tut bud. Thanks for making it, and ye, you should add some insert, update and delete guides.

But I've heard that PDO isn't completely secure (even if you're using prepared statements) and that it's really hard to make it 100% secure. And that's the reason I'll stick to MySQLi.

 

[josilol]

Great tut bud. Thanks for making it, and ye, you should add some insert, update and delete guides.

But I've heard that PDO isn't completely secure (even if you're using prepared statements) and that it's really hard to make it 100% secure. And that's the reason I'll stick to MySQLi.

Could you elaborate on this please? I've been using PDO for a while and I guess the only tricky part is when building dynamic SQL queries.

 

[geneocide]

Thank's this helped!

 

[Nehalem]

Could you elaborate on this please? I've been using PDO for a while and I guess the only tricky part is when building dynamic SQL queries.

Hmm, let me give it a try...

I've been looking after a way to make queries, etc. more secure. After a few hours of looking around, I red that you can't always be sure you're doing it right (source: stackoverflow). And I didn't want to get involved in PDO cuz of that.

But I don't know what you are thinking. That's just one of my many opinions.

 

[josilol]

Hmm, let me give it a try...

I've been looking after a way to make queries, etc. more secure. After a few hours of looking around, I red that you can't always be sure you're doing it right (source: stackoverflow). And I didn't want to get involved in PDO cuz of that.

But I don't know what you are thinking. That's just one of my many opinions.

Well you've must misinterpreted something because PDO is as safe as MySQLi. If you make the queries secure that is, by either escaping or using prepared statements.

 

[IntactDev]

@RastaLulz can you pin this? I think people should see this as PDO grows more and more popular.

Sent from my SGH-T679 using Tapatalk

 

[Damon]

I've been using PDO for a few months now and its been great experience for me. But great tutorial for people who'd like to learn how to use it.

 

[Markshall]

You must be registered for see links

This whole playlist is great.