PHP PDO

PHP PDO

PDO Stands for PHP Data Object, PDO works by providing a standard set of functions for common database operations suc as connecting, querying, result set processing and error handling, and internally translating these functions to native API calls understood by the database in use.

Introduction of PHP PDO

It is a known fact that PHP is an open-source, general-purpose scripting language that is majorly used for creating the dynamic and interactive web pages. A wide range of relational database management systems such as MYSQL, SQLite, and PostgreSQL can be used by the PHP. PHP Data Objects (PDO) is basically a new database connection abstraction library that has been offered by the PHP 5.1 version.

Lets get started with PHP Data Objects (PDO)

PDO generally is the abbreviation for the PHP Data Object that is a PHP extension defining a lightweight and consistent interface for accessing a PHP database. A core PDO class and database-specific driver is provided by the set of PHP extensions. Each and every database driver can easily expose the database-specific features as a regular extension function implementing the PDO interface.

Please note that any type of database function cannot be performed by the use of the PDO extension itself. A database-specific PDO driver must be used in order to access a database server.

Prime focus of PDO is on the data access abstraction instead of database abstraction. Data-access abstraction layer is provided by the PDO that means regardless of the database used, the same functions provided by that database to issue queries and fetch data must be used. Data abstraction is not provided by the PDO, as the SQL or emulate missing features is not rewritten by it.

Benefits of using the PDO

PDO is known as the native database driver. Here are some of the benefits of using PDO:

• Database support :Any database which is written for PDO driver can be accessed by the PDO extension. These are the several PDO drivers that are available to be used for Free Sybase, IBM DB2, Oracle Call Interface, Firebird/Interbase 6TDS, Microsoft SQL Server, and PostgreSQL databases. Please note that the drivers are not available in every system by default hence, available drivers and add ones are needed to be find whenever the user needs them.
• Usability :PDO constitutes of many helper functions to operate automatic routine operations.
• Re-usability : PDO offers the unified API in order to access the multiple databases.
• Security :A prepared statement which protects from SQL injection is used by the PDO. A prepared statement is basically a pre-compiled SQL statement that is used to separate the instruction of the SQL statement from the data.

Classes in PDO

There are basically three PDO classes as discussed below:

PDO - This PDO class represents a connection between PHP and the database.

PDOStatement - This PDO Class basically represents the prepared statement and after the execution of the statement it sets an associated result.

PDOException -This PDO class generally represents errors raised by PDO.

Here are the Databases that are supported by the PDO

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

Which one should you prefer PDO or MySQLi?

Please note that both PDO and MySQLi have their own advantages in their own terms:

• As we know that PDO works on 12 different database systems and on the other hand MySQL is limited to work only with MySQL database. Hence, if the user wants to switch his/her project to another database then that will be easier on PDO. Users have to rewrite the entire code in MySQLi.
• PDO and MySQLi are basically Object-Oriented but in the case of MySQLi, it also offers procedural API. Both the extensions support Prepared Statements as prepared Statements are very important for web application security and they protects from SQL injection.

Common DNS Strings

Create database "pdo"

create database pdo

Cteate table "users"

create table users
(
id int PRIMARY key AUTO_INCREMENT,
name char(50) not null,
email varchar(100) not null,
mobile bigint not null    
);

Database Connection using mysql

<?php  
   $Host="localhost";  
    $User="root";          
    $Password="";
	$Database="pdo";	
    try
	{  
        $pdo= new PDO("mysql:host=$Host;dbname=$Database",$User,$Password);  
     } 
	 catch(PException $e)
	 {  
    die("Error: Could not connect");  
    }    
?>

Form and PHP Script

Let's create an example having 3 fields and save data

<?php  
//get form value and save data 
extract($_REQUEST);
if(isset($save))	
{
	$query="insert into users(name,email,mobile) values('$n','$e','$m')";
	if($pdo->query($query))
	{
	echo "Data Saved successfully";	
	}	
	else
	{
	echo "Some error while executing PDO Connectivity";	
	}
}
?>
<html>
	<head>
		<title>PDO Connectivity</title>
	</head>
	<body>
		<form>
			<table>
				<tr>
					<td>Enter Your Name</td>
					<td><input type="text" name="n"/></td>
				</tr>
				<tr>
					<td>Enter Your Email</td>
					<td><input type="email" name="e"/></td>
				</tr>
				<tr>
					<td>Enter Your Mobile</td>
					<td><input type="number" name="m"/></td>
				</tr>
				<tr>
					<td colspan="2" align="center">
					<input type="submit" name="save" value="save data"/>
					</td>
				</tr>
			</table>
		</form>
	</body>
</html>

Output :

Fetch Data using PDO

Example given to fetch database from database using PDO

<table class="table table-bordered">
<tr class="thead-light">
<th>Sr No</th>
<th>Name</th>
<th>Email</th>
<th>Mobile</th>
</tr>
<?php 
$i=1;
$query=$pdo->query("select * from users");
while($result=$query->fetch(PDO::FETCH_ASSOC))
{
	echo "<tr>";
	echo "<td>".$i."</td>";
	echo "<td>".$result['name']."</td>";
	echo "<td>".$result['email']."</td>";
	echo "<td>".$result['mobile']."</td>";
	echo "</tr>";
	$i++;
}
?>
</table>

Output :