• PDO for beginner (part 1)
  • Creating A Database
  • Creating A Connection Class
  • Performing Database Queries
  • The End

#PDO for beginner (part 1)

Since the deprecation of mysql_ function in PHP, developers are advised to use mysqli_ or PDO in order to connect to databases. The main feature of PDO is that it can be used with every database provider. In this tutorial I will be using PDO to connect with MySQL. My emphasis would be on the object oriented approach.

Some users, particularly beginners, find PDO difficult to use because of its syntax hence the need for this two-part tutorial. In the first part , I will connect with the database using PDO and perform CRUD operations, paying special attention to SQL injection threats. In the second part, I will discuss how prepaid statements work with PDO.

Let’s get started with the first part.

#Creating A Database

Let’s create a database and a table for CRUD operation using PDO. Name your database pdotutorial and create a table user with the following schema:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `email` varchar(100) NOT NULL
)

#Creating A Connection Class

First, I will create a separate class for PDO connection for the database. This call will be called everytime the code needs to connect to the database and perform operations. Create a new class Connection. Add the following code to the file and save it withe the name connection.php.

class Connection
{
 
    protected $db =null;
 
 
    public function Open()
    {
        try {
        $dsn      = "mysql:dbname=pdotutorial; host=localhost";
        $user     = <Your Database User>;
        $password = <Your Database Password>;
 
        $options  = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE                      => PDO::FETCH_ASSOC,
        );
 
 
 
            $this->db = new PDO($dsn, $user, $password, $options);
 
            return $this->db;
        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
        }
    }
 
    public function Close()
    {
        $this->db = null;
        return true;
    }
}

Let’s understand the above code step by step.

First, I saved the database credentials in variables. Next, the important variable $options:

$options  = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);

In above line, I set the error mode and data fetch mode on runtime so that I do not have to worry about defining it at every SQL query. Near the end, I created the connection and return it.

Let’s test the class. Create a new file in the same directory and name it index.php. Now paste the following code in it and run it.

include 'connection.php';
 
try{
 
    $conec = new Connection();
    $con = $conec->Open();
    if($con){
        echo 'connected';
    }
    else{
        echo $con;
    }
}
catch(PDOException $ex){
    echo $ex->getMessage();
}

Now, I am successfully connected to database.

#Performing Database Queries

It is now time for CRUD operations. Started by saving data in the database.

Saving the Data

In index.php, replace the code with this:

include 'connection.php';
 
try {
 
    $conec = new Connection();
    $con   = $conec->Open();
    if ($con) {
 
        $sql = "INSERT INTO `user`(`name`, `email`) VALUES ('ahmed','ahmed.khan@cloudways.com')";
        $re  = $con->query($sql);
    } else {
        echo $con;
    }
} catch (PDOException $ex) {
    echo $ex->getMessage();
}

When you run the file, data will inserted in the database.

Fetching the Data

Now that there is data in the database, it is time to fetch the data. Add the following code to index.php:

include 'connection.php';
 
try {
 
    $conec = new Connection();
    $con   = $conec->Open();
    if ($con) {
 
        $sql = "SELECT * FROM user";
        $re  = $con->query($sql);
        foreach ($con->query($sql) as $row) {
            echo $row['name'] . "<br>";
            echo $row['email'] . "<br>";
        }
 
    } else {
        echo $con;
    }
} catch (PDOException $ex) {
    echo $ex->getMessage();
}

On running the file, the code will fetch the data.

Getting the Number of Affected Rows In PDO

In the case of update, it is important to know the number of the data rows that will be affected by the update statement. Let’s perform this in PDO by replacing the code in index.php with the following code.

include 'connection.php';
 
try {
 
    $conec = new Connection();
    $con   = $conec->Open();
    if ($con) {
 
        $sql = "UPDATE `user` SET `email`= 'ahmedkhan_847@hotmail.com' WHERE `id` = 1";
        $affectedrows  = $con->exec($sql);
        echo $affectedrows;
 
    } else {
        echo $con;
    }
} catch (PDOException $ex) {
    echo $ex->getMessage();
}

#The End

This is the end of first part of the PDO tutorial series. In the next part, I will perform the same queries using prepared statements. If you have a question or wish to add to the discussion, please leave a comment.

Hopefully this simple tutorial helped you with your development. If you like our post, please follow us on Twitter and help spread the word. We need your support to continue. If you have questions or find our mistakes in above tutorial, do leave a comment below to let us know.