Detailed PHP PDO simple tutorial

Time:2019-9-29

About 80% of Web applications are supported by PHP. Similarly, so is SQL. Before PHP 5.5, we had mysql_commands for accessing MySQL databases, but they were eventually discarded due to lack of security.

The abandonment took place on PHP 5.5 in 2013. I wrote this article in 2018, and the PHP version was 7.2. The abandonment of mysql_brings two main methods of accessing databases: mysqli and PDO libraries.

Although the mysqli library is officially designated, PDO has gained more praise because mysqli can only support MySQL database, while PDO can support 12 different types of database drivers. In addition, PDO has other features that make it a better choice for most developers. You can see some comparisons in the following table:

  PDO MySQLi
Database support 12 drivers Only MySQL
Example OOP Process + OOP
Preprocessing statement (client side) Yes No
1 Named parameter Yes No

Now I think it’s clear why PDO is the preferred choice for most developers. So let’s study it in depth and hope to cover as much as possible what you need to know about PDO in this article.

Connect

The first step is to connect to the database. Since PDO is fully object-oriented, we will use instances of PDO classes.

The first thing we need to do is define the host, database name, user name, password and database character set.


$host = 'localhost';
$db  = 'theitstuff';
$user = 'root';
$pass = 'root';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$conn = new PDO($dsn, $user, $pass);

Later, as you can see in the code above, we created a DSN variable, which is just a variable that holds database information. For some people running MySQL on external servers, you can also adjust the port number by providing a port=$port_number.

Finally, you can create an instance of the PDO class. I use the $conn variable and provide the $dsn, $user, $pass parameters. If you follow these steps, you should now have an object called $conn, which is an instance of the PDO connection class. Now it’s time to go into the database and run some queries.

A simple SQL query

Now let’s run a simple SQL query.


$tis = $conn->query('SELECT name, age FROM students');
while ($row = $tis->fetch())
{
 echo $row['name']."\t";
 echo $row['age'];
 echo "<br>";
}

This is the simplest form of running queries using PDO. We first created a variable called tis (abbreviated as TheITStuff), and then you can see that we used the query function in the created $conn object.

Then we run a while loop and create a $row variable to fetch content from the $tis object, and finally display each row by calling the column name.

It’s simple, isn’t it? Now let’s look at the preprocessing statements.

Preprocessing statement

Pre-processed statements are one of the main reasons people start using PDO, because they provide statements that can prevent SQL injection.

There are two basic ways to use them. You can use location parameters or named parameters.

Position parameter

Let’s look at an example of a query using location parameters.


$tis = $conn->prepare("INSERT INTO STUDENTS(name, age) values(?, ?)");
$tis->bindValue(1,'mike');
$tis->bindValue(2,22);
$tis->execute();

In the example above, we put two question marks and then use the bindValue () function to map the values to the query. These values are bound to the position in the statement question mark.

I can also use variables instead of providing values directly. The same example using the bindParam () function is as follows:


$name='Rishabh'; $age=20;
$tis = $conn->prepare("INSERT INTO STUDENTS(name, age) values(?, ?)");
$tis->bindParam(1,$name);
$tis->bindParam(2,$age);
$tis->execute();

Named parameter

Named parameters are also preprocessed statements that map values / variables to named locations in queries. Because there is no location binding, it is very effective in multiple queries using the same variable.


$name='Rishabh'; $age=20;
$tis = $conn->prepare("INSERT INTO STUDENTS(name, age) values(:name, :age)");
$tis->bindParam(':name', $name);
$tis->bindParam(':age', $age);
$tis->execute();

You can notice that the only change is that I use: name and: age as placeholders and then map variables to them. The colon is used before the parameter, so it’s important for PDO to know that the location is a variable.

You can similarly use bindValue () to map values directly using named parameters.

get data

PDO is very rich in data acquisition. It actually provides many formats to retrieve data from the database.

You can use PDO:: FETCH_ASSOC to get associative arrays, PDO:: FETCH_NUM to get digital arrays, and PDO:: FETCH_OBJ to get object arrays.


$tis = $conn->prepare("SELECT * FROM STUDENTS");
$tis->execute();
$result = $tis->fetchAll(PDO::FETCH_ASSOC);

You can see that I used fetchAll because I wanted all matching records. If you only need one line, you can simply use fetch.

Now that we’ve got the data, it’s time to loop it, which is very simple.


foreach ($result as $lnu){
 echo $lnu['name'];
 echo $lnu['age']."<br>";
}

As you can see, because I requested an associative array, I am accessing members by name.

Although there is no requirement for defining how you want to transfer data, you can actually set the $conn variable to its default value when defining itself.

All you need to do is create an array of $options in which you can put all the default configurations, just pass the array in the $conn variable.


$options = [
 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$conn = new PDO($dsn, $user, $pass, $options);

This is a very short and fast introduction to PDO, and we will soon produce an advanced tutorial. If you encounter any difficulties in understanding any part of this tutorial, please let me know in the comments section where I will answer for you.

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.