Are There Good Tutorials on How to Use Pdo

Are there good tutorials on how to use PDO?

MySQL.com: Using MySQL with PDO

PHP.net: PHP Data Objects (PDO)

Using PDO in PHP

Step 1:
Setting up a new PDO connection

This is not nearly as hard as it is sometimes made out to be. To begin with, you can hunt down those mysql_connect/mysql_select_db and replace them with this code:

//Obviously, replace these with your own values
$host = 'host_name';
$dbname = 'database_name';
$user = 'user_name';
$pass = 'user_pass';
try
{
    $DB = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); 
}
catch(PDOException $e)
{  
    echo $e->getMessage();  
}

This will create your PDO object which has all the same functionality of the mysql_* calls and then some.

Step 2:
Submitting a query

once you have your PDO object, you can begin using it to query your database. We'll look at a basic select query first, since the techniques we'll use are similar in most query types.
Now, you can query directly, but that takes away some of the power of PDO. Instead, we can use prepared statements. By doing so, PDO will work for us to prevent injection or even accidental query breakage. Here's an example:

$query = "SELECT * FROM table_name WHERE col1=':value1' AND col2=':value2'";
$statement = $DB->prepare($query);
$statement->execute(array(':value1' => 1, ':value2' => 2));

At this point, we've queried the database, and have a statement object with the results in it. The bonus here is that, in place of 1 or 2 in the execute statement, we could use a user-generated value, without even checking for SQL injection attempts, because PDO catches them and fixes them automatically. (Though, granted, you should still check that they exist before using user-generated values.)

Step 3:
Retrieving the results

Now, we need to get the data we were searching for, so we can use it. With PDO it's quite simple, all you need is to call the fetch command, just like you would have used that mysql_fetch_array() command before. You'll also want to include it in a while loop to retrieve all the results, since it acts almost identically to mysql_fetch_array().

//You can use several options in fetch, to determine what kind of results you get.
//PDO::FETCH_ASSOC -> gives you column names as the array indices
//PDO::FETCH_NUM   -> Gives you the column number as the indices
// By default, it uses PDO::FETCH_BOTH which does both.
while($row = $statement->fetch(PDO::FETCH_ASSOC))
{
     echo "Col1: " . $row['col1'] . "<br />";
     echo "Col2: " . $row['col2'] . "<br />";
     echo "Col3: " . $row['col3'] . "<br />";
     echo "Col4: " . $row['col4'];
}

Obviously, this is a pretty simple layout, but you can see how it works, and can modify it for your needs. This does exactly the same thing as your current mysql_* code does, except it does it in a simpler more secure manner.

Step 4:
The possibilities

From here, you can see how to replace your basic mysql_* functions. You can replace all other mysql functions with PDO calls as well, a few examples are:

mysql_num_rows() == $statement->rowCount() (used after you've executed your query)
mysql_real_escape_string() == You don't even need this anymore!

mysql_insert_id() == $statement->lastinsertid()

The definitive guide to PHP's PDO usage can be found here:

http://us3.php.net/manual/en/book.pdo.php

And here is a question that looks at the strengths and weaknesses of mysqli vs PDO:

mysqli or PDO - what are the pros and cons?

Why use PDO over MySQL in PHP?

The mysql_* functions are no longer maintained and community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi.

If you cannot decide, this article should help you choose. Though, you should know, that PDO is able to work with different kinds of RDBMS, while MySQLi is made for a specific one.

In case you decide to go with PDO, it's recommended you follow this tutorial.

What's best to learn MySQLi or PDO?

Possible duplicate! But heres my quick response.

As of the recent PHP update, 7.0, the old MySQL function was removed from PHP. This does mean that most people will need to migrate to the newest two functions: MySQLi and PDO. I will give points on both functions and will also give my personal thoughts at the end.

MySQLi

MySQLi is essentially an improvement of the now deprecated mysql_* function of PHP; however, this new function now supports prepared statements.

PDO

PHP Data Objects, also known as PDO, is a general database abstraction layer which supports MySQL as well as many other databases. It supports prepared statements as well.

What are prepared statements?

It is considered good programming practice to use Prepared Statements as they are very resilient against SQL injections/attacks. These SQL injection vulnerabilities occur due to the user input directly being made a part of the SQL query without properly sanitising it.

Personally, I make use of PDO due to it’s nature. But any of the two functions stated above are preferred as they support prepared statements.

How to properly set up a PDO connection

The goal

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

• create and maintain a single/reusable connection per database
• make sure that the connection has been set up properly

Solution

I would recommend to use both anonymous function and factory pattern for dealing with PDO connection. The use of it would looks like this :

$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 in a different file or lower 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 would let you have a centralized structure, which makes sure that connection is created only when required. It also would make the process of unit-testing and maintenance much easier.

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.

Keep in mind that this is an extremely simplified example. You also might benefit from watching two following videos:

• Global State and Singletons
• Don't Look For Things!

Also, I would strongly recommend reading a proper tutorial about use of PDO (there are a log of bad tutorial online).

PHP PDO and MySQLi

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.

mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.

PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

---

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

PDO with CodeIgniter Framework

Here you have some of tutorials for PDO + CodeIgniter

CodeIgniter with PDO

Using PDO in CodeIgniter

Using PDO in CodeIgniter

EllisLab Forum: How to implement PDO in CodeIgniter

Why you Should be using PHP’s PDO for Database Access

Using PDO statements instead of mysql_query

For the exactly such a case I wrote my PDO wrapper.

All you need is to edit configuration constants and include this file in some bootstrap file. That's all. And immediately it will let you to use PDO as easy as mysql_* used to be (as long as you have this file included), yet with full power and safety of prepared statements. Or even simpler than old mysql ext, as most operations will be written in one-two lines.

There are still two main differences from old mysql ext:

• instead of calling mysql_query you have to call DB::prepare()
• instead of adding variables in the query directly you have to pass them in execute() and substitute them in the query with question marks.

Here are some usage examples

// with one variable
$sql  = "SELECT * FROM users WHERE name=?";
$user = DB::prepare($sql)->execute([$_POST['name']])->fetch();

// with two variables
$sql  = "SELECT * FROM users WHERE age > ? and skill = ?";
$user = DB::prepare($sql)->execute([$age, $skill])->fetch();

// without variables and getting more than one rows
$sql  = "SELECT * FROM users ORDER BY id DESC";
$user = DB::prepare($sql)->execute()->fetchAll();

//insert with getting insert id
$sql  = "INSERT INTO users VALUES (NULL,?,?,?)";
$user = DB::prepare($sql)->execute([$name,$pass,$email])->fetch();
$id   = DB::lastInsertId();

if you don't know how to use PDO at all, here is a tag wiki with basic usage examples.

In short, you have to run your queries in three steps:

• prepare
• execute
• fetch

using my wrapper they all can be chained. See an example

And of course you have to follow the main rule of creating SQL statements: every variable should go into query via placeholder only

---

Related Topics