PHP PDO tutorial with CRUD operation example
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.
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.
Please login or create new account to add your comment.