PHP PDO tutorial with CRUD operation example

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

Laravel Pint & VS Code: Automate Your Code Formatting

Laravel Pint is an opinionated PHP code style fixer built on top of PHP-CS-Fixer, designed to simplify the process of ensuring clean and consistent code style in Laravel projects. (...)
Harish Kumar

Best Practices for Testing and Quality Assurance in Custom Web App Development

In the fast-paced world of web app development, delivering high-quality products that meet customer expectations is crucial. But how can you ensure your custom web application (...)
VisionX Technologies

PHP-CS-Fixer: The Ultimate Guide to PHP Code Formatting in VSCode

In this comprehensive guide, we will explore how to use PHP-CS-Fixer in VSCode to automate the process of PHP code formatting and adhere to the best coding practices.
Harish Kumar

How Can I Find Best WordPress Development Company In India 2023?

According to me there are various companies who are doing WordPress development but some of them are providing reliable word press development solutions.
Kenny William

Learn PHP Development from beginning.

PHP stance Hypertext Preprocessor, it's server-side scripting laungage and easy to use. PHP  also well  known for its speed, simplicity, flexibility features that have made it (...)
Programmer Desk

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