PHP MySQL PDO Database Connection and CRUD Operations

In this article, you will learn how to connect to the MySQL database using PHP Data Object and perform CRUD operations. So, let's first know what is PDO?

Database is the base of every web application, consisting of the database management system that manages the data users create, delete, modify, and query. There are several vendors available to connect PHP with popular DBMS. To standardize and streamline development practices, PHP introduced PHP Data Objects (PDO) in PHP 5.1.

PDO extends for PHP Data Object. It is lightweight, more portable interface for accessing the database in PHP. It is a database access layer which makes the developer to write portable code much easier.

To perform database operation using PDO, we need a database PDO Driver. Without PDO database driver, we cannot get benefit of pdo database extensions.

PDO working is object-based, which is more productive. As MySQL does not support prepared statement, stored procedures. So with the help of PDO, we can use prepared statement features in MySQL. With the help of PDO, we can make MySQL more secure, and and we can also access multiple databases. PDO Driver provides an abstraction layer between the database and PHP.



About Prepared Statement

In a prepared statement, we can use the same query multiple times. First, we need to analyze and optimize the query and later we can use it in many places. It reduces development time and executes faster. PDO provides various ways to work with objects and retrieves prepared statements that make work much easier.


PDO MySQL Connection in PHP

PDO Drivers

To use PDO in PHP, php_pdo_mysql should be enabled in php.ini in PHP extension. This is by default enabled in PHP version 5.1.0 and later versions.

The PDO can work with 12 different database systems. If you have to switch your project database to another database, PDO makes the process easy. There are different PDO drivers available for different databases.

Databases Drivers
MySQL 3.x & later PDO_MYSQL
SQLite 3 and SQLite 2 PDO_SQLITE
PostgreSQL PDO_PGSQL
ODBC v3 PDO_ODBC
IBM Informix PDO_INFORMIX
Cubrid PDO_CUBRID
Firebird PDO_FIREBIRD
Microsoft SQL Server PDO_SQLSRV


To check Available PDO Drivers in PHP

The following PHP command returns the available PDO Drivers in PHP. It returns all enabled PDO Drivers in Array.

<?php
 $database = new PDO("mysql:host=$hostname;dbname=$databasename", 
 $username, $password);
?>




PDO MySQL Database Connection

The PDO represents a connection between PHP and the database. Here is the syntax for PDO MySQL database connection. It creates a new PDO object with the data source name, user, and password. The PDO object is an instance of the PDO class.

<?php
$database = new PDO("mysql:host=$hostname;dbname=$databasename", 
$username, $password);
?>

  • $hostname - Server Name
  • $databasename - Database Name
  • $username - Database User Name
  • $password - Database Password

To handle PDO connection error

PDO has an exception class to handle any problems that may occur in our database queries. To handle the exception, it would be better to write the connection code in a try catch block. If an exception is thrown in the try block, the script stops execution and control goes directly to the catch block.

<?php
try{ 
$database = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
}
catch(PDOExtension $e){
echo $e->getMessage();
}
?>




Close the PDO MySQL database connection.

To close the MySQL PDO connection, just set null to the database connection variable.

$database = null;




MySQL CRUD operations using PDO


PHP MySQL Create Table

In this given code, we have created a table name 'employees'. It contains four fields(emp_id, first_name, last_name emp_code). The '$database' is the database connection variable that we have created above.

Here exec() is used to execute the PDO and return the number of affected rows.

<?php
$create_tbl = "CREATE TABLE employees emp_id int(11) NOT   NULL AUTO_INCREMENT, first_name varchar(25) NOT NULL,
 last_name varchar(25) NOT NULL, emp_code varchar(25) NOT NULL"; 
$create_emp = $database->exec($create_tbl);
if($create_emp != 'false'){
 echo 'Employee table created successfully.'; 
}
else{ 
 echo 'Error in Create Statement.';
} 
?>




PHP MySQL INSERT Table

Here, prepare() is used to prepare the insert statement for execution by using PDO execute() method.

<?php
$emp_insert = $database->prepare("INSERT into employees   ('emp_id','first_name','last_name','emp_code') VALUES ('', 'John', 'Bown', '121')");
$emp_insert->execute();
?>
To protect from SQL Injection, we should use placeholder instead of values. Like, we can write the above code as -
<?php
$emp_data = array('id'=>'', 'fname'=>'John', 'lname'=>'Bown', 'empcode'=>'121');
$emp_insert = $database->prepare("INSERT into employees('emp_id','first_name','last_name','emp_code') 
VALUES ('', ':fname', ':lname', ':empcode')");
$emp_insert->execute($emp_data);
?>


PHP MySQL UPDATE Table

Here, the prepare() is used to prepare the update statement for execution by using PDO execute() method. The bindparam() binds a parameter to the specified variable name.

<?php
$emp_update = $database->prepare("UPDATE employees SET first_name= :fname, last_name = :lname WHERE id = :id ");
$emp_update->bindparam(':fname', 'Doran');
$emp_update->bindparam(':lname', 'Devis');
$emp_update->execute();
?>


PHP MySQL SELECT from Table

Here, the prepare() is used to prepare the select statement for execution by using PDO execute() method. The bindparam() binds a parameter to the specified variable name to fetch the desired value, i.e., it sets the where clause condition and the fetch() method fetches a row from the result set.

Here, PDO::FETCH_ASSOC returns an array indexed by column name.

<?php
$emp_select = $database->prepare("SELECT FROM employees WHERE id = :id ");
$emp_select->bindparam(':id', '1');
$emp_select->execute();
$emprows = $emp_select->fetch(PDO::FETCH_ASSOC);
return $emprows;
?>




Related Articles

How to create search filter in PHP
PHP array length
Import Excel File into MySQL Database using PHP
PHP String Contains
PHP remove last character from string
PHP random quote generator
PHP calculate percentage of total
PHP sanitize input
Upload multiple files and store in MySQL database using PHP
multiple choice quiz in PHP and MySQL
Import Data Into MySQL From Excel File
Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
Simple pagination in PHP
Simple PHP File Cache
PHP upload multiple files
PHP Connection and File Handling on FTP Server
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
How to display PDF file in PHP from database




Read more articles


General Knowledge



Learn Popular Language