Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Introduction to PHP PDO

See in this article some php functions to connect to the database using the PDO library.

PDO (PHP Data Objects) is an extension that provides a standardized interface for working with databases, whose purpose is to abstract the connection and interactions with databases, i.e. independent of the database that is being used to run methods will be same, but that does not mean that your system will be portable across multiple databases, however that the use of PDO facilitate portability, this interface just means you will communicate with any database through a certain set of methods and classes.

You can not run interaction functions with the database using only the PDO extension, you must use a specific PDO driver to access a given database. Each database can provide a driver for PDO, but not all features are supported in all databases, for example, in MySQL, MyISAM tables do not support transactions, preventing the operation of the methods PDO::beginTransaction(), PDO::commit() and PDO::rollBack().

PDO is not a database abstraction and do not rewrite SQL.

Advantages of using PDO

  • Connection abstraction and interaction with database
  • Safety
  • Supports multiple drivers

Even today many people consider the use of PDO an option, considering the feasibility of a project to switch dabases, using the PDO only if this possibility exists. The great danger in this superficial analysis is that not always the beginning of a project we have this view and if you have to change then on top of that we particularly recommend the use of this resource, especially if the project needs to work with more than one type of database.

Check out all the drivers supported by PDO.

Manipulating connections

Every connection to the database is accomplished by creating an instance of the PDO class, that is, independent of the used driver will always instantiate the PDO class.

The constructor of the PDO class receives database information as a required parameter, known as dsn (Data Source Name), and also the optional parameters username, password and driver_options:

__construct ( string $dsn [, string $username [, string $password
    [, array $driver_options ]]] )

In the example below, we made a connection with MySQL database:

$conn = new PDO(

    'mysql:host=localhost;dbname=example-pdo', 'julio', '123456');

We can also configure our connection via driver_options parameter:

$conn = new PDO(

    'mysql:host=localhost;dbname=example-pdo', 'julio', '123456',
    array(
        PDO::ATTR_PERSISTENT => true
    ));

In the example above, we have configured our connection as persistent. A persistent connection can not be closed at the end of the script, otherwise it should be stored in cache and be reused when another script asks for a connection using the same credentials.

After opening a connection we can interact with the database using three methods of PDO class:

MethodReturnGoal
execintUsed to insert, update e delete.
queryPDOStatementUsed for tabular results, select command.
preparePDOStatementCreate a prepared statement, used for variable data.

Normally, to close a connection you only need to destroy the object, as well as its references. For that, we assign value NULL to the variable that contains the same object. If this won't be done, PHP will automatically close the connection when the script finishes, in case it's not a persistent connection.

Prepared Statements

The prepared statements offer two great benefits:

  • The query only needs to be prepared once, but can be executed a lot of times;
  • The parameters don't need to be scaped, since the driver takes care of it automatically.

These benefits mean two things: agility and safety. Let's check one example of prepared statement creation:

<?php

$stmt = $conn->prepare(
    'INSERT INTO posts (title, content) VALUES (:title, :content)'
);?>

Once the prepared statement is created, we need to inform the values to make the query. Let's have a look at all the methods that can be used:

  • bindValue()
  • bindParam()

<?php

$stmt = $conn->prepare(
    'INSERT INTO posts (title, content) VALUES (:title, :content)'
);
 
$title = 'Post title';
$content = 'Post content';
 
$stmt->bindValue(':title', $title);
$stmt->bindValue(':content', $content);?>

After informing the necessary data to the prepared statement we need to execute the execute() method to realize the query in the database.

To get the results from a select command we have some alternatives:

MethodGoal
fetch()Return the next result line.
fetchAll()Return an array with all the results.
fetchObject()Return the next line of the result as an object.
fetchColumn()Return a column of the next result line.

<?php

$stmt = $conn->prepare("SELECT * FROM posts");
while($row = $stmt->fetch()) {
    print_r($row);
}?>

An interesting resource when we're trying to get the values is the method bindColumn(), which has the responsability to associate the value of a prepared statement result column to a variable.

Difference between bindParam() and bindValue()

The big difference between these methods is that the bindParam() takes the parameter value by reference, which is really setted at the time the execute() method of the statment prepared is called, which can cause problems in some cases, but also can facilitate other, so give preference to bindValue() for basic cases.

Transactions

A transaction is a set of procedures performed in the database as a single operation. In practice, we indicate the beginning of a transaction using the start transaction or begin command in MySQL, then perform some tasks, entry, modification or registration removal(s). At the end of these procedures, if everything goes well, we report through the command commit that changes can be applied in fact on the bench, but if there is something wrong with some of the procedures we can use the rollback command, ensuring that all procedures performed from the beginning of the transaction are undone.

The integrity of a transaction depends on four properties referred to as ACID:

  1. Atomicity: A transaction must be an atomic unit of work; or all of their data changes are executed or none are executed.
  2. Consistency: Data integrity rules are provided, ie, transactions can not break the databaserules.
  3. Isolation: The result of a transaction performed concurrently to the other must be the same as the implementation in isolation. Outdoor operations to a given transaction will never see this transaction in intermediate states.
  4. Durability: Once a transaction has been completed, its effects are permanently in the system.

In the PDO we used three methods to work with transactions, beginTransaction () to initiate a transaction, commit () so that the tasks performed are maintained and rollback () to discard if a problem occurs.

Error handling in the PDO

The PDO offers 3 alternatives for error handling:

  • PDO::ERRMODE_SILENT: This is the default type used by PDO. Basically the PDO internally sets the code of a particular error, and may be redeemed by the methods PDO::errorCode() and PDO::errorInfo().
  • PDO::ERRMODE_WARNING: In addition to storing the error code, this type of error handling will send an E_WARNING message, which is widely used for debugging and/or testing of the application.
  • PDO::ERRMODE_EXCEPTION: In addition to storing the error code, this type of error handling will throw an exception PDOException, this alternative is recommended, especially for letting the code cleaner and more readable.

<?php

$dsn = 'mysql:host=localhost;dbname=example-pdo';
$user = 'julio';
$password = '123456';
try {
    $conn = new PDO($dsn, $user, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo $e->getMessage();
}?>

Conclusion

Good luck to you talking a little about PDO, a native PHP driver that has a great power of abstraction of the database layer. Soon I will be writing an article developing a data entry using the RFQ with an Object Oriented design.



Julio is a System analyst and enthusiast of Information Technology. He is currently a developer at iFactory Solutions company, working in the development of strategic systems, is also a JAVA instructor. He has knowledge and experi...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login