PHP MySQL PDO Database Connection and CRUD Operations

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

PDO extends for PHP Data Object. It is lightweight, more portable interface for accessing 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 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 also we can access multiple databases. PDO Driver provides an abstraction layer between 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 execute faster.


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 PDO_SQLITE
PostgreSQL PDO_PGSQL
ODBC v3 PDO_ODBC
IBM Informix PDO_INFORMIX


To check Avaliable 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.

<?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 returns 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, 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, 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 set the where clause condition and 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

Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
Simple pagination in PHP
Simple PHP File Cache
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