What is pdo in php example?

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the "i" stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?

If you need a short answer, it would be "Whatever you like".

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

• MySQLi (object-oriented)
• MySQLi (procedural)
• PDO

MySQLi Installation

For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

For installation details, go to: http://php.net/manual/en/mysqli.installation.php

PDO Installation

For installation details, go to: http://php.net/manual/en/pdo.installation.php

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

Example (MySQLi Object-Oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Note on the object-oriented example above:

$connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) {
  die("Database connection failed: " . mysqli_connect_error());
}

Example (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Example (PDO)

<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
  $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

MySQLi Object-Oriented:

$conn->close();

MySQLi Procedural:

mysqli_close($conn);

In this article, we will discuss PDO in PHP in detail.

There are three main options for 

PHP

<?php

    try {

        $dbhost = 'localhost';

        $dbname='gfg';

        $dbuser = 'root';

        $dbpass = '';

        $connect = new PDO(

"mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

    }

   catch (PDOException $e) {

        echo "Error : " . $e->getMessage() . "<br/>";

        die();

    }

    $count = $connect->exec("DELETE FROM employee WHERE emp_id = 123 ");

    echo("Number of rows to be deleted from the employee table is: ". $count);

?>

ting to a MySQL database server.

• MySQLi procedural
• MySQLi object-oriented
• PDO (PHP Data Objects )

MySQLi procedural and MySQLi object-oriented only support MySQL database but PDO is an advanced method along with MySQL which supports Postgres, SQLite, Oracle, and MS SQL Server.

PDO is more secure than the first two options and it is also faster in comparison with MySQLi procedural and MySQLi object-oriented.

PDO is a database access layer that provides a fast and consistent interface for accessing and managing databases in PHP applications. Every DBMS has a specific PDO driver that must be installed when you are using PDO in PHP applications.

It simplifies the database operations including:

• Creating database connection
• Executing queries
• Handling errors
• Closing the database connections

When we want to connect PHP and MySQL then we need to follow 3 steps:

• Connection with the database
• Run SQL Query
• Closing the database connection

Steps for connection using PDO.

Connection:

In this step, we connect with the database

$conn = new PDO($db_name,$username,$password);

When we want the connection between PHP and MySQL using PDO then we need to create an object of the PDO class.

$db_name = "mysql:host=localhost;dbname:gfg";

Note: $db_name contains the following information.

Run SQL query: To run SQL query we can use two methods:

• query():

$sql = $conn->query("select * from students");

• prepare():

 $sql = $conn->prepare("select * from students");

Note: The prepare() method is more secure than the query() method.

Close the connection:

For closing the database connection using PDO.

$conn = null;

Parameters: It contains the following parameters.

• dsn: It contains the information regarding connection to the database.
• user: It contains the user name.
• password: It contains the password of the database.

Return Value:

On success, it will return the PDO object. And on failure, it will return the PDOException object.

Handling error during connection:

The PDOException object will be thrown in case of any error. We can catch the exception to handle the error. 

PHP

<?php

    try {

          $dsn = "mysql:host=localhost;dbname=gfg";

        $user = "root";

        $passwd = "";

        $pdo = new PDO($dsn, $user, $passwd);

    }

    catch (PDOException $e) {

         echo "Error!: " . $e->getMessage() . "<br/>";

         die();

     }

?>

Output:

Error : SQLSTATE[28000] [1045] 
Access denied for user 'roott'@'localhost' 
(using password: NO)

Example 1: Suppose we have a database “gfg” with a table “students”. We want to fetch the details like “id” and name of all the students present in the table “students”.

PHP

<?php

    $dsn = "mysql:host=localhost;dbname=gfg";

    $user = "root";

    $passwd = "";

    $pdo = new PDO($dsn, $user, $passwd);

    $stm = $pdo->query("SELECT * FROM students");

    $rows = $stm->fetchAll(PDO::FETCH_ASSOC);

    foreach($rows as $row) {

        printf("{$row['id']} {$row['name']}\n");

    }

?>

Output:

1  Student1
2  Student2
3  Student3
4  Student4

Example 2: In this case, suppose we have the “employee” table in “gfg” database. We want to delete the employee from the “employee” table whose “id = 123”. 

PHP

<?php

    try {

        $dbhost = 'localhost';

        $dbname='gfg';

        $dbuser = 'root';

        $dbpass = '';

        $connect = new PDO(

"mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

    }

   catch (PDOException $e) {

        echo "Error : " . $e->getMessage() . "<br/>";

        die();

    }

    $count = $connect->exec("DELETE FROM employee WHERE emp_id = 123 ");

    echo("Number of rows to be deleted from the employee table is: ". $count);

?>

Output:

The number of rows to be deleted from the employee table is: 1

Benefits of PDO:

• Usability: PDO contains helper functions to operate automatic routine operations.
• Reusability: We can access multiple databases because it offers a unified API.
• Security: It provides protection fromSQL injection because it uses a prepared statement. A prepared statement separates the instruction of the SQL statement from the data.
• Error handling: It uses exceptions for error handling. There are three types of modes:
  • Silent
  • Warning
  • Exception
• Multiple database support: It is used to access any database which is written for the PDP driver. We need to find a suitable driver and add them when we use them. There are several PDO drivers available like Microsoft SQL Server, Sybase, PostgreSQL, and many more.

Supported Database: PDO supports 12 different databases.

• MySQL
• PostgreSQL
• Oracle
• Firebird
• MS SQL Server
• Sybase
• Informix
• IBM
• FreeTDS
• SQLite
• Cubrid
• 4D

What does PDO mean in PHP?

What is the function of PDO?

What is PDO explain the benefits of using PDO?

What is PDO extension?