PHP PDO tutorial with CRUD operation example

Razet · · 340 Views

One of the most significant PHP extension is called PHP Data Objects or PDO, which was available since PHP 5.0. PDO gives an interface to working with different RDBMS, including creating a database connection, performing queries, handling error, etc.

PHP PDO tutorial with CRUD operation example

Supported Databases

PDO depends on database-specific drivers, e.g. PDO_MYSQL for MySQL, PDO_PGSQL for PostgreSQL, PDO_OCI for Oracle, and so on, to work appropriately so to utilize PDO for a particular database, you have to have the relating database driver available.

PDO_CUBRID

Cubrid

PDO_DBLIB

FreeTDS / Microsoft SQL Server / Sybase

PDO_FIREBIRD

Firebird

PDO_IBM

IBM DB2

PDO_INFORMIX

IBM Informix Dynamic Server

PDO_MYSQL

MySQL 3.x/4.x/5.x

PDO_OCI

Oracle Call Interface

PDO_ODBC

ODBC v3 (IBM DB2, unixODBC and win32 ODBC)

PDO_PGSQL

PostgreSQL

PDO_SQLITE

SQLite 3 and SQLite 2

PDO_SQLSRV

Microsoft SQL Server / SQL Azure

PDO_4D

4D

If you wish to work with different databases, you should first install the relevant driver.

To check what drivers are installed on your system, create a new PHP file and add the following code snippet to it:

<?php
    print_r(PDO::getAvailableDrivers());
?>

Working With PDO

Using PDO, you could easily perform CRUD and other related DBMS operations. Essentially, PDO provides a layer that isolates database related activities from the rest of the code.

Connectivity

One of the most significant advantages of PDO is simple database connectivity. Consider the following code piece that is utilized to set up connections with the database. Note that when the underlying DBMS changes, the main change that you have to make is the database type.

In DatabaseConnection.php:

<?php

Class DatabaseConnection {

    private  $server = "mysql:host=localhost;dbname=databaseName";

    private  $user = "root";

    private  $pass = "";

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

    protected $con;
    
    public function openConnection()
    {
        try {

            $this->con = new PDO($this->server, $this->user,$this->pass,$this->options);

            return $this->con;

        }
        catch (PDOException $e) {
            echo "There is some problem in connection: " . $e->getMessage();
        }
    }

    public function closeConnection() {
   	    $this->con = null;
	}
}

In the above snippet, notice that the DBMS is MySQL. However, if the DBMS changes to MS SQL Server, the main change will be the replacement of mysql with mssql.

Creating a Table With PDO

To create a table, first declare a query, and afterward execute it with exec function as no data will be returned.

<?php

include_once 'DatabaseConnection.php';

try
{
    $database = new DatabaseConnection();

    $db = $database->openConnection();

    // sql to create table
    $sql = "CREATE TABLE `Student` ( `ID` INT NOT NULL AUTO_INCREMENT , `name`VARCHAR(40) NOT NULL , `email` VARCHAR(40)NOT NULL , PRIMARY KEY (`ID`)) ";

    // use exec() because no results are returned
    $db->exec($sql);

    echo "Table Student created successfully";

    $database->closeConnection();
}
catch (PDOException $e)
{
    echo "There is some problem in connection: " . $e->getMessage();
}
?>

Inserting Data With PDO

To insert data into a table using PDO, first, prepare the query using the prepare statement. Next, run this query with the execute function. Note that this practice prevents SQL injection attacks.

<?php

include_once 'DatabaseConnection.php';

try
{
    $database = new DatabaseConnection();

    $db = $database->openConnection();
    
    // inserting data into create table using prepare statement to prevent from sql injections
    $stm = $db->prepare("INSERT INTO student (ID,name,email) VALUES ( :id, :name, :email)") ;
    
    // inserting a record
    $stm->execute(array(':id' => 0 , ':name' => 'Any name', ':email' => '[email protected]'));
    
    echo "New record created successfully";
}
catch (PDOException $e)
{
    echo "There is some problem in connection: " . $e->getMessage();
}

?>

Select Data With PDO

To select data, first, make a query and afterward execute it in a for each loop to fetch records from the table.

<?php

include_once 'DatabaseConnection.php';

try
{
    $database = new DatabaseConnection();

    $db = $database->openConnection();

    $sql = "SELECT * FROM student " ;

    foreach ($db->query($sql) as $row) 
    {
        echo " ID: ".$row['ID'] . "<br>";
        echo " Name: ".$row['name'] . "<br>";
        echo " Email: ".$row['email'] . "<br>";
    }
}
catch (PDOException $e)
{
    echo "There is some problem in connection: " . $e->getMessage();
}

?>

Update Data With PDO

To update a record in the table, first declare a query string and then execute it with exec function.

<?php

include_once 'DatabaseConnection.php';

try
{
    $database = new DatabaseConnection();

    $db = $database->openConnection();

    $sql = "UPDATE `student` SET `name`= 'new name' , `email` = 'your email' WHERE `id` = 8" ;
    
    $affectedrows  = $db->exec($sql);

   if(isset($affectedrows))
    {
       echo "Record has been successfully updated";
    }          
}
catch (PDOException $e)
{
    echo "There is some problem in connection: " . $e->getMessage();
}

?>

Delete Data With PDO

<?php

include_once 'DatabaseConnection.php';

try
{
    $database = new DatabaseConnection();

    $db = $database->openConnection();

    $sql = "DELETE FROM student WHERE `id` = 8" ;

    $affectedrows  = $db->exec($sql);

    if(isset($affectedrows))
    {
       echo "Record has been successfully deleted";
    }          
}
catch (PDOException $e)
{
   echo "There is some problem in connection: " . $e->getMessage();
}
?>

Conclusion

PDO is the data accessing layer that significantly facilitates the way toward connecting and working with databases. Maybe, the best thing about PDO is the smoothed out process of database migration.

In this post, I presented PDO and explained how you could perform CRUD activities using PDO in PHP. If you have questions or might want to add to the conversation, do leave a comment below.

0

Please login or create new account to add your comment.

0 comments
You may also like:

Install phpMyAdmin Manually with Nginx server on Ubuntu

In this guide, I will show you how to install and configure phpMyAdmin with Nginx, MySQL, and PHP8.0 (LEMP) on an Ubuntu system. phpMyAdmin is a free and open-source database (...)
Harish Kumar

How to Upload Multiple Images with jQuery AJAX and PHP, with preview

Uploading an image without page refresh is more user-friendly than refreshing the entire page. So, in this guide, you will learn how to upload multiple images using ajax in jQuery (...)
Nakul Kumar

How to Add Google reCAPTCHA to a PHP Form with Example?

In this guide, we will integrate the Google reCAPTCHA in the PHP contact form. Utilizing this Google reCAPTCHA code, we can validate the human users and protect the form of submission (...)
Nakul Kumar

What's new in PHP 8: New Features and Improvements

PHP 8.0 is a major update of the PHP language. It contains a range of new features and optimizations such as the JIT compiler, union types, attributes, constructor property promotion, (...)
Harish Kumar

How to Install phpMyAdmin with Nginx on Ubuntu Server?

The phpMyAdmin is an open-source PHP-based tool for handle MySQL and MariaDB databases over a web-based interface. 
Harish Kumar

How To Install LEMP stack (Linux, Nginx, MySQL, PHP) on Ubuntu System

The term LEMP is an acronym of the four open-source projects:
Harish Kumar