PHP PDO tutorial with CRUD operation example

Razet · · 1477 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:

What is the difference between classes vs enums in PHP 8.1?

One of the common questions that I have been asked is what is the difference between classes and enums. Class(es) and enum(s) share many similarities but differ in some aspects. (...)
Harish Kumar

How to use the enumerations(Enums) of PHP 8.1 in Laravel?

The release of PHP 8.1 brings native enumerations to PHP. There is no more requirement for custom solutions in your Laravel projects since the Laravel v8.69 release has you back. (...)
Harish Kumar

What is Enumerations(Enums) in PHP 8.1? Enums in PHP v8.1 Explained in detail with examples.

PHP 8.1 added many new features, and Enumerations (Enum) is our favourite new feature. Enumerations (or enums for short) allow us to define a new structure similar to a class however (...)
Harish Kumar

Web App Development Technologies

If you have been planning to create robust websites or web apps, you should be aware of the various technologies required to make it happen. Over time, these technologies have (...)
Narola Infotech

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